PostgreSQL Antipatterns: уникальные идентификаторы20.08.2020 11:47

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

qavrxooxbk3yyahwviyoabkdinw.png


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


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

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


Объект 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

© Habrahabr.ru