From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Keyerror Smart <smartkeyerror@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
Date: Wed, 01 Mar 2023 10:22:53 -0500
Message-ID: <1390594.1677684173@sss.pgh.pa.us> (raw)
In-Reply-To: <20230301071512.b3ratpciflkrzkeb@jrouhaud>
References: <17812-206eaec1d133a94a@postgresql.org>
<20230228135252.agaj42dik5qfjjxi@jrouhaud>
<CAD=-kXaPVQtO3ivEY-Wvz4BkFCntnshPfcV2DCG7ujqwCNF3pA@mail.gmail.com>
<20230301025229.rxmmjxfvg45w2cj4@jrouhaud>
<CAD=-kXb1UUGqrHjVs47m6sS=tD0VQz3y=1y6d7nfTWYckNDrsw@mail.gmail.com>
<CAKFQuwb5U84yZGRRiGsRtLm3D3_jtc=TtXE=7y-z0ORg+Jy1bQ@mail.gmail.com>
<1311639.1677643997@sss.pgh.pa.us>
<CAKFQuwZTYxHmGvFyWBp3BQXmi7RBwRq8fkgLn+=sK+GOTdye_Q@mail.gmail.com>
<1328697.1677653602@sss.pgh.pa.us>
<20230301071512.b3ratpciflkrzkeb@jrouhaud>
Julien Rouhaud <rjuju123@gmail.com> writes:
> On Wed, Mar 01, 2023 at 01:53:22AM -0500, Tom Lane wrote:
>> Maybe it isn't. The code flow for CREATE TABLE AS is a bit weird
>> IIRC, and maybe it's missing a step where we should update the
>> active snapshot.
> I think it comes from this chunk in ExecCreateTableAs():
Hm. There are a lot of places that do this:
> PushCopiedSnapshot(GetActiveSnapshot());
> UpdateActiveSnapshotCommandId();
rather than
PushActiveSnapshot(GetTransactionSnapshot());
which is what would have the effect of noticing changes from other
sessions. Digging into the history of that, I found this commit:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_1_BR [c0b007603] 2011-02-28 23:28:06 -0500
Rearrange snapshot handling to make rule expansion more consistent.
With this patch, portals, SQL functions, and SPI all agree that there
should be only a CommandCounterIncrement between the queries that are
generated from a single SQL command by rule expansion. Fetching a whole
new snapshot now happens only between original queries. This is equivalent
to the existing behavior of EXPLAIN ANALYZE, and it was judged to be the
best choice since it eliminates one source of concurrency hazards for
rules. The patch should also make things marginally faster by reducing the
number of snapshot push/pop operations.
So I guess it's intended behavior that we don't notice other-session
changes between steps of a single command. Whether that rule should
apply to CREATE TABLE AS is perhaps debatable --- but I see several
other places that are doing it exactly like this, so it's not like
CREATE TABLE AS is alone in its folly.
I'm pretty hesitant to change this without substantial thought.
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, rjuju123@gmail.com, david.g.johnston@gmail.com, smartkeyerror@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
In-Reply-To: <1390594.1677684173@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