Достаточно часто у разработчика возникает потребность формировать для записей таблицы PostgreSQL некие уникальные идентификаторы — как при вставке записей, так и при их чтении.

Таблица счетчиков

Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE…

Так делать не надо! Потому что завтра же вам придется решать проблемы:

• постоянных пересекающихся блокировок при UPDATE
  см. PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»
• постепенной деградации скорости доступа к данным таблицы счетчиков
  см. PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой
• … и необходимости ее зачистки при активных транзакциях, которые будут вам мешать
  см. DBA: когда пасует VACUUM — чистим таблицу вручную

Объект SEQUENCE

Для таких задач в PostgreSQL предусмотрена отдельная сущность — SEQUENCE. Она нетранзакционна, то есть не вызывает блокировок, но две «параллельные» транзакции заведомо получат разные значения.

Чтобы получить следующий ID из последовательности, достаточно воспользоваться функцией nextval:

SELECT nextval('seq_name'::regclass);

Иногда необходимо получить сразу несколько ID — для потоковой записи через COPY, например. Использовать для этого setval(currval() + N) — в корне неправильно! По той простой причине, что между вызовами «внутренней» (currval) и «внешней» (setval) функций конкурирующая транзакция могла изменить текущее значение последовательности. Корректный способ — вызвать nextval нужное количество раз:

SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);

Псевдотип serial

В «ручном» режиме с последовательностями работать не очень удобно. Но ведь типовая задача у нас — обеспечить вставку новой записи с новым sequence-ID! Специально для этой цели в PostgreSQL придуман псевдотип serial, который при генерации таблицы «разворачивается» во что-то типа id integer NOT NULL DEFAULT nextval('tbl_id_seq').

Запоминать имя автоматически сгенерированной и привязанной к полю последовательности — не надо, для этого есть функция pg_get_serial_sequence(table_name, column_name). Эту же функцию можно использовать в собственных DEFAULT-подстановках — например, если есть необходимость сделать общую последовательность на несколько таблиц сразу.

Однако, поскольку работа с последовательностью нетранзакционна, если идентификатор из нее получала rollback'нувшаяся транзакция, то в сохраненных записях таблицы последовательность ID окажется «дырявой».

GENERATED-столбцы

Начиная с PostgreSQL 10, появилась возможность объявления идентифицирующего столбца (GENERATED AS IDENTITY), соответствующего стандарту SQL:2003. В варианте GENERATED BY DEFAULT поведение эквивалентно serial, а вот с GENERATED ALWAYS все интереснее:

CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);

INSERT INTO tbl(id) VALUES(DEFAULT);
-- Запрос успешно выполнен: одна строка изменена за 10 мс.
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

Да, чтобы вставить конкретное значение «поперек» такого столбца, придется приложить дополнительные усилия с помощью OVERRIDING SYSTEM VALUE:

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
-- Запрос успешно выполнен: одна строка изменена за 11 мс.

Заметьте, что теперь у нас в таблице два одинаковых значения id = 1 — то есть GENERATED не накладывает дополнительных UNIQUE-условий и индексов, а является исключительно декларацией, равно как и serial.

В общем случае, на современных версиях PostgreSQL использование serial не рекомендуется с предпочтительной заменой его на GENERATED. Кроме, разве что, ситуации поддержки кросс-версионных приложений, работающих с PG ниже 10.

Генерируемый UUID

Все хорошо, пока вы работаете в рамках одного экземпляра БД. Но когда их несколько, адекватного способа синхронизации последовательностей не существует (впрочем, это не мешает «неадекватно» их синхронизировать, если очень хочется). Тут на помощь приходит тип UUID и функции генерации значений для него. Я обычно использую uuid_generate_v4() как наиболее «случайную».

Скрытые системные поля

tableoid/ctid

Иногда при выборке записей из таблицы требуется как-то адресоваться к конкретной «физической» записи, или узнать, из какой конкретной секции была получена та или иная запись при обращении к «родительской» таблице при использовании наследования.

В этом случае нам помогут скрытые системные поля, присутствующие в каждой записи:

• tableoid хранит oid-идентификатор таблицы — то есть tableoid::regclass::text дает имя конкретной таблицы-секции
• ctid — «физический» адрес записи в формате (<страница>,<смещение>)

Например, ctid можно использовать при операциях с таблицей без первичного ключа, а tableoid — для реализации определенных видов внешних ключей.

oid

Вплоть до PostgreSQL 11 существовала возможность объявить при создании таблицы атрибут WITH OIDS:

CREATE TABLE tbl(id serial) WITH OIDS;

Каждая запись этой таблицы получала дополнительное скрытое поле oid с глобально-уникальным значением в рамках БД — как это организовано для системных таблиц вроде pg_class, pg_namespace,…

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

INSERT INTO tbl(id) VALUES(DEFAULT);

Запрос успешно выполнен: строка с OID 16400 добавлена за 11 мс.

Такое поле невидимо при «обычном» запросе таблицы:

SELECT * FROM tbl;

id
--
 1

Его, как и остальные системные поля надо запрашивать в явном виде:

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1

Правда, значение oid имеет всего 32 бита, поэтому весьма несложно получить переполнение, после которого даже создать никакую таблицу (ей нужен новый oid!) не удастся. Поэтому, начиная с PostgreSQL 12, WITH OIDS более не поддерживается.

«Честное» время clock_timestamp

Иногда при длительном выполнении запроса или процедуры хочется привязать к записи «текущее» время. Неудача ждет того, кто попытается для этого использовать функцию now() — она возвратит одно и то же значение в рамках всей транзакции.

Чтобы получить «вот прямо текущее» время, существует функция clock_timestamp() (и еще пучок ее собратьев). Чем отличается поведение этих функций можно увидеть на примере простого запроса:

SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);

              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03