Horton

Thread

  1. 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