Как использовать ВОЗВРАТ с ON CONFLICT в PostgreSQL?

У меня была точно такая же проблема, и я решил ее, используя «обновить» вместо «ничего не делать», хотя мне нечего было обновлять. В вашем случае это будет примерно так:

INSERT INTO chats ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") 
DO UPDATE SET 
    name=EXCLUDED.name 
RETURNING id;

Этот запрос вернет все строки, независимо от того, были ли они только что вставлены или существовали раньше.

принятый в настоящее время ответ кажется приемлемым для единственной цели конфликта, нескольких конфликтов, небольших кортежей и отсутствия триггеров. Он позволяет избежать проблемы параллелизма 1 (см. Ниже) с помощью грубой силы. Простое решение имеет свою привлекательность, побочные эффекты могут быть менее важными.

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

• Это может привести к срабатыванию триггеров, которые не следует запускать.

• Он блокирует запись невинных строк, что может повлечь за собой затраты на одновременные транзакции.

• Строка может показаться новой, хотя она и старая (отметка времени транзакции).

• Самое главное, что с моделью PostgreSQL MVCC a новая версия строки записывается для каждого UPDATE, независимо от того, изменились ли данные строки. Это влечет за собой снижение производительности для самого UPSERT, раздувание таблицы, раздувание индекса, снижение производительности для последующих операций с таблицей, VACUUM затраты. Незначительный эффект для нескольких дубликатов, но массивный для большей части дубликатов.

Кроме того, иногда использовать ON CONFLICT DO UPDATE нецелесообразно или даже невозможно. Руководство:

Для ON CONFLICT DO UPDATE необходимо указать conflict_target.

Единственная цель конфликта невозможна, если задействовано несколько индексов / ограничений.

Вы можете добиться (почти) того же без пустых обновлений и побочных эффектов. Некоторые из следующих решений также работают с ON CONFLICT DO NOTHING (без цели конфликта), чтобы отловить все возможные конфликты, которые могут возникнуть - которые могут быть или нежелательны.

Без одновременной нагрузки записи

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id  --, usr, contact              -- return more columns?
   )
SELECT 'i' AS source                           -- 'i' for 'inserted'
     , id  --, usr, contact                    -- return more columns?
FROM   ins
UNION  ALL
SELECT 's' AS source                           -- 's' for 'selected'
     , c.id  --, usr, contact                  -- return more columns?
FROM   input_rows
JOIN   chats c USING (usr, contact);           -- columns of unique index

Столбец source является необязательным дополнением, чтобы продемонстрировать, как это работает. На самом деле он может вам понадобиться, чтобы отличить оба случая (еще одно преимущество перед пустой записью).

Последний JOIN chats работает, потому что недавно вставленные строки из прикрепленного CTE, изменяющего данные еще не отображаются в базовой таблице. (Все части одного и того же оператора SQL видят одни и те же снимки базовых таблиц.)

Поскольку выражение VALUES является автономным (не прикрепленным напрямую к INSERT), Postgres не может наследовать типы данных из целевых столбцов, и вам, возможно, придется добавить явное приведение типов. Руководство:

Когда VALUES используется в INSERT, все значения автоматически приводятся к типу данных соответствующего целевого столбца. Когда он используется в других контекстах, может потребоваться указать правильный тип данных. Если все записи являются литеральными константами в кавычках, принуждение первого достаточно, чтобы определить предполагаемый тип для всех.

Сам запрос (не считая побочных эффектов) может быть немного дороже из-за нескольких дубликатов из-за накладных расходов на CTE и дополнительных SELECT (которые должны быть дешевыми, поскольку идеальный индекс существует с помощью определение - уникальное ограничение реализуется с помощью индекса).

Может быть (намного) быстрее для большого количества дубликатов. Эффективная стоимость дополнительных операций записи зависит от многих факторов.

Но в любом случае есть меньше побочных эффектов и скрытых затрат. Скорее всего, это дешевле в целом.

Присоединенные последовательности по-прежнему являются расширенными, поскольку значения по умолчанию заполняются перед тестированием на конфликты.

О CTE:

• Являются ли запросы типа SELECT единственными типа, который можно вложить?
• Дедупликация операторов SELECT в реляционном подразделении

С одновременной загрузкой записи

Предполагается, что по умолчанию READ COMMITTED изоляция транзакции. Связанный:

• Параллельные транзакции приводят к состоянию гонки с уникальным ограничением на вставку

Лучшая стратегия защиты от условий гонки зависит от точных требований, количества и размера строк в таблице и в UPSERT, количества одновременных транзакций, вероятности конфликтов, доступных ресурсов и других факторов ...

Проблема параллелизма 1

