Bug Report: INSERT ON CONFLICT sometimes does not work with partial index

Поиск
Список
Период
Сортировка
От Danylo Miroshnichenko
Тема Bug Report: INSERT ON CONFLICT sometimes does not work with partial index
Дата
Msg-id CAHn_XWZ4c8VbHYcakZJRwuxt1iQmzcoxZ51s6sm43=ZR0Et6Ng@mail.gmail.com
обсуждение исходный текст
Ответы Re: Bug Report: INSERT ON CONFLICT sometimes does not work with partial index  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-bugs
Script:

CREATE TABLE IF NOT EXISTS test
(    type character varying,    id   integer
);

CREATE UNIQUE INDEX IF NOT EXISTS uniq_id_test     ON test USING btree (type, id)     WHERE (type = 'Test');

PREPARE test (text, int, text) AS    INSERT INTO test (type, id)    VALUES ($1, $2)    ON CONFLICT (type, id)    WHERE type = $3 DO    UPDATE    SET id = EXCLUDED.id;


EXECUTE test('Test', 1, 'Test');
EXECUTE test('Test', 2, 'Test');
EXECUTE test('Test', 3, 'Test');
EXECUTE test('Test', 4, 'Test');
EXECUTE test('Test', 5, 'Test');
EXECUTE test('Test', 6, 'Test');

The last EXECUTE statement always throws an error:
[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

If we DEALLOCATE the prepared statement and then re-create it, we will have another 5 successful attempts and on the 6th attempt we again will get the error above.

All of it works fine if we replace $3 parameter with a constant value 'Test' (no errors):

PREPARE test (text, int, text) AS    INSERT INTO test (type, id)    VALUES ($1, $2)    ON CONFLICT (type, id)    WHERE type = 'Test' DO    UPDATE    SET id = EXCLUDED.id;

I read the doc about PREPARE statement and know that the planner might build a generic plan for the prepared statement and use it,  but isn't it considered a bug if the planner's work leads to a broken query?

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17768: Assert triggered on initsplan.c
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #17769: Assert triggered in indxpath.c