Вернуть строки из INSERT с ON CONFLICT без необходимости обновления

У меня есть ситуация, когда мне очень часто нужно получить строку из таблицы с уникальным ограничением, и если ее нет, создать ее и вернуться. Например, моя таблица может быть:

CREATE TABLE names(
    id SERIAL PRIMARY KEY,
    name TEXT,
    CONSTRAINT names_name_key UNIQUE (name)
);

И он содержит:

id | name
 1 | bob 
 2 | alice

Тогда я хотел бы:

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT DO NOTHING RETURNING id;

Или, возможно:

 INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT (name) DO NOTHING RETURNING id

и пусть он возвращает идентификатор боба 1. Однако RETURNING возвращает только вставленные или обновленные строки. Итак, в приведенном выше примере он ничего не вернет. Чтобы он функционировал как хотелось бы, мне действительно нужно:

INSERT INTO names(name) VALUES ('bob') 
ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE
SET name = 'bob'
RETURNING id;

что кажется несколько громоздким. Я думаю, что мои вопросы:

  1. Какова причина запрета на (моё) желаемое поведение?

  2. Есть ли более элегантный способ сделать это?


postgresql sql postgresql-9.5 upsert
person ira    schedule 29.10.2016    source источник

Ответы (1)


arrow_upward
25
arrow_downward

Это повторяющаяся проблема SELECT or INSERT, связанная с UPSERT (но отличающаяся от нее). Новая функциональность UPSERT в Postgres 9.5 по-прежнему полезна.

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO UPDATE
   SET    name = NULL
   WHERE  FALSE      -- never executed, but locks the row
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;

Таким образом, вы фактически не пишете новую версию строки без необходимости.

Я полагаю, вы знаете, что в Postgres каждый UPDATE записывает новую версию строки из-за ее модель MVCC - даже если name установлено то же значение, что и раньше. Это сделало бы операцию более дорогостоящей, добавило бы возможных проблем с параллелизмом/конкуренцией за блокировку в определенных ситуациях и дополнительно увеличило бы размер таблицы.

Однако по-прежнему существует маленький угловой случай для состояния гонки. Параллельные транзакции могли добавить конфликтующую строку, которая еще не видна в той же инструкции. Затем INSERT и SELECT оказываются пустыми.

Правильное решение для однострочного UPSERT:

Общие решения для массовых UPSERT:

Без параллельной загрузки записи

Если одновременная запись (из другого сеанса) невозможна, вам не нужно блокировать строку и можно упростить:

WITH ins AS (
   INSERT INTO names(name)
   VALUES ('bob')
   ON     CONFLICT ON CONSTRAINT names_name_key DO NOTHING  -- no lock needed
   RETURNING id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM names
WHERE  name = 'bob'  -- only executed if no INSERT
LIMIT  1;
person Erwin Brandstetter    schedule 30.10.2016
comment
Спасибо за ответ! Я вижу, что это кажется «лучшим» способом сделать это, но я не уверен, в чем практическая разница с методом, который я описал? - person ira; 31.10.2016
comment
@ira: я добавил еще несколько пояснений выше. - person Erwin Brandstetter; 31.10.2016
comment
@ErwinBrandstetter - всегда ли это возвращает идентификатор? Я пытался, и это не сработало -- stackoverflow.com/q/46586793/435563 -- возможно, я делать что-то не так? - person shaunc; 05.10.2017
comment
Хорошо... так что ошибка с вышеизложенным, я думаю, такова: SET name = NULL where false не получит блокировку, если name уже null. - person shaunc; 06.10.2017
comment
Это не так, путаница была устранена в stackoverflow.com/q/46586793/435563. - person Erwin Brandstetter; 28.05.2020