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

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