Wyłącz wszystkie ograniczenia i kontrole tabeli podczas przywracania zrzutu

Otrzymałem zrzut mojej bazy danych PostgreSQL z:

pg_dump -U user-name -d db-name -f dumpfile

którą następnie przywracam w innej bazie danych za pomocą:

psql X -U postgres  -d db-name-b -f dumpfile

Mój problem polega na tym, że baza danych zawiera ograniczenia referencyjne, kontrole i wyzwalacze, a niektóre z nich (kontrole, jak by się to wydawało w szczególności) zawodzą podczas przywracania, ponieważ informacje nie są ładowane w kolejności, która spowodowałaby, że kontrole te zostałyby uznane. Na przykład wstawienie wiersza w tabeli może być powiązane z funkcją CHECKwywołującą plpgsqlfunkcję, która sprawdza, czy warunek zachowuje się w innej niepowiązanej tabeli. Jeśli ta ostatnia tabela nie zostanie załadowana psqlprzed pierwszą, pojawi się błąd.

Poniżej przedstawiono kod SSCCE, który tworzy bazę danych, której pg_dumpnie można przywrócić po zrzuceniu :

CREATE OR REPLACE FUNCTION fail_if_b_empty () RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

CREATE TABLE IF NOT EXISTS a (
     i              INTEGER                    NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);
CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);
INSERT INTO b(i) VALUES (0);

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty());

Czy istnieje sposób, aby wyłączyć (z wiersza poleceń) wszystkie takie ograniczenia podczas przywracania zrzutu i włączyć je ponownie później? Korzystam z PostgreSQL 9.1.

Więc przeglądasz inne tabele z CHECKograniczeniem .

CHECKograniczenia powinny uruchamiać IMMUTABLEkontrole. To, co przechodzi OK dla wiersza w jednym czasie, powinno przejść OK w dowolnym momencie. Tak CHECKdefiniowane są ograniczenia w standardzie SQL. To jest również powód tego ograniczenia ( według dokumentacji ):

Obecnie CHECKwyrażenia nie mogą zawierać podkwerend ani odwoływać się do zmiennych innych niż kolumny bieżącego wiersza.

Teraz wyrażenia w CHECKograniczeniach mogą używać funkcji, nawet funkcji zdefiniowanych przez użytkownika. Powinny one być ograniczone do IMMUTABLEfunkcji, ale Postgres obecnie tego nie egzekwuje. Zgodnie z tą pokrewną dyskusją na temat hakerów pgsql , jednym z powodów jest zezwolenie na odniesienia do aktualnego czasu, który nie jest IMMUTABLEz natury.

Ale patrzysz na rzędy innego stołu, co całkowicie narusza to, jak CHECKpowinny działać ograniczenia. Nie dziwię się, że to pg_dumpnie zapewnia.

Przenieś swój czek w innej tabeli do wyzwalacza (który jest właściwym narzędziem) i powinien on działać z nowoczesnymi wersjami Postgres.

PostgreSQL 9.2 lub nowszy

Chociaż powyższe dotyczy wszystkich wersji Postgres, w Postgres 9.2 wprowadzono kilka narzędzi, które mogą pomóc w twojej sytuacji:

opcja pg_dump --exclude-table-data

Prostym rozwiązaniem byłoby zrzucenie bazy danych bez danych dla tabeli naruszającej:

--exclude-table-data=my_schema.my_tbl

Następnie dołącz tylko dane dla tej tabeli na końcu zrzutu za pomocą:

--data-only --table=my_schema.my_tbl

Ale mogą wystąpić komplikacje z innymi ograniczeniami na tym samym stole. Istnieje jeszcze lepsze rozwiązanie :

NOT VALID

Istnieje NOT VALIDmodyfikator ograniczeń. Dostępne tylko dla ograniczenia FK w wersji 9.1, ale zostało rozszerzone na CHECKograniczenia w wersji 9.2. Według dokumentacji:

Jeśli ograniczenie jest zaznaczone NOT VALID, potencjalnie długie sprawdzenie początkowe w celu sprawdzenia, czy wszystkie wiersze w tabeli spełniają ograniczenie, jest pomijane. Ograniczenie będzie nadal egzekwowane wobec kolejnych wstawek lub aktualizacji [...]

Zwykły plik zrzutu postgres składa się z trzech „sekcji”:

• pre_data
• data
• post-data

^{Postgres 9.2 wprowadził także opcję osobnego zrzutu sekcji -- section=sectionname, ale to nie pomaga w rozwiązaniu problemu.}

Tutaj jest ciekawie. Według dokumentacji:

Pozycje danych obejmują definicje indeksów, wyzwalaczy, reguł i ograniczeń innych niż sprawdzone ograniczenia kontrolne . Pozycje danych wstępnych obejmują wszystkie inne elementy definicji danych.

Odważny nacisk mój.
Możesz zmienić obraźliwe CHECKograniczenie na NOT VALID, które przenosi ograniczenie do post-datasekcji. Upuść i utwórz ponownie:

ALTER TABLE a DROP CONSTRAINT a_constr_1;
ALTER TABLE a ADD  CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()) NOT VALID;

To powinno rozwiązać twój problem. Możesz nawet pozostawić ograniczenie w tym stanie , ponieważ lepiej odzwierciedla to, co faktycznie robi: sprawdź nowe wiersze, ale nie daje żadnych gwarancji dla istniejących danych. Nie ma nic złego w NOT VALIDograniczeniu sprawdzania. Jeśli wolisz, możesz to sprawdzić później:

ALTER TABLE a VALIDATE CONSTRAINT a_constr_1;

Ale potem wracasz do status quo ante.

Wydaje się, że wynika to ze sposobu, w jaki pg_dumptworzy zrzut. Patrząc na zrzut, zauważyłem, że CHECKograniczenie było obecne w pliku zrzutu, używając składni, która jest częścią CREATE TABLEpolecenia:

CREATE TABLE a (
    i integer NOT NULL,
    CONSTRAINT a_constr_1 CHECK (fail_if_b_empty())
);      

Powoduje to awarię po przywróceniu bazy danych, ponieważ sprawdzanie jest przeprowadzane, zanim tabela alub tabela będą bmiały w sobie jakiekolwiek dane. Jeśli jednak plik zrzutu jest edytowany i CHECKdodawany jest przy użyciu następującej składni zamiast tego na końcu pliku zrzutu:

ALTER TABLE a ADD CONSTRAINT a_constr_1 CHECK (fail_if_b_empty()); 

... to nie ma problemu z przywróceniem.

Dokładną tę samą logikę można zaimplementować za pomocą polecenia TRIGGERjak w następującym skrypcie:

CREATE OR REPLACE FUNCTION fail_if_b_empty (
    ) RETURNS BOOLEAN AS $$
    SELECT EXISTS (SELECT 1 FROM b)
$$ LANGUAGE SQL;

DROP TABLE IF EXISTS a;

CREATE TABLE IF NOT EXISTS a (
    i   INTEGER   NOT NULL
);

INSERT INTO a(i) VALUES (0),(1);

CREATE TABLE IF NOT EXISTS b (
    i  INTEGER NOT NULL
);

INSERT INTO b(i) VALUES (0);

CREATE TRIGGER tr1 AFTER INSERT OR UPDATE ON a
FOR EACH ROW
EXECUTE PROCEDURE fail_if_b_empty();  

W tym przypadku jednak pg_dumptworzy (domyślnie) wyzwalacz na końcu pliku zrzutu (a nie w CREATE TABLEinstrukcji, jak w przypadku sprawdzania), więc przywracanie powiodło się.