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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general