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
|
| Список | 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 lastEXECUTEstatement 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 по дате отправления: