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?
