Início / dba / Perguntas / 16909
Accepted
filiprem
Asked: 2012-04-24 10:15:17 +0800 CST

Girar uma tabela no PostgreSQL

  • 772

Descrição do Problema

Preciso rotacionar (com DROP e CREATE) uma tabela que é muito utilizada por outros clientes.

Atualmente, tenho um programa que substitui (DROP + CREATE) esta tabela.

Às vezes, logo após a substituição da tabela, recebo "ERRO: não foi possível abrir a relação com OID xyz" de clientes simultâneos acessando a tabela.

Pode-se dizer que esse comportamento quebra o isolamento da transação... Não é?

Eu entendo que isso é causado por catálogos do sistema sendo armazenados em cache pelo back-end postgres (o que normalmente é uma coisa boa). Estou certo?

Existe alguma maneira de forçar um back-end a "esquecer" os OIDs da tabela ( DISCARD não ajuda)?

Sei que ajudará se eu mudar para o padrão "DELETE and INSERT" em vez do padrão "DROP and CREATE". Mas este é um programa herdado e não queremos alterá-lo, a menos que seja absolutamente necessário.

Ficarei grato por qualquer sugestão de como se livrar desse problema.

O objetivo é rodar a mesa de forma transparente para outros clientes.

o caso de teste

Aqui está o caso de teste mínimo para o qual reduzi o problema:

A. Este é o cliente (vários clientes executarão este SELECT em paralelo). Usaremos o pgbench para estressar o banco de dados.

$ echo "SELECT num FROM x;" > pgbench.minimal.sql

B. Este é o "rotador".

$ cat > rotate.x.sql <<EOF
BEGIN;
DROP TABLE x;
CREATE TABLE x ( num integer );
COMMIT;
EOF

C. criar banco de dados vazio e tabela x.

$ createdb dev
$ psql dev -c "CREATE TABLE x ( num integer )"

D. Iniciar 30 clientes.

$ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql

E. (em outro terminal) Execute o "rotador".

$ psql dev -f rotate.x.sql

F. observe o que acontece com os clientes (logo após os COMMITs "rotator").

Client 4 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^
Client 0 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^

(...e assim por diante - todo cliente falha)

Comentários e ideias

O que é interessante - mesmo que os clientes trabalhem no modo "nova conexão para cada transação" (adicione a opção "-C" ao pgbench), ocorre o mesmo erro. Nem sempre e nem para todos os clientes, mas sim.

Eu fiz esta pergunta há algum tempo na lista de discussão: http://archives.postgresql.org/pgsql-general/2012-04/msg00431.php - este post SO é apenas uma cópia.

Para mais público: A mesma coisa se aplica à rotação de uma partição. Portanto, pode ser interessante para todas as pessoas que usam particionamento.

Isso tudo no PostgreSQL 9.0, Linux.

Solução

(graças a Chris Travers)

filip@srv:~$ cat rotate.x.sql
BEGIN;
DROP TABLE IF EXISTS xold;
ALTER TABLE x RENAME TO xold;
CREATE TABLE x ( num integer );
COMMIT;
postgresql partitioning

2 respostas

  1. Best Answer

    E ALTER TABLE RENAME seguido de CREATE TABLE?

    • 4

  2. Com base no que encontrei aqui :

    Esta é a maneira mais otimizada:

    CREATE TABLE parent ... ;
CREATE TABLE child_1() INHERITS (parent);
CREATE TABLE child_2() INHERITS (parent);

    INSERTos comandos são feitos sempre em arquivos child_1.

    SELECTcomandos são feitos em parent.

    Em seu priod de escolha, execute o seguinte sql para rotacionar child_1com child_2:

    TRUNCATE TABLE child_2;
BEGIN;
ALTER TABLE child_1 RENAME TO child_tmp;
ALTER TABLE child_2 RENAME TO child_1;
ALTER TABLE child_tmp RENAME TO child_2;
COMMIT;

    Isso é muito mais rápido em comparação com

    DELETE FROM parent WHERE time < now() - interval 'given period'

    Isso também evita a fragmentação da tabela, portanto, as consultas SELECT funcionam mais rapidamente na tabela.

    • 0

relate perguntas