From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Keyerror Smart <smartkeyerror@gmail.com>,
        Julien Rouhaud <rjuju123@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: 
 <CAKFQuwZTYxHmGvFyWBp3BQXmi7RBwRq8fkgLn+=sK+GOTdye_Q@mail.gmail.com>
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>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Tue, 28 Feb 2023 21:55:40 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1328696.1677653602.1@sss.pgh.pa.us>
Date: Wed, 01 Mar 2023 01:53:22 -0500
Message-ID: <1328697.1677653602@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1328697.1677653602%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Session 4:
> create table tbl2 as select * from view1;
> SELECT 0

> Why is it OK for session 4 to be different here?

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.

> It is unclear to me whether you were instead talking about other sessions
> dropping tables as another way of saying "ACCESS EXCLUSIVE" in which case
> at what lock level should this anomaly go away, and does it?

The originally-proposed tests seemed to all involve either TRUNCATE
or DROP TABLE, which are outside what I consider to be our MVCC
guarantees.  Your example here does seem a bit strange though.
Views generally ought not have different semantics from writing
out the view query in-line.

			regards, tom lane