From: Tom Lane <tgl@sss.pgh.pa.us>
To: Cherio <cherio@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17810: Update from 13.09 to 13.10 breaks SQLs with VACUUM
In-reply-to: 
 <CAKHqFkJGNLbbgY_=_4Gk9JioCOSh5iY++cGOfdB3_r+YNUW1wA@mail.gmail.com>
References: <17810-8eb7b4e1b7a68e1a@postgresql.org>
 <371962.1677515656@sss.pgh.pa.us>
 <CAKHqFkJsF8TOm0UV+-4=M98hc_D5qqqxFu3AY-mOyKrPuMMswQ@mail.gmail.com>
 <CAKHqFkJfO3-TA4YL_O0yw8EaKTGY9+2HFAiRgHpEu8q1abvesg@mail.gmail.com>
 <391334.1677520801@sss.pgh.pa.us>
 <CAKHqFkL3Ms6NzZ9_9UbpN3Qr0UKHdHisRH=JkeGYirYRhg0R7g@mail.gmail.com>
 <392516.1677521462@sss.pgh.pa.us>
 <CAKHqFkJGNLbbgY_=_4Gk9JioCOSh5iY++cGOfdB3_r+YNUW1wA@mail.gmail.com>
Comments: In-reply-to Cherio <cherio@gmail.com>
	message dated "Mon, 27 Feb 2023 13:41:56 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <409155.1677527025.1@sss.pgh.pa.us>
Date: Mon, 27 Feb 2023 14:43:45 -0500
Message-ID: <409156.1677527025@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/409156.1677527025%40sss.pgh.pa.us>
Precedence: bulk

Cherio <cherio@gmail.com> writes:
> You are right. My mental model of the change is a speculation, but this is
> only because the specifics of the change are not clarified anywhere.

The problem is that statements that aren't allowed to execute in a
transaction block (usually because they need an immediate commit)
weren't checking that properly in the context of pipelined queries.
This should never have been allowed, but we weren't enforcing that
properly.  The net effect in some cases is that such commands
unexpectedly committed the effects of prior commands in the pipeline,
and in other cases that a rollback occurring later in the pipeline
would leave you with corrupted on-disk state.  We judged that to be
a bad enough bug that the fix should be backpatched.

The original fix for that went in six months ago (13.8 et al) but
we later discovered that there was still a hole in it.  The fact
that the 13.10 release notes only mention ANALYZE is a result of
my inadequate summarization of the commit log entry :-(.

> BTW, why ANALYZE?

ANALYZE is a slightly different case.  It can work inside a transaction
block or not, but it has two different strategies depending on that: one
uses internal commits and the other doesn't.  It was applying the wrong
one in this context, which again had the effect of prematurely committing
previous commands in the pipeline.

> Are VACUUM and ANALYZE the only commands that must be executed
> separately?

There's a couple dozen such commands --- easiest way to find them
is to grep the source code for PreventInTransactionBlock calls.
I believe "can't run inside a transaction block" is mentioned in
all their manual pages, but we don't have a central list.

			regards, tom lane