DBA: transfert de valeurs SEQUENCE entre des bases de données PostgreSQL

Comment puis-je transférer vers l'autre base de données PostgreSQL la dernière valeur affectée au champ "auto-incrémentation" du type série s'il peut y avoir des suppressions dans la table, et "simplement remplacer max (pk)" ne fonctionne plus?

Peu de gens savent que même si PG ne fournit pas jusqu'à la version 10 des fonctions pour trouver la dernière valeur de séquence pour un tel champ à partir d'une autre session , cela peut toujours être fait.



PostgreSQL prend en charge les champs "auto-incrémentés" définis par le pseudotype série :

Les types de données smallserial, serial et bigserial ne sont pas de vrais types, mais sont juste un outil pratique pour créer des colonnes avec des identificateurs uniques (similaires à la propriété AUTO_INCREMENT dans certains SGBD).

Techniquement, il s'agit simplement d'une liaison automatique de la définition DEFAULT du champ et des valeurs générées par l'objet SEQUENCE . Et parfois, je veux examiner ces valeurs - par exemple, pour les transférer dans une base de données de structure similaire .

Mais ce n'est pas facile à faire:

currval
Renvoie la valeur renvoyée par le dernier appel nextval pour cette séquence dans la session en cours. (Si nextval n'a jamais été appelé pour une séquence donnée dans une session donnée, une erreur est renvoyée.) Comme cette valeur est limitée par la portée de la session, cette fonction produit un résultat prévisible, que nextval ait été appelé ultérieurement dans d'autres sessions ou non.

Essayons néanmoins d'extraire ces informations:

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 

C'est la valeur id = 4 que nous voulons obtenir. Mais quelqu'un a supprimé une partie des enregistrements, et dans le tableau, ce n'est plus:

 DELETE FROM tst WHERE id > 2; 

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

Tout d'abord, nous allons trouver le nom de la séquence correspondant à notre champ:

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

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

Utilisez maintenant le nom de séquence résultant comme table dans la requête:

 SELECT * FROM public.tst_id_seq; 

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

En fait, le champ last_value stocke également la toute «dernière» valeur que la séquence a réussi à générer au moment de notre appel.

Maintenant, construisons un script simple pour transférer des valeurs de séquence d'une base de données à une autre à l' aide du module 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 !~ '^_'; --      

Mais rappelez-vous que s'il y a une activité sur la base source, le résultat ne sera pas défini!