Re: Autovacuum of independent tables

On Tue, Sep 8, 2020 at 5:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Tue, Sep 8, 2020 at 4:38 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> The reason that's not so is that whether or not transaction A *has*
> >> touched table B is irrelevant. It *could* read table B at any moment,
> >> for all autovacuum knows. Therefore we cannot remove rows that should
> >> still be visible to A's snapshot.
>
> > Right. But in the default isolation level, the snapshot of A gets reset
> > between each SELECT, and does not persist to the end of the transaction.
>
> Well, we don't know what isolation level the OP is using. We also don't
>

Per the thread, he's using the default, which should be read committed.

> know what PG version he's using. From memory, it hasn't been that long
>

Per his session list, 11.2.

> since we fixed things so that an idle read-committed transaction
> advertises no xmin. It's also possible that the transaction isn't really
> idle between statements (eg, if it's holding open cursors, or the like).
>

Oh, now *cursors* is definitely something I didn't think of. And especially
in the context of ODBC, I wonder if it might be creating cursors
transparently, and that this somehow causes the problems.

Michael, do you know if that might be the case? Or try enabling
log_statements to check if it is?

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>