DBA: transferencia de valores de SECUENCIA entre bases de datos PostgreSQL

¿Cómo puedo transferir a la otra base de datos PostgreSQL el último valor asignado del campo "incremento automático" del tipo de serie si podría haber eliminaciones en la tabla, y "simplemente sustituir max (pk)" ya no funciona?

Pocas personas saben que, aunque PG no proporciona funciones de hasta la versión 10 para averiguar el último valor de secuencia para dicho campo desde otra sesión , todavía se puede hacer.



PostgreSQL admite campos de "incremento automático" definidos por el pseudotipo serial :

Los tipos de datos smallserial, serial y bigserial no son tipos reales, pero son solo una herramienta conveniente para crear columnas con identificadores únicos (similar a la propiedad AUTO_INCREMENT en algunos DBMS).

Técnicamente, esto es solo un enlace automático de la definición PREDETERMINADA para el campo y los valores generados por el objeto SEQUENCE . Y a veces quiero analizar estos valores, por ejemplo, transferirlos a una base de datos de estructura similar .

Pero esto no es fácil de hacer:

currval
Devuelve el valor devuelto por la última llamada nextval para esta secuencia en la sesión actual. (Si nextval nunca se ha llamado para una secuencia dada en una sesión dada, se devuelve un error). Dado que este valor está limitado por el alcance de la sesión, esta función produce un resultado predecible, independientemente de si nextval se llamó posteriormente en otras sesiones o no.

Sin embargo, intentemos extraer esta información:

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 es el valor id = 4 que queremos obtener. Pero alguien eliminó parte de los registros, y en la tabla ya no está:

 DELETE FROM tst WHERE id > 2; 

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

Primero, descubriremos el nombre de la secuencia correspondiente a nuestro campo:

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

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

Ahora use el nombre de secuencia resultante como una tabla en la consulta:

 SELECT * FROM public.tst_id_seq; 

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

En realidad, el campo last_value también almacena el "último" valor que la secuencia logró generar en el momento de nuestra llamada.

Ahora construyamos un script simple para transferir valores de secuencia de una base de datos a otra usando el 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 !~ '^_'; --      

Pero recuerde que si hay alguna actividad en la base de origen, ¡el resultado será indefinido!