Re: vacuum on table1 skips rows because of a query on table2

On Mon, 2019-10-28 at 13:00 -0400, Robert Haas wrote:
> On Sat, Oct 26, 2019 at 1:44 PM Virender Singla <virender(dot)cse(at)gmail(dot)com> wrote:
> > If long-running transaction is "read committed", then we are sure that any new query coming
> > (even on same table1 as vacuum table) will need snapshot on point of time query start and not the time transaction
> > starts (but still why read committed transaction on table2 cause vacuum on table1 to skip rows).
>
> I wish that this argument were completely correct, but it isn't,
> because the current query could involve a function written in some
> procedural language (or in C) which could do anything, including
> accessing tables that the query hasn't previously touched. It could be
> that the function will only be called towards the end of the current
> query's execution, or it could be that it's going to be called
> multiple times and does different things each time.

Even if you call a function that uses a new table in a READ COMMITTED
transaction, that function would use the snapshot of the statement that
called the function and *not* the transaction snapshot, so the function
could see no tuples older than the statement's snapshot.

So VACUUM could remove tuples that were visible when the transaction
started, but are not visible in the current statement's snapshot.

Of course a C function could completely ignore MVCC and access any
old tuple, but do we want to cater for that?

Yours,
Laurenz Albe