Таблица счетчиков
Казалось бы чего проще? Заводим отдельную табличку, в ней запись со счетчиком. Надо получить новый идентификатор читаем оттуда, чтобы записать новое значение делаем
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
