Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set

Julien Rouhaud <rjuju123(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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