Baza danych „zamrożona” na ALTER TABLE

Nasze środowisko produkcyjne po prostu zamarło * dziś rano podczas zmiany stołu i dodania kolumny.

Obrażające SQL:ALTER TABLE cliente ADD COLUMN topicos character varying(20)[];

* Logowanie do naszego systemu wymaga wyboru z tej samej tabeli, więc nikt nie może się zalogować podczas tabeli zmian. W rzeczywistości musieliśmy zabić proces, aby system mógł wznowić normalne działanie.

Struktura tabeli:

CREATE TABLE cliente
(
  rut character varying(30) NOT NULL,
  nombre character varying(150) NOT NULL,
  razon_social character varying(150) NOT NULL,
  direccion character varying(200) NOT NULL,
  comuna character varying(100) NOT NULL,
  ciudad character varying(100) NOT NULL,
  codigo_pais character varying(3) NOT NULL,
  activo boolean DEFAULT true,
  id serial NOT NULL,
  stock boolean DEFAULT false,
  vigente boolean DEFAULT true,
  clase integer DEFAULT 1,
  plan integer DEFAULT 1,
  plantilla character varying(15) DEFAULT 'WAYPOINT'::character varying,
  facturable integer DEFAULT 1,
  toolkit integer DEFAULT 0,
  propietario integer DEFAULT 0,
  creacion timestamp without time zone DEFAULT now(),
  codelco boolean NOT NULL DEFAULT false,
  familia integer DEFAULT 0,
  enabled_machines boolean DEFAULT false,
  enabled_canbus boolean DEFAULT false,
  enabled_horometro boolean DEFAULT false,
  enabled_comap boolean DEFAULT false,
  enabled_frio boolean DEFAULT false,
  enabled_panico boolean DEFAULT false,
  enabled_puerta boolean DEFAULT false,
  enabled_rpm boolean DEFAULT false,
  enabled_supervisor integer DEFAULT 0,
  demo boolean,
  interno boolean,
  mqtt_enable boolean NOT NULL DEFAULT false,
  topicos character varying(20)[],
  CONSTRAINT pk_cliente PRIMARY KEY (rut),
  CONSTRAINT fk_cliente_familiaid FOREIGN KEY (familia)
      REFERENCES cliente_familia (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT pk_pais FOREIGN KEY (codigo_pais)
      REFERENCES pais (codigo) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT unique_id_cliente UNIQUE (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cliente
  OWNER TO waypoint;
GRANT ALL ON TABLE cliente TO waypoint;
GRANT ALL ON TABLE cliente TO waypointtx;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE cliente TO waypointtomcat;
GRANT SELECT ON TABLE cliente TO waypointphp;
GRANT SELECT ON TABLE cliente TO waypointpphppublic;
GRANT ALL ON TABLE cliente TO waypointsoporte;
GRANT SELECT, INSERT ON TABLE cliente TO waypointsalesforce;
GRANT SELECT ON TABLE cliente TO waypointadminuser;
GRANT SELECT ON TABLE cliente TO waypointagenda;
GRANT SELECT ON TABLE cliente TO waypointmachines;
GRANT SELECT ON TABLE cliente TO waypointreports;
GRANT SELECT ON TABLE cliente TO readonly;

CREATE INDEX index_cliente
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default");

CREATE INDEX index_cliente_activo
  ON cliente
  USING btree
  (activo);

CREATE INDEX index_cliente_id_activo
  ON cliente
  USING btree
  (id, activo);

CREATE INDEX index_cliente_rut_activo
  ON cliente
  USING btree
  (rut COLLATE pg_catalog."default", activo);


CREATE TRIGGER trigger_default_admin
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_admin();

CREATE TRIGGER trigger_default_grupo
  AFTER INSERT
  ON cliente
  FOR EACH ROW
  EXECUTE PROCEDURE crea_default_clientegrupo();  

Czy powinienem wyłączyć ograniczenia, wyzwalacze, czy coś innego?

Być może strojenie DB?

Co jeszcze powinienem przewidzieć do dalszej analizy?

Wersja: PostgreSQL 9.4.5 na x86_64-unknown-linux-gnu, skompilowany przez gcc (Debian 4.9.2-10) 4.9.2, 64-bit

Operacje DDL zwykle blokują obiekt, na który działają, dlatego nie powinny być wykonywane poza planowanymi oknami konserwacji (gdy użytkownicy oczekują zakłóceń lub system będzie całkowicie offline przez planowany czas) - nic nie możesz zrobić o tym łatwo ¹ .

Niektóre operacje zachowują blokadę zapisu, więc aplikacja może obsługiwać żądania, które tylko odczytują obiekty, których dotyczy problem.

Dokumentacja wydaje się całkiem dobra w zestawianiu blokad, które mogą być utrzymywane przez operacje DDL.

Ten wpis na blogu zawiera podsumowanie, które sugeruje, że dodanie kolumny może być operacją online, jeśli kolumna ma wartość zerową i nie ma wartości domyślnej ani unikatowego ograniczenia, chociaż oznacza to, że stwierdzenie, które według ciebie powinno zostać uruchomione bez blokad (jak postgres IIRC domyślnie kolumny mają wartość NULLable, chyba że wyraźnie zaznaczono inaczej). Czy wykonałeś jakieś inne operacje po kolumnie dodawania? Być może utworzenie na nim indeksu (który domyślnie zabrałby blokadę zapisu na stole)?

¹ _{Niektóre uzgodnienia dotyczące replikacji / klastrowania / kopii lustrzanej umożliwiają aktualizację kopii lustrzanej (wstrzymywanie aktualizacji podczas zmiany i odtwarzanie ich później), przejście do używania kopii jako aktywnej i tak dalej, aż każda kopia zostanie zaktualizowana, więc czas przestoju jest ograniczony do czasu potrzebnego na odtworzenie zmian dokonanych podczas operacji DDL. Takie operacje na żywo nie są jednak pozbawione ryzyka, więc jeśli nie jest to absolutnie niemożliwe, zaleca się ustawienie odpowiedniego okna konserwacji do wykonywania i weryfikacji aktualizacji strukturalnych.}

Polecenie, które chcesz uruchomić, blokuje dostęp do tabeli WYŁĄCZNIE DOSTĘPU, uniemożliwiając wszystkim pozostałym dostęp do tej tabeli. Ale czas trwania tej blokady powinien wynosić zaledwie kilka milisekund, ponieważ dodanie kolumny takiej jak ta, którą chcesz dodać, nie wymaga ponownego zapisania tabeli, a jedynie zaktualizowania metadanych.

Gdzie może pojawić się problem, i założę się, że dolary do pączków, że jest to problem, który widzisz, są w priorytetach blokady. Ktoś ma słabą blokadę, taką jak blokada ACCESS SHARE, na tym stole i obozuje na niej w nieskończoność (może wyciekło połączenie bezczynności w transakcji? Ktoś, kto otworzył psql, rozpoczął zapytanie w trybie powtarzalnego odczytu, a potem pojechał na wakacje?).

DODAJ KOLUMNĘ próbuje uzyskać WYJĄTKOWY DOSTĘP, którego potrzebuje, i ustawia się w kolejce za pierwszą blokadą.

Teraz wszystkie przyszłe wnioski o blokadę stoją w kolejce za oczekującym żądaniem DOSTĘP WYŁĄCZNIE.

Koncepcyjnie, przychodzące żądania blokady, które są kompatybilne z już przyznaną blokadą, mogą przeskakiwać nad oczekującym WYŁĄCZENIEM i być przyznawane poza kolejnością, ale nie w ten sposób robi to PostgreSQL.

Musisz znaleźć proces, który utrzymuje długotrwały słaby zamek.

Możesz to zrobić, sprawdzając tabelę pg_locks.

select * from pg_locks where 
    granted and relation = 'cliente'::regclass \x\g\x

Jeśli zrobisz to, gdy wszystko jest zamknięte, powinieneś otrzymać tylko jedną odpowiedź (chyba że istnieje wielu długowiecznych winowajców). Jeśli zrobisz to po tym, jak już zabiłeś DODAJ KOLUMNĘ, możesz zobaczyć wiele przyznanych blokad, ale jeśli powtórzysz to kilka razy, jedna lub kilka powinno zostać za każdym razem.

Następnie możesz pobrać identyfikator PID uzyskany z pg_lock i wysłać zapytanie do pg_stat_activity, aby zobaczyć, co robi przestępca:

select * from pg_stat_activity where pid=28731 \x\g\x

...

backend_start    | 2016-03-22 13:08:30.849405-07
xact_start       | 2016-03-22 13:08:36.797703-07
query_start      | 2016-03-22 13:08:36.799021-07
state_change     | 2016-03-22 13:08:36.824369-07
waiting          | f
state            | idle in transaction
backend_xid      |
backend_xmin     |
query            | select * from cliente limit 4;

Uruchomił więc zapytanie w ramach transakcji, a następnie stał bezczynny bez zamykania transakcji. Jest teraz 13:13, więc byli bezczynni przez 5 minut.