DBA: transferindo valores de SEQUENCE entre bancos de dados PostgreSQL

Como posso transferir para o outro banco de dados PostgreSQL o último valor atribuído do campo “incremento automático” do tipo serial, se houver alguma exclusão na tabela, e “apenas substituir max (pk)” não funcionar mais?

Poucas pessoas sabem que, embora o PG não forneça funções da versão 10 para descobrir o último valor de sequência para esse campo em outra sessão , isso ainda pode ser feito.



O PostgreSQL suporta campos de "incremento automático" definidos pelo pseudótipo serial :

Os tipos de dados smallserial, serial e bigserial não são tipos reais, mas são apenas uma ferramenta conveniente para criar colunas com identificadores exclusivos (semelhantes à propriedade AUTO_INCREMENT em alguns DBMSs).

Tecnicamente, isso é apenas um link automático da definição DEFAULT para o campo e os valores gerados pelo objeto SEQUENCE . E, às vezes, quero examinar esses valores - por exemplo, transferi-los para um banco de dados de estrutura semelhante .

Mas isso não é fácil de fazer:

currval
Retorna o valor retornado da última chamada nextval para esta sequência na sessão atual. (Se nextval nunca tiver sido chamado para uma determinada sequência em uma determinada sessão, um erro será retornado.) Como esse valor é limitado pelo escopo da sessão, essa função fornece um resultado previsível, independentemente de o nextval ter sido chamado posteriormente em outras sessões ou não.

Vamos tentar extrair essas informações, no entanto:

CREATE TABLE tst( id serial --   , val integer ); INSERT INTO tst(val) VALUES(1),(2),(4),(8); 

 TABLE tst; 

 id | val -------- 1 | 1 2 | 2 3 | 4 4 | 8 

Este é o valor id = 4 que queremos obter. Mas alguém excluiu parte dos registros e, na tabela, não existe mais:

 DELETE FROM tst WHERE id > 2; 

 id | val -------- 1 | 1 2 | 2 

Primeiro, descobriremos o nome da sequência correspondente ao nosso campo:

 SELECT pg_get_serial_sequence('tst', 'id'); 

 pg_get_serial_sequence ---------------------- public.tst_id_seq 

Agora use o nome da sequência resultante como uma tabela na consulta:

 SELECT * FROM public.tst_id_seq; 

 last_value | log_cnt | is_called -------------------------------- 4 | 29 | t 

Na verdade, o campo last_value também armazena o valor “last” que a sequência conseguiu gerar no momento da nossa chamada.

Agora vamos criar um script simples para transferir valores de sequência de um banco de dados para outro usando o módulo dblink :

 --   ,    SELECT ( SELECT nlv FROM dblink( 'host=... port=5432 dbname=... user=... password=...' , $q$ SELECT setval(pg_get_serial_sequence('$q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_table) || $q$', '$q$ || sequence_column || $q$'), $q$ || lv || $q$) $q$ ) T(nlv bigint) --   ) nlv , * FROM ( SELECT ( SELECT relname FROM pg_class WHERE oid = (dp).refobjid ) sequence_table , ( SELECT attname FROM pg_attribute WHERE (attrelid, attnum) = ((dp).refobjid, (dp).refobjsubid) ) sequence_column , * FROM ( SELECT --    - ( SELECT lv FROM dblink( 'dbname=' || current_database() , $q$ SELECT last_value FROM $q$ || quote_ident(sequence_schema) || $q$.$q$ || quote_ident(sequence_name) || $q$ $q$ ) T(lv bigint) --   ,   ""- ) lv , ( SELECT dp FROM pg_depend dp WHERE (classid, objid, refclassid, deptype) = ('pg_class'::regclass, (quote_ident(sequence_schema) || '.' || quote_ident(sequence_name))::regclass, 'pg_class'::regclass, 'a') LIMIT 1 ) dp --      , * FROM information_schema.sequences WHERE sequence_schema IN ('public') --    ) T ) T WHERE sequence_table !~ '^_'; --      

Mas lembre-se de que, se houver alguma atividade na base de origem, o resultado será indefinido!