Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение «автоинкремент»-поля типа serial, если в таблице могли быть какие-то удаления, и «просто подставить max(pk)» уже не подходит?
Мало кто знает, что хоть PG и не предоставляет до версии 10 функций, чтобы узнать последнее значение последовательности для такого поля из другого сеанса, это все-таки можно сделать.
![](https://habrastorage.org/r/w1560/webt/2w/b4/1a/2wb41aanxsejoysm3kvpyxdjfjk.png)
PostgreSQL поддерживает «автоинкремент»-поля, определяемые псевдотипом serial:
Но сделать это непросто:
Давайте попробуем эту информацию все-таки вытащить:
Вот это значение
Сначала узнаем, как называется последовательность, соответствующая нашему полю:
Теперь используем полученное имя последовательности в качестве таблицы в запросе:
Собственно, поле last_value и хранит то самое «последнее» значение, которое успела сгенерировать последовательность на момент нашего вызова.
Теперь соберем простой скрипт для переноса значений последовательностей из одной базы в другую с помощью модуля dblink:
Но помните, что при наличии какой-то активности на базе-источнике результат будет неопределен!
Мало кто знает, что хоть PG и не предоставляет до версии 10 функций, чтобы узнать последнее значение последовательности для такого поля из другого сеанса, это все-таки можно сделать.
![](https://habrastorage.org/webt/2w/b4/1a/2wb41aanxsejoysm3kvpyxdjfjk.png)
PostgreSQL поддерживает «автоинкремент»-поля, определяемые псевдотипом serial:
Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД).Технически, это всего лишь автоматическая увязка DEFAULT-определения для поля и генерируемых объектом SEQUENCE значений. И иногда в эти значения хочется заглянуть — например, для переноса в аналогичную по структуре базу.
Но сделать это непросто:
currval
Возвращает значение, выданное при последнем вызове nextval для этой последовательности в текущем сеансе. (Если в данном сеансе nextval ни разу не вызывалась для данной последовательности, возвращается ошибка.) Так как это значение ограничено рамками сеанса, эта функция выдаёт предсказуемый результат вне зависимости от того, вызвалась ли впоследствии nextval в других сеансах или нет.
Системное представление pg_sequences
Начиная с PostgreSQL 10 появилось системное представление pg_sequences, в котором эта информация уже видна без особых усилий.
Давайте попробуем эту информацию все-таки вытащить:
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
Вот это значение
id = 4
мы и хотим получить. Но кто-то удалил часть записей, и в таблице его больше нет:DELETE FROM tst WHERE id > 2;
id | val
--------
1 | 1
2 | 2
Сначала узнаем, как называется последовательность, соответствующая нашему полю:
SELECT pg_get_serial_sequence('tst', 'id');
pg_get_serial_sequence
----------------------
public.tst_id_seq
Теперь используем полученное имя последовательности в качестве таблицы в запросе:
SELECT * FROM public.tst_id_seq;
last_value | log_cnt | is_called
--------------------------------
4 | 29 | t
Собственно, поле last_value и хранит то самое «последнее» значение, которое успела сгенерировать последовательность на момент нашего вызова.
Теперь соберем простой скрипт для переноса значений последовательностей из одной базы в другую с помощью модуля 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 !~ '^_'; -- исключаемые из синхронизации последовательностей таблицы
Но помните, что при наличии какой-то активности на базе-источнике результат будет неопределен!