Re: Autovacuum of independent tables

(Please don't drop the mailinglist from CC, as others are likely interested
in the responses)

On Tue, Sep 8, 2020 at 3:06 PM Michael Holzman <michaelholzman(at)gmail(dot)com>
wrote:

>
>
> 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".
>

That is not what autocommit means.

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.

> 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!
>

So just to be clear, here is how PostgreSQL behaves by default:

postgres=# commit;
WARNING: there is no transaction in progress
COMMIT
postgres=# select 2*2;
?column?
----------
4
(1 row)

postgres=# commit;
WARNING: there is no transaction in progress
COMMIT

But yes, if you explicitly ask that a query shall keep a transaction open
across multiple statements, by turning off autocommit, it will.

In fact, *PostgreSQL* will always behave that way. The *psql client* will
behave differently depending on how you configure it, and the same will of
course apply to any other client that you have. In the example above, psql.

You cannot both have a transaction existing and not existing at the same
time. You do have to separate the idea of transactions from snapshots
though, as they can differ quite a bit depending on isolation levels.

> And how much a running transaction blocks autovacuum is also dependent on
> what isolation level you're running it in. In the default isolation level,
> a snapshot is taken for each individual select, so does not block vacuuming
> past the end of the individual select. Higher isolation levels will.
>
> > We use default isolation mode and we proved that SELECTs block
autovacuum. As soon as we added COMMITs after SELECTS in several places
(not all as we still have not fixed all the code), autovacuum started
working properly in the fixed flows.

As I said yes, a running SELECT will, because of the snapshot. An open
transaction will not, past the individual select, because a new snapshot is
taken for each SELECT.

If you have an open transaction that runs regular selects but as separate
queries then it will not block autovacuum, unless it also does something
else.

Of course if it's a big query that runs the whole time it will, but then
there would also not be a way to "add commits" into the middle of it, so
clearly that's not what's going on here.

//Magnus