non-zero xmax yet visible

From: Ming Li <mli89257(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: non-zero xmax yet visible
Date: 2014-01-14 16:26:57
Message-ID: CAPcxA_2wLRkK35qbrFVJn6Ln0HR58qKQJcztA2ZmmczKsK6xbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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 |

=> 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

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.

Is this an expected behavior? How shall we interpret xmax in these cases?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Zev Benjamin 2014-01-14 18:07:24 HeadlineWordEntry bit fields
Previous Message Torsten Förtsch 2014-01-14 15:57:32 Re: question about checksum in 9.3