Возвращать строки из 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

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

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

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

  • В чем причина недопустимости моего (желаемого) поведения?

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

Ответы

Ответ 1

Это повторяющаяся проблема 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 установлено на то же значение, что и раньше. Это сделало бы операцию более дорогостоящей, добавив к возможным concurrency проблемам/блокировке в определенных ситуациях и раздув таблицу дополнительно.

Подробное объяснение и как обернуть это в функцию:

Почему "исключенные" строки не включены в предложение RETURNING?

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

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;