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

On Tue, Mar 20, 2018 at 11:41 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> Hi, Amit
>
> On Tue, Mar 20, 2018 at 8:02 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>> On Tue, Mar 20, 2018 at 12:22 PM, Masahiko Sawada <sawada.mshk@gmail.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.
>

I sent the previous mail before checking the mail by Tom, and now I
understood that the necessity of including PGXACT.xid. Sorry for the
stupid question.

Regards,

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