Magnus Haganderwrites: > 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.
Yes, this is exactly what happens.
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.
It's obviously not a bug. I was just surprised when I figured that out. It's also quite complex to explain to my colleagues. Actually, this is the main reason I started this thread: I tried to explain to someone and felt that I miss something.