Re: non-zero xmax yet visible

On Wed, Jan 15, 2014 at 10:48 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Wed, Jan 15, 2014 at 1:26 AM, Ming Li <mli89257@gmail.com> wrote:
>> I'm a little bit confused by the meaning of xmax.
>>
>> The documentation at
>> http://www.postgresql.org/docs/current/static/ddl-system-columns.html
>> says
>> "xmax
>>
>> The identity (transaction ID) of the deleting transaction, or zero for
>> an undeleted row version. It is possible for this column to be nonzero
>> in a visible row version. That usually indicates that the deleting
>> transaction hasn't committed yet, or that an attempted deletion was
>> rolled back."
>>
>> According to this, it seems a committed change should result in an
>> xmax value of zero. But a huge number of rows in our database have
>> non-zero xmax values and are still visible.
> Not exactly, this is only the case of a tuple that has been only
> inserted in a transaction. To put it in simple words an inserted row
> will have its xmin set to the current transaction ID with xman set at
> 0, and a deleted row will have its xmax updated to the transaction ID
> of the transaction that removed it.
> An updated row is the combination of a deletion and an insertion.

Well, an update statement generates a new row version. The deletion
xmax is set on the old row version and not on the new one?

If only session 1 is doing an update concurrently, xmax of the final
row version is set to 0 as expected.

    => update test_data set value = 3 where id = 1;
    => commit;
    => select xmin, xmax, id, value from test_data;
       xmin    | xmax | id | value
    -----------+------+----+-------
     363072457 |    0 |  1 |     3

If session 1 and 2 perform overlapping update transactions as in my
previous example, xmax of the final committed row version is not zero.
This is what I find confusing. Why is the new row version of an update
transaction associated with a non-zero xmax? The new row version
itself is neither deleted nor updated.

>
> The data visible from other sessions depends as well on the isolation level:
> http://www.postgresql.org/docs/current/static/transaction-iso.html
> The default, read committed, means that the query will see data
> committed by other sessions before the *query* began.
>
>> I did the following experiment with 2 sessions.
>>
>> Session 1
>>
>>     => create table test_data (id int, value int);
>>     => insert into test_data(id) values(1);
>>     => commit;
>>     => update test_data set value = 1 where id = 1;
>>     => select txid_current();
>>      txid_current
>>     --------------
>>         362938838
>>
>> Session 2
>>
>>     => select xmin, xmax, id, value from test_data;
>>        xmin    |   xmax    | id | value
>>     -----------+-----------+----+-------
>>      362938803 | 362938838 |  1 |
> This session is using a transaction ID between 362938803 and
> 362938838, explaining why it is the one visible. You are also not
> giving all the information of session 2, a transaction began there as
> well.
>
>>     => update test_data set value = 2 where id = 1;
>>
>> Session 1
>>
>>     => commit;
>>
>> Session 2
>>
>>     => select txid_current();
>>      txid_current
>>     --------------
>>         362938861
>>
>>     => commit;
>>     => select xmin, xmax, id, value from test_data;
>>        xmin    |   xmax    | id | value
>>     -----------+-----------+----+-------
>>      362938861 | 362938861 |  1 |     2
> In this case what this session
>
>> So in this case, xmax is equal to xmin. I've also seen cases where
>> xmax is larger than xmin and the row is visible.
> With the isolation level read committed, changes committed by other
> sessions during a transaction are visible.
>
>> Is this an expected behavior? How shall we interpret xmax in these cases?
> This is part of how MVCC works in Postgres, xman is the transaction ID
> until when this tuple is visible for other sessions.
> Regards,
> --
> Michael