Если параллельная транзакция записала в строку, которую ваша транзакция теперь пытается выполнить UPSERT, ваша транзакция должна дождаться завершения другой.

Если другая транзакция завершается ROLLBACK (или какой-либо ошибкой, то есть автоматическим ROLLBACK), ваша транзакция может продолжаться нормально. Незначительный побочный эффект: пропуски в порядковых номерах. Но никаких недостающих строк.

Если другая транзакция завершается нормально (неявно или явно COMMIT), ваш INSERT обнаружит конфликт (индекс / ограничение UNIQUE является абсолютным) и DO NOTHING, следовательно, также не вернет строку. (Также невозможно заблокировать строку, как показано в проблеме параллелизма 2 ниже, поскольку она не отображается.) SELECT видит тот же снимок с начала запроса и также не может вернуть пока невидимый ряд.

Любые такие строки отсутствуют в результирующем наборе (даже если они существуют в базовой таблице)!

Это может быть нормально. Особенно, если вы не возвращаете строки, как в примере, и довольны тем, что строка есть. Если этого недостаточно, есть разные способы обойти это.

Вы можете проверить количество строк вывода и повторить оператор, если он не соответствует количеству строк ввода. Может быть достаточно для редкого случая. Дело в том, чтобы запустить новый запрос (может быть в той же транзакции), который затем увидит новые зафиксированные строки.

Или проверьте отсутствующие строки результатов в том же запросе и перезапишите строки с помощью трюка грубой силы, продемонстрированного в Ответ Алексстони.

WITH input_rows(usr, contact, name) AS ( ... )  -- see above
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact                   -- we need unique columns for later join
   )
