On Tue, Sep 8, 2020 at 4:01 PM Michael Holzman <michaelholz...@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 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
