Re: Autovacuum of independent tables

On 9/8/20 3:27 AM, Michael Paquier wrote:
> On Tue, Sep 08, 2020 at 11:16:04AM +0300, Michael Holzman wrote:
>> Autovacuum does not clean dead tuples of closed transactions in tableB
>> while there is an open transaction on tableA.
>> But the tables have nothing in common. They are handled by separate
>> applications and there are no transactions that touch both tables
>> simultaneously.
>> Why does autovacuum create an artificial dependency on the tables?
> This is called MVCC, which applies to a session as a whole. The point
> here is that even if your application knows that only tableA is used
> by a given transaction, Postgres cannot know that, as it could be
> possible that data from tableB is needed in this same transaction, so
> old versions of the rows from tableB matching with the snapshot hold
> by this long-running transaction still have to be around.

Too bad the START TRANSACTION statement doesn't have a RESERVING clause
where you can enumerate the tables you'll be using.

--
Angular momentum makes the world go 'round.