ON CONFLICT DO SELECT в PostgreSQL 19: get-or-create одним запросом
Get-or-create в PostgreSQL до сих пор пишется в обход. Задача знакомая: нужен id тега, пользователя или ключа идемпотентности, и если строка уже есть, следует вернуть её, а если нет — создать. Очевидный вариант с DO NOTHING не подходит, потому что RETURNING отдаёт только вставленные строки, и на существующую запрос ответит пустым результатом.
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO NOTHING
RETURNING id; -- тег уже есть: 0 строк
Дальше обычно выбирают из двух неудобных вариантов. Второй SELECT означает лишний поход в базу и окно, за которое строку могут удалить. Холостой DO UPDATE SET name = EXCLUDED.name заставляет RETURNING работать, но PostgreSQL выполняет физическое обновление, даже если данные не изменились. Каждый вызов оставляет новую версию строки, мёртвый кортеж для VACUUM и запись в WAL, а если HOT не сработал, ещё и правки во всех индексах. Вдобавок срабатывают триггеры на UPDATE, и аудит фиксирует изменения, которых не было.
В PostgreSQL 19 появилось третье действие при конфликте — DO SELECT. Существующая строка возвращается как есть, без записи:
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id, old.id IS NULL AS created;
Вторая колонка опирается на ссылку old в RETURNING, её добавили в версии 18. У вставленной строки старые значения равны NULL, у найденной заполнены, так что приложение сразу понимает, создало оно запись или получило существующую.
От DO NOTHING конструкция отличается двумя требованиями: RETURNING обязателен, а цель конфликта нужно назвать явно. Арбитром может быть только уникальный индекс или ограничение NOT DEFERRABLE, а exclusion constraints не подходят.
Если найденную строку дальше меняют в той же транзакции, её можно заблокировать в этом же запросе:
INSERT INTO balances (account_id, amount) VALUES (42, 0)
ON CONFLICT (account_id) DO SELECT FOR UPDATE
RETURNING *;
Поддерживаются все четыре режима блокировки, от FOR UPDATE до FOR KEY SHARE, и условие WHERE, по которому отбираются возвращаемые строки. Блокировку получат все конфликтующие строки, в том числе те, что под условие не попали.
PostgreSQL 19 сейчас в четвёртой бете, вышедшей 24 сентября, а релиз-кандидат обещают в начале октября. В этой бете из релиза убрали SQL/PGQ, FOR PORTION OF и операции над партициями, но DO SELECT в документации версии 19 остался. Холостые DO UPDATE в коде стоит пометить уже сейчас: после обновления каждый из них заменяется одной правкой, и таблицы с частым get-or-create перестанут копить мёртвые кортежи.
#backendvkhub #postgresql
Get-or-create в PostgreSQL до сих пор пишется в обход. Задача знакомая: нужен id тега, пользователя или ключа идемпотентности, и если строка уже есть, следует вернуть её, а если нет — создать. Очевидный вариант с DO NOTHING не подходит, потому что RETURNING отдаёт только вставленные строки, и на существующую запрос ответит пустым результатом.
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO NOTHING
RETURNING id; -- тег уже есть: 0 строк
Дальше обычно выбирают из двух неудобных вариантов. Второй SELECT означает лишний поход в базу и окно, за которое строку могут удалить. Холостой DO UPDATE SET name = EXCLUDED.name заставляет RETURNING работать, но PostgreSQL выполняет физическое обновление, даже если данные не изменились. Каждый вызов оставляет новую версию строки, мёртвый кортеж для VACUUM и запись в WAL, а если HOT не сработал, ещё и правки во всех индексах. Вдобавок срабатывают триггеры на UPDATE, и аудит фиксирует изменения, которых не было.
В PostgreSQL 19 появилось третье действие при конфликте — DO SELECT. Существующая строка возвращается как есть, без записи:
INSERT INTO tags (name) VALUES ('postgres')
ON CONFLICT (name) DO SELECT
RETURNING id, old.id IS NULL AS created;
Вторая колонка опирается на ссылку old в RETURNING, её добавили в версии 18. У вставленной строки старые значения равны NULL, у найденной заполнены, так что приложение сразу понимает, создало оно запись или получило существующую.
От DO NOTHING конструкция отличается двумя требованиями: RETURNING обязателен, а цель конфликта нужно назвать явно. Арбитром может быть только уникальный индекс или ограничение NOT DEFERRABLE, а exclusion constraints не подходят.
Если найденную строку дальше меняют в той же транзакции, её можно заблокировать в этом же запросе:
INSERT INTO balances (account_id, amount) VALUES (42, 0)
ON CONFLICT (account_id) DO SELECT FOR UPDATE
RETURNING *;
Поддерживаются все четыре режима блокировки, от FOR UPDATE до FOR KEY SHARE, и условие WHERE, по которому отбираются возвращаемые строки. Блокировку получат все конфликтующие строки, в том числе те, что под условие не попали.
PostgreSQL 19 сейчас в четвёртой бете, вышедшей 24 сентября, а релиз-кандидат обещают в начале октября. В этой бете из релиза убрали SQL/PGQ, FOR PORTION OF и операции над партициями, но DO SELECT в документации версии 19 остался. Холостые DO UPDATE в коде стоит пометить уже сейчас: после обновления каждый из них заменяется одной правкой, и таблицы с частым get-or-create перестанут копить мёртвые кортежи.
#backendvkhub #postgresql