pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17532: Prepared statements and insert on conflict can fail on the 6th execution
2+ messages / 2 participants
[nested] [flat]
* BUG #17532: Prepared statements and insert on conflict can fail on the 6th execution
@ 2022-06-25 15:05 PG Bug reporting form <noreply@postgresql.org>
2022-06-25 15:56 ` Re: BUG #17532: Prepared statements and insert on conflict can fail on the 6th execution David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2022-06-25 15:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jack@jackchristensen.com
The following bug has been logged on the website:
Bug reference: 17532
Logged by: Jack Christensen
Email address: jack@jackchristensen.com
PostgreSQL version: 14.4
Operating system: MacOS
Description:
A user of the Go driver pgx reported a strange bug
(https://github.com/jackc/pgx/issues/1234). After investigating the issue I
was able to duplicate it in directly in psql. The problem occurs when a
prepared statement has an INSERT ... ON CONFLICT. The statement works the
first 5 times and fails on the 6th time with ERROR: there is no unique or
exclusion constraint matching the ON CONFLICT specification. I would guess
this has to do with the planner choosing a generic or custom plan.
Here is the reproduction case:
CREATE TYPE promo_reason AS ENUM ('promo_reason');
CREATE TYPE promo_type AS ENUM ('promo_type');
CREATE TABLE promos
(
promo_id UUID NOT NULL PRIMARY KEY,
user_id UUID NOT NULL,
reason promo_reason NOT NULL,
type promo_type NOT NULL
);
CREATE UNIQUE INDEX promotions_user_id_first_transfer_key ON promos
(user_id)
WHERE type = 'promo_type' AND reason = 'promo_reason';
PREPARE s AS INSERT INTO promos
(promo_id, user_id, reason, type)
VALUES
($1, $2, $3, $4)
ON CONFLICT (user_id) WHERE type = $4 AND reason = $3
DO NOTHING;
EXECUTE s ('00ebd890-f5ac-47c7-9365-4ce9875c04a1',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('8e3775d9-af90-472f-9720-d0341ff7bba7',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('f983db27-62d9-4ef1-bc67-e1e492eee48e',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('10532081-851c-4dc4-9d83-8750bd4cf78d',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('98e049e4-8762-4c95-be6c-31f4d8f9b04e',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
EXECUTE s ('355972ac-20a4-4c17-b28d-70d8cb2dd5b8',
'132b64e6-1dc9-46cb-9349-5a0d7469622b', 'promo_reason', 'promo_type');
And here is the output I get when running it:
CREATE TYPE
CREATE TYPE
CREATE TABLE
CREATE INDEX
PREPARE
INSERT 0 1
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
psql:prepared_statement_vs_on_conflict_bug.sql:27: ERROR: there is no
unique or exclusion constraint matching the ON CONFLICT specification
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17532: Prepared statements and insert on conflict can fail on the 6th execution
2022-06-25 15:05 BUG #17532: Prepared statements and insert on conflict can fail on the 6th execution PG Bug reporting form <noreply@postgresql.org>
@ 2022-06-25 15:56 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2022-06-25 15:56 UTC (permalink / raw)
To: Jack Christensen <jack@jackchristensen.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sat, Jun 25, 2022, 08:25 PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17532
> Logged by: Jack Christensen
> Email address: jack@jackchristensen.com
> PostgreSQL version: 14.4
> Operating system: MacOS
> Description:
>
> A user of the Go driver pgx reported a strange bug
> (https://github.com/jackc/pgx/issues/1234). After investigating the issue
> I
> was able to duplicate it in directly in psql. The problem occurs when a
> prepared statement has an INSERT ... ON CONFLICT. The statement works the
> first 5 times and fails on the 6th time with ERROR: there is no unique or
> exclusion constraint matching the ON CONFLICT specification. I would guess
> this has to do with the planner choosing a generic or custom plan.
>
See existing bug 17445 for discussion on this.
https://www.postgresql.org/message-id/17445-fb74db6348391e85%40postgresql.org
In short, you have written a query that probably should always fails and
just fails to do so in some instances.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-06-25 15:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-25 15:05 BUG #17532: Prepared statements and insert on conflict can fail on the 6th execution PG Bug reporting form <noreply@postgresql.org>
2022-06-25 15:56 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox