[PostgreSQL, Программирование, SQL, Администрирование баз данных] PostgreSQL Antipatterns: уникальные идентификаторы

Ответить на тему

Автор

Сообщение

news_bot ^®

Стаж: 6 лет 9 месяцев
Сообщений: 27286

news_bot ^® написал(а)
20-Авг-2020 12:35

Цитировать

Достаточно часто у разработчика возникает потребность формировать для записей таблицы 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

===========
Источник:
habr.com
===========
Похожие новости:

Теги для поиска: #_postgresql, #_programmirovanie (Программирование), #_sql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_sql, #_identifikatory (идентификаторы), #_sequence, #_serial, #_generated, #_uuid, #_tableoid, #_ctid, #_clock_timestamp, #_blog_kompanii_tenzor (
Блог компании Тензор
), #_postgresql, #_programmirovanie (
Программирование
), #_sql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)

Профиль ЛС

Ответить на тему

Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы

Текущее время: 22-Ноя 16:12
Часовой пояс: UTC + 5