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

Hi,

I got it, thank you for your detailed explanation!
Original


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us&gt;
Date: 2025年8月30日 10:06
To: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com&gt;
Cc: ZhangChi <798604270(at)qq(dot)com&gt;, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org&gt;
Subject: Re: BUG #19036: Failed prepared INSERT statement make another SELECT query generate wrong result

"David&nbsp;G.&nbsp;Johnston"&nbsp;<david(dot)g(dot)johnston(at)gmail(dot)com&gt;&nbsp;writes:
&gt;&nbsp;On&nbsp;Friday,&nbsp;August&nbsp;29,&nbsp;2025,&nbsp;ZhangChi&nbsp;<798604270(at)qq(dot)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