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

From: Danylo Miroshnichenko <miroshnik(dot)dan(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Bug Report: INSERT ON CONFLICT sometimes does not work with partial index
Date: 2023-02-04 17:34:19
Message-ID: CAHn_XWZ4c8VbHYcakZJRwuxt1iQmzcoxZ51s6sm43=ZR0Et6Ng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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?

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-02-04 20:28:20 Re: BUG #17769: Assert triggered in indxpath.c
Previous Message Tom Lane 2023-02-03 22:31:21 Re: BUG #17768: Assert triggered on initsplan.c