Jak atomowo zastąpić dane tabeli w PostgreSQL

Chcę zastąpić całą zawartość tabeli, nie wpływając na żadne przychodzące SELECTinstrukcje podczas procesu.

Przypadkiem użycia jest posiadanie tabeli, która przechowuje informacje o skrzynce pocztowej, które są regularnie wyodrębniane i muszą być przechowywane w tabeli PostgreSQL. Jest wielu klientów korzystających z aplikacji, która stale odpytuje tę samą tabelę.

Normalnie zrobiłbym coś takiego (przychodzący pseudokod) ...

BEGIN TRANSACTION
TRUNCATE TABLE
INSERT INTO
COMMIT

Niestety nie można odczytać tabeli podczas tego procesu; ze względu na czas potrzebny INSERT INTOdo ukończenia. Stół jest zamknięty.

W MySQL użyłbym ich RENAME TABLEpolecenia atomowego, aby uniknąć tych problemów ...

CREATE TABLE table_new LIKE table; 
INSERT INTO table_new;
RENAME TABLE table TO table_old, table_new TO table; *atomic operation*
DROP TABLE table_old;

Jak mogę to osiągnąć w PostgreSQL?

Na potrzeby tego pytania możesz założyć, że nie używam kluczy obcych.

Tak, TRUNCATE TABLE polecenie , które wykonujesz „... uzyskuje blokadę WYŁĄCZNIE DOSTĘPU dla każdej tabeli, na której działa ”, więc w pierwszym wysłanym bloku SQL wszyscy inni klienci próbujący uzyskać dostęp do tabeli po tym czasie będą blokowani do czasu INSERTzakończenia a COMMIT.

Możesz użyć tego samego obejścia, co w kodzie specyficznym dla MySQL; Postgres obsługuje mniej więcej tę samą składnię i będzie miał podobne zachowanie blokowania. To znaczy:

BEGIN;
-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
-- LOCK TABLE "table" IN SHARE ROW EXCLUSIVE mode;
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";

COMMIT;

Dodatkowy bonus: Postgres faktycznie obsługuje transakcyjny DDL, w przeciwieństwie do MySQL, więc w razie potrzeby ODWRÓCENIA powyższej transakcji, możesz to zrobić bezpiecznie.