, sel AS (
   SELECT 'i'::"char" AS source                 -- 'i' for 'inserted'
        , id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's'::"char" AS source                 -- 's' for 'selected'
        , c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (                                      -- RARE corner case
   INSERT INTO chats AS c (usr, contact, name)  -- another UPSERT, not just UPDATE
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel   s USING (usr, contact)     -- columns of unique index
   WHERE  s.usr IS NULL                         -- missing!
   ON     CONFLICT (usr, contact) DO UPDATE     -- we've asked nicely the 1st time ...
   SET    name = c.name                         -- ... this time we overwrite with old value
   -- SET name = EXCLUDED.name                  -- alternatively overwrite with *new* value
   RETURNING 'u'::"char" AS source              -- 'u' for updated
           , id  --, usr, contact               -- return more columns?
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;

Это похоже на запрос выше, но мы добавляем еще один шаг с CTE ups, прежде чем вернем полный набор результатов. Последний CTE большую часть времени ничего не делает. Только если в возвращаемом результате пропадают строки, мы используем грубую силу.

Еще больше накладных расходов. Чем больше конфликтов с уже существующими строками, тем больше вероятность, что это превзойдет простой подход.

Один побочный эффект: второй UPSERT записывает строки в неправильном порядке, поэтому он повторно вводит возможность тупиковых ситуаций (см. Ниже), если три или более транзакций, записывающих в одни и те же строки, перекрываются. Если это проблема, вам нужно другое решение - например, повторить все утверждение, как упомянуто выше.

Проблема параллелизма 2

Если параллельные транзакции могут выполнять запись в задействованные столбцы затронутых строк, и вы должны убедиться, что найденные вами строки все еще присутствуют на более позднем этапе той же транзакции, вы можете дешево заблокировать существующие строки в CTE. ins (который иначе был бы разблокирован) с помощью:

...
ON CONFLICT (usr, contact) DO UPDATE
SET name = name WHERE FALSE  -- never executed, but still locks the row
...

И также добавьте в SELECT предложение блокировки , как FOR UPDATE.

Это заставляет конкурирующие операции записи ждать окончания транзакции, когда все блокировки будут сняты. Так что будьте краткими.

Более подробная информация и объяснение:

• Как включить исключенные строки в ВОЗВРАЩЕНИЕ из INSERT ... ПРИ КОНФЛИКТЕ
• SELECT или INSERT в функция подвержена условиям гонки?

Тупики?

Защититесь от взаимоблокировок, вставляя строки в последовательном порядке. Видеть:

• Тупик с многострочными вставками INSERT, несмотря на ON CONFLICT НИЧЕГО НЕ ДЕЛАЕТ

Типы данных и приведения

Существующая таблица как шаблон для типов данных ...

Явное приведение типов для первой строки данных в отдельном выражении VALUES может быть неудобным. Есть способы обойти это. Вы можете использовать любое существующее отношение (таблица, представление, ...) в качестве шаблона строки. Целевая таблица - очевидный выбор для варианта использования. Входные данные автоматически приводятся к соответствующим типам, как в предложении VALUES в INSERT:

WITH input_rows AS (
  (SELECT usr, contact, name FROM chats LIMIT 0)  -- only copies column names and types
   UNION ALL
   VALUES
      ('foo1', 'bar1', 'bob1')  -- no type casts here
    , ('foo2', 'bar2', 'bob2')
   )
   ...

Это не работает для некоторых типов данных. Видеть:

• Приведение типа NULL при обновлении нескольких строк

... и имена

Это также работает для всех типов данных.

При вставке во все (ведущие) столбцы таблицы вы можете не указывать имена столбцов. Предположим, что таблица chats в этом примере состоит только из 3 столбцов, используемых в UPSERT:

WITH input_rows AS (
   SELECT * FROM (
      VALUES
      ((NULL::chats).*)         -- copies whole row definition
      ('foo1', 'bar1', 'bob1')  -- no type casts needed
    , ('foo2', 'bar2', 'bob2')
      ) sub
   OFFSET 1
   )
   ...

Кроме того: не используйте зарезервированные слова, такие как "user", в качестве идентификатора. Это заряженная ножка. Используйте допустимые идентификаторы в нижнем регистре без кавычек. Заменил на usr.

Upsert, являющийся расширением запроса INSERT, может быть определен с двумя разными режимами поведения в случае конфликта ограничений: DO NOTHING или DO UPDATE.

INSERT INTO upsert_table VALUES (2, 6, 'upserted')
   ON CONFLICT DO NOTHING RETURNING *;

 id | sub_id | status
----+--------+--------
 (0 rows)

Также обратите внимание, что RETURNING ничего не возвращает, потому что кортежи не были вставлены. Теперь с DO UPDATE можно выполнять операции с кортежем, с которым возник конфликт. Во-первых, обратите внимание, что важно определить ограничение, которое будет использоваться для определения наличия конфликта.

INSERT INTO upsert_table VALUES (2, 2, 'inserted')
   ON CONFLICT ON CONSTRAINT upsert_table_sub_id_key
   DO UPDATE SET status = 'upserted' RETURNING *;

 id | sub_id |  status
----+--------+----------
  2 |      2 | upserted
(1 row)

WITH e AS(
    INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
)
SELECT * FROM e
UNION
    SELECT id FROM chats WHERE user=$1, contact=$2;

Основная цель использования ON CONFLICT DO NOTHING - избежать выдачи ошибки, но это не приведет к возврату строки. Итак, нам нужен еще SELECT, чтобы получить существующий идентификатор.

В этом SQL, если он терпит неудачу из-за конфликтов, он ничего не вернет, тогда второй SELECT получит существующую строку; если вставка прошла успешно, то будут две одинаковые записи, тогда нам понадобится UNION, чтобы объединить результат.

Для вставки одного элемента я бы, вероятно, использовал объединение при возврате идентификатора:

WITH new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    VALUES ($1, $2, $3)
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT COALESCE(
    (SELECT id FROM new_chats),
    (SELECT id FROM chats WHERE user = $1 AND contact = $2)
);

Для вставки нескольких элементов вы можете поместить значения во временный WITH и ссылаться на них позже:

WITH chats_values("user", "contact", "name") AS (
    VALUES ($1, $2, $3),
           ($4, $5, $6)
), new_chats AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT * FROM chat_values
    ON CONFLICT("user", "contact") DO NOTHING
    RETURNING id
) SELECT id
    FROM new_chats
   UNION
  SELECT chats.id
    FROM chats, chats_values
   WHERE chats.user = chats_values.user
     AND chats.contact = chats_values.contact

Основываясь на ответе Эрвина выше (кстати, потрясающий ответ, без него я бы никогда не попал!), Вот где я оказался. Он решает пару дополнительных потенциальных проблем - он допускает дублирование (что в противном случае может вызвать ошибку), выполняя select distinct на входном наборе, и он гарантирует, что возвращенные идентификаторы точно соответствуют входным set, в том числе в том же порядке и с возможностью дублирования.

Вдобавок и одна часть, которая была важна для меня, это значительно сокращает количество ненужных улучшений последовательности с помощью new_rows CTE, чтобы попытаться вставить только те, которых там еще нет. Учитывая возможность одновременной записи, в этом сокращенном наборе все равно будут возникать конфликты, но последующие шаги позаботятся об этом. В большинстве случаев пробелы в последовательности не имеют большого значения, но когда вы выполняете миллиарды upserts с высоким процентом конфликтов, это может иметь значение между использованием int или bigint в качестве идентификатора.

Несмотря на то, что он большой и уродливый, он работает очень хорошо. Я тщательно протестировал его с миллионами upserts, высокой степенью параллелизма, большим количеством коллизий. Скала.

Я упаковал его как функцию, но если это не то, что вы хотите, должно быть легко увидеть, как перевести на чистый SQL. Я также изменил данные примера на что-то простое.

CREATE TABLE foo
(
  bar varchar PRIMARY KEY,
  id  serial
);
CREATE TYPE ids_type AS (id integer);
CREATE TYPE bars_type AS (bar varchar);

CREATE OR REPLACE FUNCTION upsert_foobars(_vals bars_type[])
  RETURNS SETOF ids_type AS
$$
BEGIN
  RETURN QUERY
    WITH
      all_rows AS (
        SELECT bar, ordinality
        FROM UNNEST(_vals) WITH ORDINALITY
      ),
      dist_rows AS (
        SELECT DISTINCT bar
        FROM all_rows
      ),
      new_rows AS (
        SELECT d.bar
        FROM dist_rows d
             LEFT JOIN foo f USING (bar)
        WHERE f.bar IS NULL
      ),
      ins AS (
        INSERT INTO foo (bar)
          SELECT bar
          FROM new_rows
          ORDER BY bar
          ON CONFLICT DO NOTHING
          RETURNING bar, id
      ),
      sel AS (
        SELECT bar, id
        FROM ins
        UNION ALL
        SELECT f.bar, f.id
        FROM dist_rows
             JOIN foo f USING (bar)
      ),
      ups AS (
        INSERT INTO foo AS f (bar)
          SELECT d.bar
          FROM dist_rows d
               LEFT JOIN sel s USING (bar)
          WHERE s.bar IS NULL
          ORDER BY bar
          ON CONFLICT ON CONSTRAINT foo_pkey DO UPDATE
            SET bar = f.bar
          RETURNING bar, id
      ),
      fin AS (
        SELECT bar, id
        FROM sel
        UNION ALL
        TABLE ups
      )
    SELECT f.id
    FROM all_rows a
         JOIN fin f USING (bar)
    ORDER BY a.ordinality;
END
$$ LANGUAGE plpgsql;

Я изменил удивительный ответ Эрвина Брандштеттера, который не увеличивает последовательность, а также не блокирует запись каких-либо строк. Я относительно новичок в PostgreSQL, поэтому, пожалуйста, дайте мне знать, если вы заметите какие-либо недостатки этого метода:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   SELECT 
     c.usr
     , c.contact
     , c.name
     , r.id IS NOT NULL as row_exists
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   )
INSERT INTO chats (usr, contact, name)
SELECT usr, contact, name
FROM new_rows
WHERE NOT row_exists
RETURNING id, usr, contact, name

Это предполагает, что таблица chats имеет уникальное ограничение на столбцы (usr, contact).

Обновление: добавлены предлагаемые изменения из spatar (ниже). Спасибо!

Еще одно обновление, согласно комментарию Revinand:

WITH input_rows(usr, contact, name) AS (
   VALUES
      (text 'foo1', text 'bar1', text 'bob1')  -- type casts in first row
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, new_rows AS (
   INSERT INTO chats (usr, contact, name)
   SELECT 
     c.usr
     , c.contact
     , c.name
   FROM input_rows AS r
   LEFT JOIN chats AS c ON r.usr=c.usr AND r.contact=c.contact
   WHERE r.id IS NULL
   RETURNING id, usr, contact, name
   )
SELECT id, usr, contact, name, 'new' as row_type
FROM new_rows
UNION ALL
SELECT id, usr, contact, name, 'update' as row_type
FROM input_rows AS ir
INNER JOIN chats AS c ON ir.usr=c.usr AND ir.contact=c.contact

Я не тестировал приведенное выше, но если вы обнаружите, что вновь вставленные строки возвращаются несколько раз, вы можете либо изменить UNION ALL на просто UNION, либо (лучше) просто удалить первый запрос вообще.

Самое простое и эффективное решение -

BEGIN;

INSERT INTO chats ("user", contact, name) 
    VALUES ($1, $2, $3), ($2, $1, NULL) 
ON CONFLICT ("user", contact) DO UPDATE
  SET name = excluded.name
  WHERE false
RETURNING id;

SELECT id
FROM chats
WHERE (user, contact) IN (($1, $2), ($2, $1));

COMMIT;

DO UPDATE WHERE false блокирует, но не обновляет строку, что является функцией, а не ошибкой, поскольку гарантирует, что другая транзакция не сможет удалить строку.

Некоторые комментарии хотят различать обновленные и созданные строки.

В этом случае просто добавьте txid_current() = xmin AS created к выделенному.