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?
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 |