From: Tom Lane <tgl@sss.pgh.pa.us>
To: Julien Rouhaud <rjuju123@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Keyerror Smart <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: <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>
Comments: In-reply-to Julien Rouhaud <rjuju123@gmail.com>
	message dated "Wed, 01 Mar 2023 15:15:12 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1390593.1677684173.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 01 Mar 2023 10:22:53 -0500
Message-ID: <1390594.1677684173@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1390594.1677684173%40sss.pgh.pa.us>
Precedence: bulk

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 who=
le
    new snapshot now happens only between original queries.  This is equiv=
alent
    to the existing behavior of EXPLAIN ANALYZE, and it was judged to be t=
he
    best choice since it eliminates one source of concurrency hazards for
    rules.  The patch should also make things marginally faster by reducin=
g 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