pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
6+ messages / 4 participants
[nested] [flat]
* BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
@ 2025-08-29 11:39 PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2025-08-29 11:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
The following bug has been logged on the website:
Bug reference: 19036
Logged by: Chi Zhang
Email address: 798604270@qq.com
PostgreSQL version: 17.5
Operating system: ubuntu 24.04 with docker
Description:
Hi,
In the following test case, the `EXECUTE` statement will fail with an error
`result of range union would not be contiguous`. The final SELECT query
should return 1 row as there is only one value `1` in t1.c0 and only one
value `1` in t3.c0, however, the query returns 0 rows.
```
SET plan_cache_mode = force_generic_plan;
CREATE TABLE t1(c0 REAL);
CREATE TABLE t3(c0 bigserial, c1 int4range);
PREPARE prepare_query (int4range, int4range, int4range, int4range,
int4range, int4range, int4range, int4range, int4range, int4range, int4range,
int4range, int4range, int4range, int4range, int4range, int4range) AS INSERT
INTO t3(c1) VALUES(((range_merge((((($3)-($4)))*($5)),
(((($6)+($7)))-((($8)-($9))))))-((((((($10)-($11)))-((($12)*($13)))))*((((($14)*($15)))+((($16)-($17)))))))));
EXECUTE prepare_query('(-556931823,1276777813)'::int4range,
'(-571202662,1382275249]'::int4range, '[507243772,1979226805)'::int4range,
'(-886673458,-886673458]'::int4range, '[-602190450,-571202662]'::int4range,
'(-1179115181,-994816467]'::int4range,
'[-1324483627,-617195673]'::int4range, '(-1920488796,362367315]'::int4range,
'(-1920488796,-974159112]'::int4range, '(-1511616986,309266836)'::int4range,
'[264292163,2029512724]'::int4range, '(-1235934435,-274850186]'::int4range,
'[510315686,1121320469)'::int4range, '(52072425,1543185664)'::int4range,
'[13120838,1960723456]'::int4range, '[-1240013782,-395016816]'::int4range,
'(1151766089,2106918647)'::int4range);
DEALLOCATE prepare_query;
INSERT INTO t1(c0) VALUES(CAST(CAST(1 AS INT) AS INT));
INSERT INTO t3(c1) VALUES('[-761818403,793731611]'::int4range);
SELECT t1.c0 FROM t3, t1 WHERE (CAST(t1.c0 AS INT)) IN (t3.c0);
```
If you remove the `SET plan_cache_mode = force_generic_plan;` option or the
prepared statement, the query will return correct result.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
@ 2025-08-29 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 01:51 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-08-29 15:13 UTC (permalink / raw)
To: 798604270@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> In the following test case, the `EXECUTE` statement will fail with an error
> `result of range union would not be contiguous`. The final SELECT query
> should return 1 row as there is only one value `1` in t1.c0 and only one
> value `1` in t3.c0, however, the query returns 0 rows.
I don't see a bug here particularly. If you check the contents of
table t3 at the end of the test, you will see
c0 | c1
----+------------------------
2 | [-761818403,793731612)
(1 row)
in the "wrong" case. This shows that the prepared insert advanced
the sequence underlying "c0 bigserial" before failing, whereas
in the default plan_cache_mode the error occurred before touching
the sequence. This discrepancy isn't a bug. It occurs because
in the default mode the planner will attempt to constant-fold
that messy range expression during planning, and thus it will
hit the range union failure before anything is done to the
sequence. In the generic case the failure occurs in the executor,
and the sequence has already been advanced.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-08-30 01:51 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-08-30 01:57 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: ZhangChi @ 2025-08-30 01:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi tom lane,
Thanks for your reply.
I still have a problem with this. When an INSERT fails, why not undo all the effects of the INSERT?
Original
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2025年8月29日 23:13
To: 798604270 <798604270@qq.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
PG Bug reporting form <noreply@postgresql.org> writes:
> In the following test case, the `EXECUTE` statement will fail with an error
> `result of range union would not be contiguous`. The final SELECT query
> should return 1 row as there is only one value `1` in t1.c0 and only one
> value `1` in t3.c0, however, the query returns 0 rows.
I don't see a bug here particularly. If you check the contents of
table t3 at the end of the test, you will see
c0 | c1
----+------------------------
2 | [-761818403,793731612)
(1 row)
in the "wrong" case. This shows that the prepared insert advanced
the sequence underlying "c0 bigserial" before failing, whereas
in the default plan_cache_mode the error occurred before touching
the sequence. This discrepancy isn't a bug. It occurs because
in the default mode the planner will attempt to constant-fold
that messy range expression during planning, and thus it will
hit the range union failure before anything is done to the
sequence. In the generic case the failure occurs in the executor,
and the sequence has already been advanced.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 01:51 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
@ 2025-08-30 01:57 ` David G. Johnston <david.g.johnston@gmail.com>
2025-08-30 02:06 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2025-08-30 01:57 UTC (permalink / raw)
To: ZhangChi <798604270@qq.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Friday, August 29, 2025, ZhangChi <798604270@qq.com> wrote:
>
>
> I still have a problem with this. When an INSERT fails, why not undo all
> the effects of the INSERT?
>
Performance.
Sequences don’t go backward.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 01:51 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-08-30 01:57 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result David G. Johnston <david.g.johnston@gmail.com>
@ 2025-08-30 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 02:09 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2025-08-30 02:06 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: ZhangChi <798604270@qq.com>; pgsql-bugs <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, August 29, 2025, ZhangChi <798604270@qq.com> wrote:
>> I still have a problem with this. When an INSERT fails, why not undo all
>> the effects of the INSERT?
> Performance.
Yeah. You can certainly argue that it was a design error to make
nextval() nontransactional, but the performance advantages are
compelling. Most critically, if we required that, then any
transaction doing nextval() would block all other transactions from
doing nextval() on the same sequence: they'd have to wait to see if
the first one committed before they could know what value to use.
(Deadlocks between nextval's on different sequences could be a problem
as well.) So the odds that we'd change that are nil, even if there
weren't a few decades worth of backwards compatibility to worry about.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 01:51 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-08-30 01:57 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result David G. Johnston <david.g.johnston@gmail.com>
2025-08-30 02:06 ` Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-08-30 02:09 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
0 siblings, 0 replies; 6+ messages in thread
From: ZhangChi @ 2025-08-30 02:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Hi,
I got it, thank you for your detailed explanation!
Original
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2025年8月30日 10:06
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: ZhangChi <798604270@qq.com>, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, August 29, 2025, ZhangChi <798604270@qq.com> wrote:
>> I still have a problem with this. When an INSERT fails, why not undo all
>> the effects of the INSERT?
> Performance.
Yeah. You can certainly argue that it was a design error to make
nextval() nontransactional, but the performance advantages are
compelling. Most critically, if we required that, then any
transaction doing nextval() would block all other transactions from
doing nextval() on the same sequence: they'd have to wait to see if
the first one committed before they could know what value to use.
(Deadlocks between nextval's on different sequences could be a problem
as well.) So the odds that we'd change that are nil, even if there
weren't a few decades worth of backwards compatibility to worry about.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-08-30 02:09 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-29 11:39 BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result PG Bug reporting form <noreply@postgresql.org>
2025-08-29 15:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 01:51 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.com>
2025-08-30 01:57 ` David G. Johnston <david.g.johnston@gmail.com>
2025-08-30 02:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-08-30 02:09 ` =?utf-8?B?WmhhbmdDaGk=?= <798604270@qq.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