Re: non-zero xmax yet visible

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.

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