Thread

1. vacuum on table1 skips rows because of a query on table2

   Virender Singla <virender.cse@gmail.com> — 2019-10-25T06:03:11Z

   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.
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2019-10-25T16:46:57Z

   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
   
   
   
   

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

   Virender Singla <virender.cse@gmail.com> — 2019-10-26T04:10:02Z

   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).
   Hence if a vacuum on table1 sees that all the transactions in the database
   are "read committed" and no one
   accessing table1, vacuum should be able to clear dead rows.
   For read committed transactions, different table should not interfere with
   each other.
   
   On Fri, Oct 25, 2019 at 10:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
   
   > 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
   >
   

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

   Robert Haas <robertmhaas@gmail.com> — 2019-10-28T17:00:47Z

   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
   
   
   
   

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

   Laurenz Albe <laurenz.albe@cybertec.at> — 2019-10-28T21:41:57Z

   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
   
   
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2019-10-28T21:54:56Z

   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
   
   
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2019-10-28T22:00:21Z

   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