Обсуждение: vacuum on table1 skips rows because of a query on table2

Virender Singla <virender.cse@gmail.com> writes:
> Currently I see the vacuum behavior for a table is that, even if a long
> running query on a different table is executing in another read committed
> transaction.
> That vacuum in the 1st transaction skips the dead rows until the long
> running query finishes.
> Why that is the case, On same table long running query blocking vacuum we
> can understand but why query on a different table block it.

Probably because vacuum's is-this-row-dead-to-everyone tests are based
on the global xmin minimum.  This must be so, because even if the
long-running transaction hasn't touched the table being vacuumed,
we don't know that it won't do so in future.  So we can't remove
rows that it should be able to see if it were to look.

            regards, tom lane

On Sat, Oct 26, 2019 at 1:44 PM Virender Singla <virender.cse@gmail.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.

Now, this is pretty unlikely and most queries don't behave anything
like that.  They do things like "+" or "coalesce" which don't open new
tables. There are contrary examples, though, even among functions
built into core, like "table_to_xmlschema", which takes a relation OID
as an argument and thus may open a new relation each time it's called.
If we had some way of analyzing a query and determining whether it
uses any functions or operators that open new tables, then this kind
of optimization might be possible, but we don't.

However, even if we did have such infrastructure, it wouldn't solve
all of our problems, because vacuum would have to know which sessions
were running queries that might open new tables and which were running
queries that won't open new tables -- and among the latter, it would
need to know which tables those sessions already have open. We could
make the former available via a new shared memory flag and the latter
could, perhaps, be deduced from the lock table, which is already
shared. However, if we did all that, VACUUM would potentially have to
do significantly more work to deduce the xmin horizon for each table
that it wanted to process.

Even given all that, I'm moderately confident that something like this
would benefit a lot of people. However, it would probably hurt some
people too, either because the overhead of figuring out that the
current query won't lock any more relations, so that we can advertise
that fact in shared memory, or because of the increased overhead of
figuring out the xmin horizon for a table to be vacuumed. Users with
short-running queries and small tables would be the most likely to be
harmed. On the other hand, for users with giant tables, even more
aggressive approaches might pay off - e.g. recompute the xmin horizon
every 1GB or so, because it might have advanced, and the effort to
recheck that might pay off by allowing us to vacuum more stuff sooner.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Mon, 2019-10-28 at 13:00 -0400, Robert Haas wrote:
> On Sat, Oct 26, 2019 at 1:44 PM Virender Singla <virender.cse@gmail.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

Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Oct 26, 2019 at 1:44 PM Virender Singla <virender.cse@gmail.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,
> [ for lots of reasons ]

On top of the problems Robert enumerated, there's another fairly serious
one, which is that "global xmin" is not just the minimal XID that's
running.  Rather, it's the minimum XID that was running when any active
snapshot was taken.  Thus, even if you could prove that some long-running
transaction isn't going to touch the table you wish to vacuum, that
fact in itself won't move your estimate of the relevant xmin very much:
that transaction's own XID is holding back the xmins of every other
transaction --- and not only the ones open now, but ones that will
start in future, which you certainly can't predict anything about.

Thus, to decide whether tuples newer than the long-running transaction's
XID are safe to remove, you'd have to figure out what the other
transactions' snapshots would look like if that transaction weren't there
... and you don't have that information.  The model we use of exposing
only "xmin", and not any more-detailed info about the contents of other
transactions' snapshots, really isn't adequate to allow this sort of
analysis.  You could imagine exposing more info, but that carries more
costs --- costs that would be paid whether or not VACUUM ever gets any
benefit from it.

> Even given all that, I'm moderately confident that something like this
> would benefit a lot of people. However, it would probably hurt some
> people too, either because the overhead of figuring out that the
> current query won't lock any more relations, so that we can advertise
> that fact in shared memory, or because of the increased overhead of
> figuring out the xmin horizon for a table to be vacuumed.

Yeah, the whole thing is a delicate tradeoff between the cost of
tracking/advertising transaction state and the value of being able
to remove tuples sooner.  Maybe we can move that tradeoff, but it'd
require a whole lot of pretty fundamental rework.

> On the other hand, for users with giant tables, even more
> aggressive approaches might pay off - e.g. recompute the xmin horizon
> every 1GB or so, because it might have advanced, and the effort to
> recheck that might pay off by allowing us to vacuum more stuff sooner.

Hmm, that's an interesting idea.  It wouldn't take a lot of work
to try it.

            regards, tom lane

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Mon, 2019-10-28 at 13:00 -0400, Robert Haas wrote:
>> 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.

I don't think that's particularly relevant here.  Our sessions already
advertise the xmin from their oldest live snapshot, which would be
the statement snapshot in this case.  What the OP is wishing for is
analysis that's finer-grained than "global xmin" allows for ---
but per Robert's comments and my own nearby comments, you would need
a *whole* lot more information to do noticeably better.

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

That's already not guaranteed to work, since a tuple older than the
xmin your session is advertising could disappear at any moment.

            regards, tom lane