Horton

Thread

  1. BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    PG Bug reporting form <noreply@postgresql.org> — 2025-08-29T11:39:42Z

    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.

  2. Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-29T15:13:42Z

    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

  3. Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    ZhangChi <798604270@qq.com> — 2025-08-30T01:51:46Z

    Hi tom&nbsp;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?&nbsp;


         Original
         
       
From: Tom Lane <tgl@sss.pgh.pa.us&gt;
Date: 2025年8月29日 23:13
To: 798604270 <798604270@qq.com&gt;
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
Subject: Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result



       PG&nbsp;Bug&nbsp;reporting&nbsp;form&nbsp;<noreply@postgresql.org&gt;&nbsp;writes:
&gt;&nbsp;In&nbsp;the&nbsp;following&nbsp;test&nbsp;case,&nbsp;the&nbsp;`EXECUTE`&nbsp;statement&nbsp;will&nbsp;fail&nbsp;with&nbsp;an&nbsp;error
&gt;&nbsp;`result&nbsp;of&nbsp;range&nbsp;union&nbsp;would&nbsp;not&nbsp;be&nbsp;contiguous`.&nbsp;The&nbsp;final&nbsp;SELECT&nbsp;query
&gt;&nbsp;should&nbsp;return&nbsp;1&nbsp;row&nbsp;as&nbsp;there&nbsp;is&nbsp;only&nbsp;one&nbsp;value&nbsp;`1`&nbsp;in&nbsp;&nbsp;t1.c0&nbsp;and&nbsp;only&nbsp;one
&gt;&nbsp;value&nbsp;`1`&nbsp;in&nbsp;t3.c0,&nbsp;however,&nbsp;the&nbsp;query&nbsp;returns&nbsp;0&nbsp;rows.

I&nbsp;don't&nbsp;see&nbsp;a&nbsp;bug&nbsp;here&nbsp;particularly.&nbsp;&nbsp;If&nbsp;you&nbsp;check&nbsp;the&nbsp;contents&nbsp;of
table&nbsp;t3&nbsp;at&nbsp;the&nbsp;end&nbsp;of&nbsp;the&nbsp;test,&nbsp;you&nbsp;will&nbsp;see

&nbsp;c0&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;c1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
----+------------------------
&nbsp;&nbsp;2&nbsp;|&nbsp;[-761818403,793731612)
(1&nbsp;row)

in&nbsp;the&nbsp;"wrong"&nbsp;case.&nbsp;&nbsp;This&nbsp;shows&nbsp;that&nbsp;the&nbsp;prepared&nbsp;insert&nbsp;advanced
the&nbsp;sequence&nbsp;underlying&nbsp;"c0&nbsp;bigserial"&nbsp;before&nbsp;failing,&nbsp;whereas
in&nbsp;the&nbsp;default&nbsp;plan_cache_mode&nbsp;the&nbsp;error&nbsp;occurred&nbsp;before&nbsp;touching
the&nbsp;sequence.&nbsp;&nbsp;This&nbsp;discrepancy&nbsp;isn't&nbsp;a&nbsp;bug.&nbsp;&nbsp;It&nbsp;occurs&nbsp;because
in&nbsp;the&nbsp;default&nbsp;mode&nbsp;the&nbsp;planner&nbsp;will&nbsp;attempt&nbsp;to&nbsp;constant-fold
that&nbsp;messy&nbsp;range&nbsp;expression&nbsp;during&nbsp;planning,&nbsp;and&nbsp;thus&nbsp;it&nbsp;will
hit&nbsp;the&nbsp;range&nbsp;union&nbsp;failure&nbsp;before&nbsp;anything&nbsp;is&nbsp;done&nbsp;to&nbsp;the
sequence.&nbsp;&nbsp;In&nbsp;the&nbsp;generic&nbsp;case&nbsp;the&nbsp;failure&nbsp;occurs&nbsp;in&nbsp;the&nbsp;executor,
and&nbsp;the&nbsp;sequence&nbsp;has&nbsp;already&nbsp;been&nbsp;advanced.

			regards, tom&nbsp;lane

  4. Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    David G. Johnston <david.g.johnston@gmail.com> — 2025-08-30T01:57:05Z

    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.

  5. Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-30T02:06:32Z

    "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

  6. Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

    ZhangChi <798604270@qq.com> — 2025-08-30T02:09:36Z

    Hi,


I got it, thank you for your detailed explanation!
         Original
         
       
From: Tom Lane <tgl@sss.pgh.pa.us&gt;
Date: 2025年8月30日 10:06
To: David G. Johnston <david.g.johnston@gmail.com&gt;
Cc: ZhangChi <798604270@qq.com&gt;, pgsql-bugs <pgsql-bugs@lists.postgresql.org&gt;
Subject: Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result



       "David&nbsp;G.&nbsp;Johnston"&nbsp;<david.g.johnston@gmail.com&gt;&nbsp;writes:
&gt;&nbsp;On&nbsp;Friday,&nbsp;August&nbsp;29,&nbsp;2025,&nbsp;ZhangChi&nbsp;<798604270@qq.com&gt;&nbsp;wrote:
&gt;&gt;&nbsp;I&nbsp;still&nbsp;have&nbsp;a&nbsp;problem&nbsp;with&nbsp;this.&nbsp;When&nbsp;an&nbsp;INSERT&nbsp;fails,&nbsp;why&nbsp;not&nbsp;undo&nbsp;all
&gt;&gt;&nbsp;the&nbsp;effects&nbsp;of&nbsp;the&nbsp;INSERT?

&gt;&nbsp;Performance.

Yeah.&nbsp;&nbsp;You&nbsp;can&nbsp;certainly&nbsp;argue&nbsp;that&nbsp;it&nbsp;was&nbsp;a&nbsp;design&nbsp;error&nbsp;to&nbsp;make
nextval()&nbsp;nontransactional,&nbsp;but&nbsp;the&nbsp;performance&nbsp;advantages&nbsp;are
compelling.&nbsp;&nbsp;Most&nbsp;critically,&nbsp;if&nbsp;we&nbsp;required&nbsp;that,&nbsp;then&nbsp;any
transaction&nbsp;doing&nbsp;nextval()&nbsp;would&nbsp;block&nbsp;all&nbsp;other&nbsp;transactions&nbsp;from
doing&nbsp;nextval()&nbsp;on&nbsp;the&nbsp;same&nbsp;sequence:&nbsp;they'd&nbsp;have&nbsp;to&nbsp;wait&nbsp;to&nbsp;see&nbsp;if
the&nbsp;first&nbsp;one&nbsp;committed&nbsp;before&nbsp;they&nbsp;could&nbsp;know&nbsp;what&nbsp;value&nbsp;to&nbsp;use.
(Deadlocks&nbsp;between&nbsp;nextval's&nbsp;on&nbsp;different&nbsp;sequences&nbsp;could&nbsp;be&nbsp;a&nbsp;problem
as&nbsp;well.)&nbsp;&nbsp;So&nbsp;the&nbsp;odds&nbsp;that&nbsp;we'd&nbsp;change&nbsp;that&nbsp;are&nbsp;nil,&nbsp;even&nbsp;if&nbsp;there
weren't&nbsp;a&nbsp;few&nbsp;decades&nbsp;worth&nbsp;of&nbsp;backwards&nbsp;compatibility&nbsp;to&nbsp;worry&nbsp;about.

			regards,&nbsp;tom&nbsp;lane