DBA: Übertragung von SEQUENCE-Werten zwischen PostgreSQL-Datenbanken

Wie kann ich den zuletzt zugewiesenen Wert des Feldes "Auto-Increment" des seriellen Typs in die andere PostgreSQL-Datenbank übertragen, wenn die Tabelle Löschungen enthalten könnte und "nur noch max (pk)" nicht mehr funktioniert?

Es ist nur wenigen bekannt, dass PG zwar nicht über Funktionen der Version 10 verfügt , um den letzten Wert einer Sequenz für ein solches Feld aus einer anderen Sitzung herauszufinden , dies jedoch dennoch möglich ist.



PostgreSQL unterstützt "Auto-Inkrement" -Felder, die durch den seriellen Pseudotyp definiert sind :

Die Datentypen "smallserial", "serial" und "bigserial" sind keine reellen Typen, sondern lediglich ein praktisches Tool zum Erstellen von Spalten mit eindeutigen Bezeichnern (ähnlich der AUTO_INCREMENT-Eigenschaft in einigen DBMS).

Technisch gesehen ist dies nur eine automatische Verknüpfung der DEFAULT-Definition für das Feld und der vom SEQUENCE- Objekt generierten Werte. Und manchmal möchte ich diese Werte untersuchen - zum Beispiel, um sie in eine Datenbank mit ähnlicher Struktur zu übertragen .

Dies ist jedoch nicht einfach:

currval
Gibt den Wert zurück, der vom letzten nextval-Aufruf für diese Sequenz in der aktuellen Sitzung zurückgegeben wurde. (Wenn nextval in einer bestimmten Sitzung noch nie für eine bestimmte Sequenz aufgerufen wurde, wird ein Fehler zurückgegeben.) Da dieser Wert durch den Umfang der Sitzung begrenzt ist, liefert diese Funktion ein vorhersagbares Ergebnis, unabhängig davon, ob nextval später in anderen Sitzungen aufgerufen wurde oder nicht.

Versuchen wir doch diese Informationen herauszuholen:

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 

Dies ist der Wert id = 4 , den wir erhalten möchten. Aber jemand hat einen Teil der Datensätze gelöscht, und in der Tabelle ist es nicht mehr:

 DELETE FROM tst WHERE id > 2; 

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

Zuerst ermitteln wir den Namen der Sequenz, die unserem Feld entspricht:

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

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

Verwenden Sie nun den resultierenden Sequenznamen als Tabelle in der Abfrage:

 SELECT * FROM public.tst_id_seq; 

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

Tatsächlich speichert das last_value- Feld auch den allerletzten Wert, den die Sequenz zum Zeitpunkt unseres Aufrufs generiert hat.

Erstellen wir nun ein einfaches Skript zum Übertragen von Sequenzwerten von einer Datenbank in eine andere mit dem Modul 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 !~ '^_'; --      

Denken Sie jedoch daran, dass das Ergebnis undefiniert ist, wenn eine Aktivität in der Quellbasis vorhanden ist!