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

Question

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

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. Есть ли более элегантный способ сделать это?

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

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

• SELECT или INSERT в функции склонны к условиям гонки?
• Как использовать RETURNING с ON CONFLICT в PostgreSQL?
• Вернуть строки из INSERT с ON CONFLICT без необходимости обновления

Почему "исключенные" строки не включены в 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;