Семантика INSERT SELECT для обновления по конфликту ничего не возвращает
Мы столкнулись с очень специфической проблемой нашей производственной системы. К сожалению, несмотря на большие усилия, я не смог воспроизвести проблему локально, поэтому я не могу предоставить минимальный, полный и поддающийся проверке пример. Кроме того, поскольку это производственный код, мне пришлось изменить имена таблиц в следующем примере. Однако я полагаю, что я представляю все соответствующие факты.
У нас есть четыре таблицы bucket_holder, bucket, item и bucket_total созданные следующим образом:
CREATE TABLE bucket_holder (
id SERIAL PRIMARY KEY,
bucket_holder_uid UUID NOT NULL
);
CREATE TABLE bucket (
id SERIAL PRIMARY KEY,
bucket_uid UUID NOT NULL,
bucket_holder_id INTEGER NOT NULL REFERENCES bucket_holder (id),
default_bucket BOOLEAN NOT NULL
);
CREATE TABLE item (
id SERIAL PRIMARY KEY,
item_uid UUID NOT NULL,
bucket_id INTEGER NOT NULL REFERENCES bucket (id),
amount NUMERIC NOT NULL
);
CREATE TABLE bucket_total (
bucket_id INTEGER NOT NULL REFERENCES bucket (id),
amount NUMERIC NOT NULL
);
Также есть соответствующие индексы для соответствующих столбцов:
CREATE UNIQUE INDEX idx1 ON bucket_holder (bucket_holder_uid);
CREATE UNIQUE INDEX idx2 ON bucket (bucket_uid);
CREATE UNIQUE INDEX idx3 ON item (item_uid);
CREATE UNIQUE INDEX idx4 ON bucket_total (bucket_id);
Идея заключается в том, что bucket_holder держит bucket с, один из которых является default_bucket, bucket держать item и каждое bucket имеет уникальное bucket_total записи, содержащую сумму количеств все item s.
Мы пытаемся сделать массовые вставки в таблицу item следующим образом:
WITH
unnested AS (
SELECT *
FROM UNNEST(
ARRAY['00000000-0000-0000-0000-00000000001a', '00000000-0000-0000-0000-00000000002a']::UUID[],
ARRAY['00000000-0000-0000-0000-00000000001c', '00000000-0000-0000-0000-00000000002c']::UUID[],
ARRAY[1.11, 2.22]::NUMERIC[]
)
AS T(bucket_holder_uid, item_uid, amount)
),
inserted_item AS (
INSERT INTO item (bucket_id, item_uid, amount)
SELECT bucket.id, unnested.item_uid, unnested.amount
FROM unnested
JOIN bucket_holder ON unnested.bucket_holder_uid = bucket_holder.bucket_holder_uid
JOIN bucket ON bucket.bucket_holder_id = bucket_holder.id
JOIN bucket_total ON bucket_total.bucket_id = bucket.id
WHERE bucket.default_bucket
FOR UPDATE OF bucket_total
ON CONFLICT DO NOTHING
RETURNING bucket_id, amount
),
total_for_bucket AS (
SELECT bucket_id, SUM(amount) AS total
FROM inserted_item
GROUP BY bucket_id
)
UPDATE bucket_total
SET amount = amount + total_for_bucket.total
FROM total_for_bucket
WHERE bucket_total.bucket_id = total_for_bucket.bucket_id
В действительности передаваемые массивы являются динамическими и имеют длину до 1000, но все 3 массива имеют одинаковую длину. Массивы всегда сортируются так, чтобы bucket_holder_uids были в порядке, чтобы гарантировать, что тупик не может возникнуть. item_uid ON CONFLICT DO NOTHING заключается в том, что мы должны быть в состоянии справиться с ситуацией, когда некоторые item уже присутствовали (конфликт на item_uid). В этом случае bucket_total конечно, не должен обновляться.
Этот запрос предполагает, что соответствующие записи bucket_holder, bucket и bucket_total уже существуют. В противном случае запрос может завершиться ошибкой, так как на практике такая ситуация не возникает. Вот пример настройки некоторых образцов данных:
INSERT INTO bucket_holder (bucket_holder_uid) VALUES ('00000000-0000-0000-0000-00000000001a');
INSERT INTO bucket (bucket_uid, bucket_holder_id, default_bucket) VALUES ('00000000-0000-0000-0000-00000000001b', (SELECT id FROM bucket_holder WHERE bucket_holder_uid = '00000000-0000-0000-0000-00000000001a'), TRUE);
INSERT INTO bucket_total (bucket_id, amount) VALUES ((SELECT id FROM bucket WHERE bucket_uid = '00000000-0000-0000-0000-00000000001b'), 0);
INSERT INTO bucket_holder (bucket_holder_uid) VALUES ('00000000-0000-0000-0000-00000000002a');
INSERT INTO bucket (bucket_uid, bucket_holder_id, default_bucket) VALUES ('00000000-0000-0000-0000-00000000002b', (SELECT id FROM bucket_holder WHERE bucket_holder_uid = '00000000-0000-0000-0000-00000000002a'), TRUE);
INSERT INTO bucket_total (bucket_id, amount) VALUES ((SELECT id FROM bucket WHERE bucket_uid = '00000000-0000-0000-0000-00000000002b'), 0);
Похоже, что этот запрос сделал правильные вещи для сотен тысяч item, но для нескольких элементов item bucket_total был обновлен в два раза больше, чем item. Я не знаю, обновлялось ли оно дважды или обновлялось ли оно вдвое больше, чем item. Однако в этих случаях был вставлен только один item (вставка дважды была бы невозможна в любом случае, поскольку существует ограничение уникальности для item_uid). Наши журналы показывают, что для затронутых bucket два потока выполняли запрос одновременно.
Может кто-нибудь увидеть и объяснить любую проблему с этим запросом и указать, как он может быть переписан?
Мы используем версию PG9.6.6
ОБНОВИТЬ
Мы говорили об этом с основным разработчиком Postgres, который, очевидно, не видит здесь проблемы с параллелизмом. Сейчас мы исследуем действительно неприятные возможности, такие как повреждение индекса или (удаленный) шанс ошибки pg.
Ответы
Ответ 1
Некоторые мысли, ожидая больше данных
Исходя из вашей проблемы, звучит так, будто либо вставленный элемент CTE возвращает дубликаты, либо оператор update как-то выполнен дважды. Оба звучат странно, наверное, ошибка в pg? Может быть, попытаться максимально упростить запрос
некоторые идеи: Похоже, вы сначала кладете предметы в какое-то ведро по умолчанию. В этом случае не имеет смысла иметь соединение с таблицей сегментов (соединение от 1 до многих). Почему бы просто не иметь идентификатор корзины по умолчанию в таблице держателей (или иметь для этого отдельный cte)
эта строка, похоже, ничего не делает: JOIN bucket_total ON bucket_total.bucket_id = bucket.id
Вероятно, было бы достаточно просто вставить данные в таблицу позиций. Почему бы не использовать bucket_total в качестве представления (например, выбрать bucket_id, sum (amount)... from items...) Если для заполнения требуется некоторое время, возможно, используйте его в качестве материализованного представления или таблицы отчетов. Или, если вы запускаете этот скрипт много раз в течение дня, возможно, создайте триггер в таблице элементов, чтобы добавить/вычесть 1 в корзину при вставке/удалении
при условии, что вы можете уменьшить ваш запрос до чего-то вроде этого:
WITH
unnested AS (....),
INSERT INTO item (bucket_id, item_uid, amount)
SELECT bucket_holder2.dflt_bucket_id, unnested.item_uid, unnested.amount
FROM unnested
JOIN bucket_holder2 ON unnested.bucket_holder_uid = bucket_holder2.bucket_holder_uid
ON CONFLICT DO NOTHING
Обновление Попытка выполнить эти запросы на 9.6, и он работал нормально. Так что я думаю, что нет никаких проблем с запросом и pg, возможно, пришло время воссоздать таблицу/базу данных. Еще одна идея для тестирования - вы можете попробовать изменить "UPDATE" на "INSERT" для обновления bucket_total, удалив текущий уникальный ключ и создав инкрементный первичный ключ. Таким образом, вы можете поймать/отремонтировать двойные вставки (если это так)
