Как использовать RETURNING с ON CONFLICT в PostgreSQL?

У меня есть следующий UPSERT в PostgreSQL 9.5:

INSERT INTO chats ("user", "contact", "name") 
           VALUES ($1, $2, $3), 
                  ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO NOTHING
RETURNING id;

Если нет конфликтов, он возвращает что-то вроде этого:

----------
    | id |
----------
  1 | 50 |
----------
  2 | 51 |
----------

Но если есть конфликты, он не возвращает никаких строк:

----------
    | id |
----------

Я хочу вернуть новый id столбцы, если нет конфликтов или вернуть существующие id столбцы конфликтующих столбцов.
Можно ли это сделать? Если так, то как?

Источник

10 ответов

Решение

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

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 с помощью грубой силы (см. Ниже). Простое решение имеет свою привлекательность, побочные эффекты могут быть менее важными.

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

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

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

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

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

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

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

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:

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

Принимая по умолчанию READ COMMITTED изоляция транзакции.

Соответствующий ответ на dba.SE с подробным объяснением:

Наилучшая стратегия защиты от условий гонки зависит от точных требований, количества и размера строк в таблице и в 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

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

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

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

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

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

Тупики?

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

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

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

Явное приведение типов для первой строки данных в автономном 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 needed
    , ('foo2', 'bar2', 'bob2')
   )
   ...

Это не работает для некоторых типов данных (объяснение в связанном ответе внизу). Следующий трюк работает для всех типов данных:

... и имена

Если вы вставляете целые строки (все столбцы таблицы - или, по крайней мере, набор начальных столбцов), вы также можете опустить имена столбцов. Принимая таблицу chats в примере используются только 3 столбца:

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,

Источник 
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)
);
Источник

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)
Источник

Если все, что вы хотите, это добавить одну строку

Затем вы можете значительно упростить ситуацию, используя простой EXISTSПроверьте:

      WITH
  extant AS (
    SELECT id FROM chats WHERE ("user", "contact") = ($1, $2)
  ),
  inserted AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT ($1, $2, $3)
    WHERE NOT EXISTS (SELECT NULL FROM extant)
    RETURNING id
  )
SELECT id FROM inserted
UNION ALL
SELECT id FROM extant

Поскольку нет ON CONFLICTоговорка, обновления нет — только вставка, и только при необходимости. Так что никаких ненужных обновлений, никаких ненужных блокировок записи, никаких ненужных приращений последовательности. Отливки также не требуются.

Если блокировка записи была функцией в вашем случае использования, вы можете использовать SELECT FOR UPDATEв extantвыражение.

И если вам нужно знать, была ли вставлена ​​новая строка, вы можете добавить столбец флага в верхний уровень UNION:

      SELECT id, TRUE AS inserted FROM inserted
UNION ALL
SELECT id, FALSE FROM extant
Источник

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

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

Несмотря на то, что он большой и уродливый, он работает очень хорошо. Я тщательно протестировал его с миллионами 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
        FROM UNNEST(_vals)
      ),
      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
         JOIN fin f USING (bar);
END
$$ LANGUAGE plpgsql;
Источник

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

      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 к выбору.

Источник

Я изменил удивительный ответ Эрвина Брандштеттера, который не увеличивает последовательность, а также не блокирует запись каких-либо строк. Я относительно новичок в 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 (ниже). Спасибо!

Источник

Обновите поле до его текущего значения.

      INSERT INTO chats c ("user", "contact", "name") 
       VALUES ($1, $2, $3), 
              ($2, $1, NULL) 
ON CONFLICT("user", "contact") DO UPDATE
SET user = c.user
RETURNING id
Источник
Другие вопросы по тегам