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