Re: XID-assigned idle transactions affect vacuum's job.

On Tue, Mar 20, 2018 at 12:22 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> 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?
>

I think the main reason is that while computing snapshots, we also
rely on PGXACT.xid. Basically, it can be present in some other
snapshots xmin. Now, if you ignore it in vacuum (GetOldestXmin), then
it is quite possible that the xid we have ignored will be part of some
other snapshot's xmin which I think in turn can lead to wrong results.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com