Re: Autovacuum of independent tables
| От | Magnus Hagander |
|---|---|
| Тема | Re: Autovacuum of independent tables |
| Дата | |
| Msg-id | CABUevEy89njjF9HSCiJyzfa7R=7D_X0q5HXM+WOvObeTKrv14w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Autovacuum of independent tables (Michael Holzman <michaelholzman@gmail.com>) |
| Ответы |
Re: Autovacuum of independent tables
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | 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 по дате отправления: