Thread: Autovacuum of independent tables

Hi,

I have two applications A and B. A runs SELECT statements only and only on tableA. B actively updates tableB, A never looks into tableB. B has nothing to do with tableA.

Still, if A is inside a long running transaction, autovacuum does not handle tableB. Why is it so?

--

Regards,
    Michael Holzman

autovacuum does cleaning of changes related to finished transactions. It does nothing if possible dead tuples are assigned to open transactions.

This is called MVCC, which applies to a session as a whole.  The point
here is that even if your application knows that only tableA is used
by a given transaction, Postgres cannot know that, as it could be
possible that data from tableB is needed in this same transaction, so
old versions of the rows from tableB matching with the snapshot hold
by this long-running transaction still have to be around.

On Tue, Sep 8, 2020 at 11:28 AM Michael Paquier wrote:

This is called MVCC, which applies to a session as a whole.  The point
here is that even if your application knows that only tableA is used
by a given transaction, Postgres cannot know that, as it could be
possible that data from tableB is needed in this same transaction, so
old versions of the rows from tableB matching with the snapshot hold
by this long-running transaction still have to be around.

 Yes, I thought so. I just hoped there may be a workaround decoupling the tables.

Thanks.

--

Regards,
    Michael Holzman

You can try to reduce length of transactions, if possible.

 

Interesting. Are you telling the Oracle version of the code had no intermittent COMMIT and relied on one
final COMMIT at the end. Even in Oracle developers must have planned for commit since a long running
open transaction can lead to “snapshot too old” error.

On Tue, Sep 8, 2020 at 3:03 PM Magnus Hagander wrote:

A PostgreSQL SELECT does *not* open a transaction past the end of the statement, if it's run independently on a connection.

This sounds like you are using a client on PostgreSQL that uses an "autocommit off" mode, since that's the only case where you'd need to add COMMITs (or ROLLBACKs) to close a transaction after a SELECT. 

Yes, this is correct. We do not use autocommit. Everything is controlled explicitly. We run quite complex multi-statement multi-table transactions and cannot work with "autocommit on".

Therefore, this is what we have

> psql

psql (11.2)
Type "help" for help.

pg-11.2 rw => COMMIT;
WARNING:  25P01: there is no transaction in progress
LOCATION:  EndTransactionBlock, xact.c:3675
COMMIT
Time: 0.745 ms
pg-11.2 rw => select 2*2;
 ?column?
----------
        4
(1 row)

Time: 0.347 ms
pg-11.2 rw => COMMIT;
COMMIT
Time: 0.525 ms

The first COMMIT (immediately after connect) fails as there is no transaction.

The second one works as even this SELECT opened one. We have a transaction (and a snapshot) when no table is touched!

Whether you have autocommit on or off, you can *always* control things explicitly. And you can certainly run "multi-statement transactions" in autocommit on -- in fact, it's what most people do since it's the default configuration of the system (and I don't see why multi-table would even be relevant).

Autocommit on/off only controls what happens when you *don't* control things explicitly.

On Tue, Sep 8, 2020 at 4:25 PM Magnus Hagander wrote:

Whether you have autocommit on or off, you can *always* control things explicitly. And you can certainly run "multi-statement transactions" in autocommit on -- in fact, it's what most people do since it's the default configuration of the system (and I don't see why multi-table would even be relevant).

Autocommit on/off only controls what happens when you *don't* control things explicitly.

I know that we can control things explicitly with "autocommit on". But we would need to add "BEGIN" statements to the code which is an even bigger change than adding COMMITs. We considered it and found that the development cost is too high.

It seems I was not clear enough. I do not complain. I have been a PG fan since 2000 when I worked with it for the first time. I just wanted to understand it deeper and, fortunately, find a work around that would simplify our current development.

Magnus Hagander <magnus@hagander.net> writes:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.

I don't think the OP is claiming that autovacuum is blocked, only that
it's failing to remove recently-dead rows that he thinks could be removed.

The reason that's not so is that whether or not transaction A *has*
touched table B is irrelevant.  It *could* read table B at any moment,
for all autovacuum knows.  Therefore we cannot remove rows that should
still be visible to A's snapshot.

There are some approximations involved in figuring out which rows are
potentially still visible to someone.  So perhaps this is a situation
where an approximation is being used and tighter analysis would have
shown that indeed a row could be removed.  But we haven't seen any
evidence of that so far.  The basic fact that A's snapshot is limiting
removal of rows from a table it has not touched is not a bug.

Magnus Haganderwrites:
> Oh sure, but there is clearly *something* going on, so we should try to
> figure that out. Because a transaction running multiple independent selects
> with the defaults settings will not actually block autovacuum.

I don't think the OP is claiming that autovacuum is blocked, only that
it's failing to remove recently-dead rows that he thinks could be removed.

The reason that's not so is that whether or not transaction A *has*
touched table B is irrelevant.  It *could* read table B at any moment,
for all autovacuum knows.  Therefore we cannot remove rows that should
still be visible to A's snapshot.

There are some approximations involved in figuring out which rows are
potentially still visible to someone.  So perhaps this is a situation
where an approximation is being used and tighter analysis would have
shown that indeed a row could be removed.  But we haven't seen any
evidence of that so far.  The basic fact that A's snapshot is limiting
removal of rows from a table it has not touched is not a bug.

I feel there is still some piece of information missing there, that could explain the problem better...

Magnus Hagander <magnus@hagander.net> writes:
> On Tue, Sep 8, 2020 at 4:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> The reason that's not so is that whether or not transaction A *has*
>> touched table B is irrelevant.  It *could* read table B at any moment,
>> for all autovacuum knows.  Therefore we cannot remove rows that should
>> still be visible to A's snapshot.

> Right. But in the default isolation level, the snapshot of A gets reset
> between each SELECT, and does not persist to the end of the transaction.

Well, we don't know what isolation level the OP is using.  We also don't

know what PG version he's using.  From memory, it hasn't been that long

since we fixed things so that an idle read-committed transaction
advertises no xmin.  It's also possible that the transaction isn't really
idle between statements (eg, if it's holding open cursors, or the like).

Well, we don't know what isolation level the OP is using. 

We also don't know what PG version he's using. 

From memory, it hasn't been that long
since we fixed things so that an idle read-committed transaction
advertises no xmin.  It's also possible that the transaction isn't really
idle between statements (eg, if it's holding open cursors, or the like).

Per his session list, 11.2.

Oh, now *cursors* is definitely something I didn't think of. And especially in the context of ODBC, I wonder if it might be creating cursors transparently, and that this somehow causes the problems.

Michael, do you know if that might be the case? Or try enabling log_statements to check if it is?