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
Date: Mon, 27 Feb 2023 14:43:45 -0500
Message-ID: <409156.1677527025@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, cherio@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17810: Update from 13.09 to 13.10 breaks SQLs with VACUUM
In-Reply-To: <409156.1677527025@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox