DBA: перенос значений SEQUENCE между базами PostgreSQL

Как можно перенести в другую PostgreSQL-базу последнее назначавшееся значение «автоинкремент»-поля типа serial, если в таблице могли быть какие-то удаления, и «просто подставить max(pk)» уже не подходит?

Мало кто знает, что хоть PG и не предоставляет до версии 10 функций, чтобы узнать последнее значение последовательности для такого поля из другого сеанса, это все-таки можно сделать.



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 !~ '^_'; -- исключаемые из синхронизации последовательностей таблицы

Но помните, что при наличии какой-то активности на базе-источнике результат будет неопределен!