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

У меня есть эта таблица (сгенерированная 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.

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

Ответы

Ответ 1

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

Команда 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).)

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

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

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

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

по теме: