Re: [PATCH] VACUUM: avoid pre-creation transactions holding back cleanup of newly created relations

Hi,

On 2025-12-09 14:31:09 -0500, Melanie Plageman wrote:
> On Tue, Dec 9, 2025 at 2:25 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> >
> > Isn't the whole idea that it would be safe to allow freezing in this case
> > incorrect? Consider the following scenario:
> >
> > A1: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT;
> > B1: CREATE TABLE foo AS SELECT random();
> > B2: VACUUM FREEZE foo;
> > A2: SELECT * FROM foo;
> >
> > If you allowed freezing of the rows in B2, A2 will see the rows as visible,
> > despite them not being supposed to be visible.
>
> Is the reason this isn't a problem for COPY FREEZE because the
> freezing happens in the same transaction block as creating the table
> so A2 wouldn't be able to see the catalog entry for the table?

It is a problem for COPY FREEZE - you shouldn't use it unless you're ok with
that. Our docs say:

"Note that all other sessions will immediately be able to see the data once it
has been successfully loaded. This violates the normal rules of MVCC
visibility and users should be aware of the potential problems this might
cause."

Would probably be good to call that out more aggressively.

I don't think that means it's ok for CREATE TABLE to do the same
implicitly. With COPY FREEZE you explicitly opt-in to this behaviour - and
even there it'd be rather nice if we could fix this behaviour.

It might be worth to invent a mechanism that causes errors on table access
under certain visibility conditions. COPY FREEZE isn't the only concerning
command, e.g. rewriting ALTER TABLEs are also problematic (whereas
CLUSTER/VACUUM FULL is very careful to not trigger issues).

Greetings,

Andres Freund