Re: Autovacuum of independent tables - Mailing list pgsql-general
| From | Magnus Hagander |
|---|---|
| Subject | Re: Autovacuum of independent tables |
| Date | |
| Msg-id | CABUevEy89njjF9HSCiJyzfa7R=7D_X0q5HXM+WOvObeTKrv14w@mail.gmail.com Whole thread Raw |
| In response to | Re: Autovacuum of independent tables (Michael Holzman <michaelholzman@gmail.com>) |
| Responses |
Re: Autovacuum of independent tables
|
| List | pgsql-general |
On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman <michaelholzman@gmail.com> wrote:
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.
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. So clearly there is something else going on -- something else must be non-default, or it's something that the driver layer does.
To show that, something as simple as the following, with autovacuum logging enabled:
session 1:
CREATE TABLE test AS SELECT * FROM generate_series(1,10000);
session 2:
begin;
SELECT count(*) FROM test;
\watch 1
session 1:
delete from test;
In this case, you will see autovacuum firing just fine, even though there is an open transaction that queries the table test. As you're running you can use a third session to see that session 2 flips between "active" and "idle in transaction". The log output in my case was:
2020-09-08 16:13:12.271 CEST [26753] LOG: automatic vacuum of table "postgres.public.test": index scans: 0
pages: 0 removed, 45 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 56 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 241585
buffer usage: 112 hits, 4 misses, 5 dirtied
avg read rate: 0.006 MB/s, avg write rate: 0.008 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 5.01 s
pages: 0 removed, 45 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 56 removed, 0 remain, 0 are dead but not yet removable, oldest xmin: 241585
buffer usage: 112 hits, 4 misses, 5 dirtied
avg read rate: 0.006 MB/s, avg write rate: 0.008 MB/s
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 5.01 s
It is failing to *truncate* the table, but the general autovacuum is running.
Are you by any chance specifically referring to the truncation step?
However, if you change the session 2 to select from a *different* table, the truncation also works, so I'm guessing that's not it?
//Magnus
pgsql-general by date: