On Wed, Jan 15, 2014 at 10:48 AM, Michael Paquier <michael.paquier@xxxxxxxxx> wrote: > On Wed, Jan 15, 2014 at 1:26 AM, Ming Li <mli89257@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
