Czy bezpiecznie jest anulować zapytanie PostgreSQL ALTER TABLE, które czeka na zamek?

ALTER TABLEZapytanie rozpoczęliśmy kilka godzin temu i dopiero niedawno zdaliśmy sobie sprawę (za pośrednictwem pg_stat_activity), że czeka ono na zamek. Odkryliśmy inne zapytanie, które trzyma blokadę tabeli, którą chcemy zmienić, i nie pozwalając jej odejść.

Nasze zapytanie jest „prostym” zapytaniem (zmiana typu danych kolumny), ale działa na ogromnej tabeli.

Zamiast zabić proces, który utrzymuje zamek, zdecydowaliśmy, że wolimy go zabić ALTER TABLE.

Zrobiliśmy nie zawinąć ALTER TABLEw transakcji.

O ile rozumiem, fakt, że nasze zapytanie czeka na zamek, oznacza, że zawsze czekał na zamek i nigdy niczego nie zmienił.

Czy to prawda? Czy możemy bezpiecznie anulować nasze ALTER TABLEzapytanie? Czy jest możliwe, że zapytanie już coś zmodyfikowało, a anulowanie go pozostawiłoby naszą bazę danych w jakimś stanie w połowie?

PS: Plan jest anulować za pomocą SELECT pg_cancel_backend(pid);. Jeśli to zły pomysł, daj mi znać.

O ile rozumiem, fakt, że nasze zapytanie czeka na zamek, oznacza, że ​​zawsze czekał na zamek i nigdy niczego nie zmienił.

Racja - jeśli widzisz, że pg_stat_activity.waiting jest „prawdziwe” dla ALTER TABLE, to prawie na pewno oznacza, że ​​cierpliwie czeka na blokadę ACCESS EXCLUSIVE na docelowym stole i jego prawdziwą pracę (przepisanie tabeli w razie potrzeby, zmiana katalogów , przebudowywanie indeksów itp.) jeszcze się nie rozpoczęło.

Czy możemy bezpiecznie anulować zapytanie ALTER TABLE? Czy jest możliwe, że zapytanie już coś zmodyfikowało, a anulowanie go pozostawiłoby naszą bazę danych w jakimś stanie w połowie?

Anulowanie zapytań (lub, równoważnie, wycofanie transakcji) w PostgreSQL nie wiąże się z żadnym ryzykiem uszkodzenia bazy danych, które mogło zostać wystraszone w niektórych innych bazach danych (np. Przerażające ostrzeżenie na dole tej strony). Właśnie dlatego osoby niebędące superużytkownikami w najnowszych wersjach mogą swobodnie korzystać pg_cancel_backend()i pg_terminate_backend()zabijać własne zapytania działające w innych backendach - można z nich bezpiecznie korzystać bez obaw o uszkodzenie bazy danych. W końcu PostgreSQL musi być przygotowany na poradzenie sobie z każdym procesem ginięcia, np. SIGKILL z zabójcy OOM, zamknięcie serwera itp. Do tego właśnie służy dziennik WAL .

Być może widzieliście również, że w PostgreSQL można wykonywać większość poleceń DDL zagnieżdżonych w transakcji (wielowyrazowej), np.

BEGIN;
ALTER TABLE foo ...;
ALTER TABLE bar ...;
-- more stuff
COMMIT; -- or ROLLBACK; if you've changed your mind

(niesamowite, aby upewnić się, że migracje schematów przebiegają razem albo wcale). Powiedziałeś jednak:

Zrobiliśmy nie zawinąć ALTER TABLEw transakcji.

To w porządku dla jednego polecenia - z dokumentów ,

PostgreSQL faktycznie traktuje każdą instrukcję SQL jako wykonywaną w ramach transakcji. Jeśli nie wydasz polecenia BEGIN, każda pojedyncza instrukcja ma niejawne BEGIN i (jeśli się powiedzie) COMMIT owinięte wokół niego. Grupa instrukcji otoczona BEGIN i COMMIT jest czasem nazywana blokiem transakcji.

Więc anulowanie tego ALTER TABLE, albo przez pg_cancel_backend()albo Ctrl-C wydane z kontrolnego polecenia psql, będzie miało podobny efekt, jakbyś to zrobił

BEGIN;
ALTER TABLE ... ;
ROLLBACK;

(choć, jak miejmy nadzieję, zobaczycie, anulowanie tego kosztownego ALTER TABLEmoże uratować bazę danych przed niepotrzebnym szlifowaniem, jeśli tylko idziesz ROLLBACK).

Aby rozwinąć poprawną i doskonałą odpowiedź Josha:

Czy możemy bezpiecznie anulować zapytanie ALTER TABLE?

Tak.

Byłoby bezpieczne, nawet gdyby było w trakcie przepisywania tabeli .

Jeśli chcesz, możesz po prostu zamknąć cały serwer PostgreSQL lub maszynę, na której działa, zrestartować go i wszystko będzie dobrze. DDL w PostgreSQL jest transakcyjny i bezpieczny w przypadku awarii.

Operacje DDL są rejestrowane przez WAL i gwarantuje się, że można je przywrócić lub zakończyć po przywróceniu po awarii lub przerwie.