Русский
Русский
English
Статистика
Реклама

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

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


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


Казалось бы чего проще? Заводим отдельную табличку, в ней запись со счетчиком. Надо получить новый идентификатор читаем оттуда, чтобы записать новое значение делаем 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
Источник: habr.com
К списку статей
Опубликовано: 20.08.2020 10:19:07
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Программирование

Sql

Администрирование баз данных

Идентификаторы

Sequence

Serial

Generated

Uuid

Tableoid

Ctid

Clock_timestamp

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru