Как включить исключенные строки в RETURNING из INSERT ... ON CONFLICT

Question

У меня есть эта таблица (сгенерированная Django):

CREATE TABLE feeds_person (
  id serial PRIMARY KEY,
  created timestamp with time zone NOT NULL,
  modified timestamp with time zone NOT NULL,
  name character varying(4000) NOT NULL,
  url character varying(1000) NOT NULL,
  email character varying(254) NOT NULL,
  CONSTRAINT feeds_person_name_ad8c7469_uniq UNIQUE (name, url, email)
);

Я пытаюсь массово вставить много данных, используя INSERT с ON CONFLICT пункт.

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

В других случаях я бы сделал что-то вроде:

INSERT INTO feeds_person (created, modified, name, url, email)
VALUES blah blah blah
ON CONFLICT (name, url, email) DO UPDATE SET url = feeds_person.url
RETURNING id

Делать UPDATE заставляет оператор возвращать id из этого ряда. За исключением того, что это не работает с этой таблицей. Я думаю, что это не работает, потому что у меня есть несколько уникальных полей вместе, тогда как в других случаях я использовал этот метод, у меня было только одно уникальное поле.

Я получаю эту ошибку при попытке запустить SQL через курсор Django:

django.db.utils.ProgrammingError: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Как мне сделать массовую вставку с этой таблицей и вернуть вставленные и существующие идентификаторы?

Answer 1

Вы получаете ошибку:

Команда ON CONFLICT DO UPDATE не может повлиять на строку во второй раз

означает, что вы пытаетесь сохранить одну и ту же строку более одного раза в одной команде. Другими словами: у вас есть обманщики на (name, url, email) в вашем VALUES список. Сложите дубликаты (если это вариант), и это должно работать. Но вам придется решить, какую строку выбрать из каждого набора дупов.

INSERT INTO feeds_person (created, modified, name, url, email)
SELECT DISTINCT ON (name, url, email) *
FROM  (
   VALUES
   ('blah', 'blah', 'blah', 'blah', 'blah')
   -- ... more
   ) v(created, modified, name, url, email)  -- match column list
ON     CONFLICT (name, url, email) DO UPDATE
SET    url = feeds_person.url
RETURNING id;

Так как мы используем отдельно стоящие VALUES Выражение теперь, вы должны добавить явное приведение типов для типов не по умолчанию. Подобно:

VALUES
    (timestamptz '2016-03-12 02:47:56+01'
   , timestamptz '2016-03-12 02:47:56+01'
   , 'n3', 'u3', 'e3')
   ...

Ваш timestamptz столбцы нуждаются в явном приведении типов, тогда как строковые типы могут работать по умолчанию text, (Вы все еще можете бросить на varchar(n) сразу.)

Есть способы определить, какую строку выбрать из каждого набора дубликатов:

• Выберите первую строку в каждой группе GROUP BY?

Вы правы, в настоящее время нет способа получить исключенные строки в RETURNING пункт. Я цитирую Postgres Wiki:

Обратите внимание, что RETURNING не делает видимымEXCLUDED.*"псевдоним из UPDATE (просто общий "TARGET.*"псевдоним там виден). Предполагается, что это создает досадную неоднозначность для простых, распространенных случаев [30], приносящих мало пользы. В какой-то момент в будущем мы можем использовать способ разоблачения, если RETURNING-проектированные кортежи были вставлены и обновлены, но это, вероятно, не должно превращать это в первую совершенную итерацию функции [31].

Однако вы не должны обновлять строки, которые не должны обновляться. Пустые обновления стоят почти так же дорого, как и обычные обновления, и могут иметь непредвиденные побочные эффекты. Для начала вам не нужен UPSERT, ваш случай больше похож на "SELECT or INSERT". Связанные с:

• SELECT или INSERT в функции склонны к условиям гонки?

Одним из более чистых способов вставки набора строк было бы использование CTE, изменяющих данные:

WITH val AS (
   SELECT DISTINCT ON (name, url, email) *
   FROM  (
      VALUES 
      (timestamptz '2016-1-1 0:0+1', timestamptz '2016-1-1 0:0+1', 'n', 'u', 'e')
    , ('2016-03-12 02:47:56+01', '2016-03-12 02:47:56+01', 'n1', 'u3', 'e3')
      -- more (type cast only needed in 1st row)
      ) v(created, modified, name, url, email)
   )
, ins AS (
   INSERT INTO feeds_person (created, modified, name, url, email)
   SELECT created, modified, name, url, email FROM val
   ON     CONFLICT (name, url, email) DO NOTHING
   RETURNING id, name, url, email
   )
SELECT 'inserted' AS how, id FROM ins  -- inserted
UNION  ALL
SELECT 'selected' AS how, f.id         -- not inserted
FROM   val v
JOIN   feeds_person f USING (name, url, email);

Дополнительная сложность должна платить за большие столы, где INSERT это правило и SELECT исключение.

Первоначально я добавил NOT EXISTS предикат на последнем SELECT для предотвращения дубликатов в результате. Но это было излишним. Все CTE одного запроса видят одинаковые снимки таблиц. Набор вернулся с ON CONFLICT (name, url, email) DO NOTHING является взаимоисключающим для набора, возвращенного после INNER JOIN на тех же столбцах.

К сожалению, это также открывает крошечное окно для состояния гонки. Если...

• параллельная транзакция вставляет конфликтующие строки
• еще не совершил
• но в конечном итоге совершает

... некоторые строки могут быть потеряны.

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

Связанные с:

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