Início / dba / Perguntas / 100779
Accepted
Clarkey
Asked: 2015-05-07 08:25:25 +0800 CST

Como substituir atomicamente os dados da tabela no PostgreSQL

  • 772

Desejo substituir todo o conteúdo de uma tabela, sem afetar nenhuma SELECTinstrução recebida durante o processo.

O caso de uso é ter uma tabela que armazene informações de caixa de correio que são extraídas regularmente e precisam ser armazenadas em uma tabela PostgreSQL. Existem muitos clientes usando um aplicativo que consulta constantemente essa mesma tabela.

Normalmente, eu faria algo como (pseudocódigo recebido)...

BEGIN TRANSACTION
TRUNCATE TABLE
INSERT INTO
COMMIT

Mas infelizmente a tabela não pode ser lida durante este processo; devido ao tempo que leva INSERT INTOpara ser concluído. A mesa está bloqueada.

No MySQL, eu teria usado o RENAME TABLEcomando atômico para evitar esses problemas...

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;

Como eu poderia conseguir isso no PostgreSQL?

Para os propósitos desta questão, você pode assumir que não estou usando chaves estrangeiras.

postgresql

1 respostas

  1. Best Answer

    Certo, o TRUNCATE TABLE comando que você está executando "... adquire um bloqueio ACCESS EXCLUSIVE em cada tabela em que opera ", então no primeiro bloco SQL que você postou, quaisquer outros clientes que tentarem acessar a tabela após esse horário serão bloqueados até que você INSERTtermine e você COMMIT.

    Você pode usar a mesma solução alternativa do código específico do MySQL; O Postgres suporta aproximadamente a mesma sintaxe e terá um comportamento de bloqueio semelhante. A saber:

    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;

    Bônus extra: Postgres realmente suporta DDL transacional, ao contrário do MySQL, portanto, caso você precise fazer o ROLLBACK da transação acima, você pode fazê-lo com segurança.

    • 28

relate perguntas