Re: Autovacuum of independent tables

On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman <michaelholzman(at)gmail(dot)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

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