PostgreSQL. Корректировка следующего значения полей SMALLSERIAL, SERIAL и BIGSERIAL

    У PostgreSQL, как и у многих других СУБД, есть возможность создавать в таблицах автоинкрементные столбцы. Для этого предназначены типы данных SMALLSERIAL, SERIAL и BIGSERIAL. К сожалению, авторы PostgreSQL не сделали никаких ограничений на прямую запись в столбцы этих типов. С одной стороны – это удобно. В таблицу можно записать данные, у которых уже есть значения для этого столбца. Но с другой стороны – это большая проблема. Такие действия могут привести к дублированию или к ошибке, когда позже при обычной вставке новой записи в таблицу СУБД попытается заполнить поле автоматически. Для сравнения, можно привести как продуманно это реализовано в MS SQL Server. Что бы записать значение в столбец, помеченный как IDENTITY, нужно это разрешить специальной командой "SET IDENTITY_INSERT": вызываем "SET IDENTITY_INSERT имя_таблицы ON", вставляем нужные записи и вызываем "SET IDENTITY_INSERT имя_таблицы OFF". При этом СУБД сама скорректирует текущее значение счетчика на максимальное значение столбца. Для PostgreSQL эту корректировку надо произвести вручную.

    Решение этой задачи исходит из самой реализации автоинкрементных столбцов в PostgreSQL. При создании таблицы со столбцом, имеющим тип SMALLSERIAL, SERIAL или BIGSERIAL, в таблице создается целочисленный столбец (соответственно SMALLINT, INT или BIGINT) и генератор последовательности с названием "имя_таблицы_имя_столбца_seq", на который ссылается DEFAULT-ограничение этого столбца. То есть команда

CREATE TABLE имя_таблицы 
(
  имя_столбца SERIAL
);

равнозначна следующему набору команд:

CREATE SEQUENCE имя_таблицы_имя_столбца_seq AS INT;
CREATE TABLE имя_таблицы 
(
  имя_столбца INT NOT NULL DEFAULT NEXTVAL('имя_таблицы_имя_столбца_seq')
);
ALTER SEQUENCE имя_таблицы_имя_столбца_seq OWNED BY имя_таблицы.имя_столбца;

Последняя команда определяет, что последовательность "принадлежит" столбцу таблицы и она будет удалена при удалении этого столбца или таблицы.

    Значит алгоритм корректировки текущего значения счетчика автоинкрементного столбца такой: получаем максимальное значение столбца и присваиваем его текущему значению последовательности. Рассмотрим его реализацию на примере.

    Создадим три таблицы (три схемы – для последнего скрипта) и вставим в них по одной строке, не заполняя поле id. Последнюю таблицу очистим.

CREATE SCHEMA x;
CREATE TABLE x.xyz(id SERIAL PRIMARY KEY, name TEXT);
CREATE SCHEMA y;
CREATE TABLE y.xyz(id SERIAL PRIMARY KEY, name TEXT);
CREATE SCHEMA e;
CREATE TABLE e.xyz(id SERIAL PRIMARY KEY, name TEXT);

INSERT INTO x.xyz(name) VALUES ('строка #1');
INSERT INTO y.xyz(name) VALUES ('строка #1');
INSERT INTO e.xyz(name) VALUES ('строка #1');

TRUNCATE TABLE e.xyz;

У всех таблиц полю id при вставке автоматически было присвоено 1, а следующее значение для последовательности – 2:

Добавим в таблицы x.xyz и y.xyz еще по 3 строки с указанием id:

INSERT INTO x.xyz(id, name) VALUES (2, 'строка #2');
INSERT INTO x.xyz(id, name) VALUES (4, 'строка #4');
INSERT INTO x.xyz(id, name) VALUES (6, 'строка #6');

INSERT INTO y.xyz(id, name) VALUES (12, 'строка #12');
INSERT INTO y.xyz(id, name) VALUES (14, 'строка #14');
INSERT INTO y.xyz(id, name) VALUES (16, 'строка #16');

Как видите, это никак не повлияло на последовательности, которые генерируют значение для столбца id:

    "Починить" последовательность можно реализовав приведенный выше алгоритм. Для таблицы x.xyz SQL-команда будет выглядеть так:

SELECT SETVAL('x.xyz_id_seq', MAX(id)) FROM x.xyz;

Это работает. Но допустим вызов SETVAL производится процедурой импорта, и эта процедура не знает, что в пустую таблицу ничего не добавилось. MAX(id) для пустой таблицы e.xyz вернет NULL и текущее значение последовательности e.xyz_id_seq останется прежним (в нашем примере на данный момент – 3). Замена "MAX(id)" на "COALESCE(MAX(id), 1))" приведет к тому, что текущее значение последовательности e.xyz_id_seq станет равным 1 и вызов NEXTVAL вернет 2. Лучше, но не идеально. Хотелось бы получить начальное значение последовательности – 1. Замена на "COALESCE(MAX(id), 0))" приведет к ошибке "setval передано значение 0 вне пределов последовательности xyz_id_seq (1..2147483647)" (напомню, что последовательности по умолчанию начинаются с 1). Нам поможет третий параметр функции SETVAL. Это флаг (имеет тип boolean), который указывает на то, что использовалось или нет значение, передаваемое во втором параметре. По умолчанию этот флаг равен true, поэтому передавая в SETVAL 1 мы при вызове NEXTVAL получаем 2. Передадим в SETVAL максимальное значение id увеличенное на 1 и false:

SELECT SETVAL('e.xyz_id_seq', COALESCE(MAX(id), 0) + 1, false) FROM e.xyz;

Теперь вызов NEXTVAL для пустой таблицы будет возвращать 1.

    На этом можно и остановиться. Но, давайте рассмотрим вариант, когда в базе несколько десятков таблиц с автоинкрементными столбцами, или несколько сотен, а мы скопировали все данные из другой базы данных. Прописывать для каждой таблицы отдельный вызов SETVAL? Думаю, что лучше написать скрипт, который достанет из базы данных перечень всех таблиц с автоинкрементными столбцами и выполнит для их последовательностей SETVAL:

do $$
declare
  rec record;
begin
  for rec in
    select quote_ident(sch.nspname) || '.' || quote_ident(tab.relname) as table_name, 
           quote_ident(col.attname) as column_name, 
           quote_ident(sch.nspname) || '.' || quote_ident(seq.relname) as sequence_name
      from pg_class seq,     -- список последовательностей
           pg_depend dep,    -- список связей
           pg_class tab,     -- список таблиц
           pg_namespace sch, -- список схем
           pg_attribute col  -- список столбцов таблицы
     where seq.relkind = 'S' -- выбираем последовательности
       and dep.objid = seq.oid -- objid - OID зависимого объекта (последовательности)
       and dep.refobjsubid <> 0 -- refobjsubid - номер столбца (0 - для всех других типов объектов)
       and tab.oid = dep.refobjid -- refobjid - OID вышестоящего объекта (таблицы)
       and sch.oid = tab.relnamespace -- relnamespace - OID схемы содержащей таблицу
       --and sch.nspname = 'x' -- наименование схемы содержащей таблицу
       and col.attrelid = tab.oid -- attrelid - OID таблицы содержащей столбец
       and col.attnum = dep.refobjsubid -- attnum и refobjsubid - порядковый номер столбца
  loop
    execute 'select SETVAL(''' || rec.sequence_name || ''', COALESCE(MAX(' || rec.column_name || '), 0) + 1, false) from ' || rec.table_name;
  end loop;
end $$;

Выполним скрипт на наших тестовых таблицах и убедимся в корректности его работы: