DBA: mentransfer nilai SEQUENCE antara database PostgreSQL

Bagaimana saya bisa mentransfer ke database PostgreSQL lain nilai ditugaskan terakhir dari bidang "auto-increment" dari jenis serial jika ada penghapusan dalam tabel, dan "hanya mengganti max (pk)" tidak lagi berfungsi?

Hanya sedikit orang yang tahu bahwa meskipun PG tidak menyediakan hingga versi 10 fungsi untuk mengetahui nilai terakhir dari urutan untuk bidang seperti itu dari sesi lain , ini masih bisa dilakukan.



PostgreSQL mendukung bidang "kenaikan otomatis" yang ditentukan oleh pseudotipe serial :

Tipe data smallserial, serial, dan bigserial bukan tipe nyata, tetapi hanya alat yang nyaman untuk membuat kolom dengan pengidentifikasi unik (mirip dengan properti AUTO_INCREMENT di beberapa DBMS).

Secara teknis, ini hanya tautan otomatis definisi DEFAULT untuk bidang dan nilai-nilai yang dihasilkan oleh objek SEQUENCE . Dan kadang-kadang saya ingin melihat nilai-nilai ini - misalnya, untuk mentransfernya ke database dalam struktur yang sama .

Tapi ini tidak mudah dilakukan:

saat ini
Mengembalikan nilai yang dikembalikan dari panggilan nextval terakhir untuk urutan ini di sesi saat ini. (Jika nextval tidak pernah dipanggil untuk urutan tertentu dalam sesi yang diberikan, kesalahan dikembalikan.) Karena nilai ini dibatasi oleh cakupan sesi, fungsi ini menghasilkan hasil yang dapat diprediksi, terlepas dari apakah nextval selanjutnya dipanggil dalam sesi lain atau tidak.

Namun, mari kita coba mengeluarkan informasi ini:

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 

Ini adalah nilai id = 4 yang ingin kita dapatkan. Tetapi seseorang menghapus sebagian dari catatan, dan dalam tabel itu tidak ada lagi:

 DELETE FROM tst WHERE id > 2; 

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

Pertama, kami akan menemukan nama urutan yang sesuai dengan bidang kami:

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

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

Sekarang gunakan nama urutan yang dihasilkan sebagai tabel dalam kueri:

 SELECT * FROM public.tst_id_seq; 

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

Sebenarnya, bidang last_value juga menyimpan nilai "terakhir" yang berhasil dihasilkan oleh urutan pada saat panggilan kami.

Sekarang mari kita membangun skrip sederhana untuk mentransfer nilai urutan dari satu database ke yang lain menggunakan 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 !~ '^_'; --      

Tetapi ingat bahwa jika ada aktivitas di pangkalan sumber, hasilnya akan tidak ditentukan!