Hi, Long transactions often annoy users because if a long transaction exists on a database vacuum cannot reclaim efficiently. There are several reason why they exist on a database but it's a common case where users or applications forget to commit/rollback transactions. That is, transaction is not executing SQL and its state is 'idle in transaction' on pg_stat_activity. In this case, such transactions don't affect vacuum's job either if they aren't assigned transaction id or if they don't have a snapshot. However if they have xid it will affect vacuum's job even if they don't have a snapshot.
I think that to decide which deleted tuples must be preserved we don't need to care about backend PGXACT.xid but must care about PGXACT.xmin. But current GetOldestXmin considers both of them. I guess one reason why GetOldestXmin does so is that it's also used to determine where to truncate pg_subtrans. Is there anything else reason? If nothing, I'd like to change GetOldestXmin so that it sees only PGXACT.xmin for vacuum purposes. Once we addressed this issue it'll helpful especially for user who uses read committed transaction isolation level. Regards, -- Masahiko Sawada NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
