Magnus Hagander <magnus@hagander.net> writes: > 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.
I don't think the OP is claiming that autovacuum is blocked, only that it's failing to remove recently-dead rows that he thinks could be removed.
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.
There are some approximations involved in figuring out which rows are potentially still visible to someone. So perhaps this is a situation where an approximation is being used and tighter analysis would have shown that indeed a row could be removed. But we haven't seen any evidence of that so far. The basic fact that A's snapshot is limiting removal of rows from a table it has not touched is not a bug.
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. So adding COMMIT between each select shouldn't change that part, should it? That is, it's the snapshot age that decides it, not the transaction age.
I feel there is still some piece of information missing there, that could explain the problem better...
