Re: Autovacuum of independent tables - Mailing list pgsql-general
| From | Magnus Hagander |
|---|---|
| Subject | Re: Autovacuum of independent tables |
| Date | |
| Msg-id | CABUevExt_G98fka__-iba7rC3otQwGoO_vKHxJD1rVxNnS9sRg@mail.gmail.com Whole thread Raw |
| In response to | Autovacuum of independent tables (Michael Holzman <michaelholzman@gmail.com>) |
| Responses |
Re: Autovacuum of independent tables
|
| List | pgsql-general |
(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@gmail.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> psqlpsql (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 msThe 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:
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
pgsql-general by date: