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

Hi, Amit

On Tue, Mar 20, 2018 at 8:02 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> 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.
>

Sorry I'm still confusing. You meant that it's possible that an xmin
of a snapshot can be older than the oldest PGXACT.xmin? If it's
possible I'm sure the problem happens but I'm not sure it can happen
because PGXACT.xmin is the oldest xid when taking a snapshot. I think
that the oldest PGXACT.xmin can be either the same as or younger than
the oldest PGXACT.xid.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center