Обсуждение: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

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.

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

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

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? 

"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

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