Re: have trouble understanding xmin and xmax with update operations from two different sessions

On 06/30/2017 11:32 PM, rajan wrote:
> have trouble understanding xmin and xmax with update operations from two
> different sessions
>
> So, as found below, Session2 is trying to update a row which is already
> updated with a different value and it's update fails with *UPDATE 0*
>
> But from Session3, I see that xmax value is visible as Session2's txid. Why
> is it like that?
> Can we not put Session2's txid to xmin instead(although the update failed)?
> And if we try to consider that xmax is update with Session2's txid bcoz the
> update failed, then why bother updating the xmax?
>
> Please help me understand this.

One thing to remember is that txid_current() is more then informational:

https://www.postgresql.org/docs/9.6/static/functions-info.html

txid_current() bigint get current transaction ID, assigning a new one
if the current transaction does not have one

So calling it can advance the xid manually. Some testing here showed
that what xmin or xmax is created depends on when you call txid_current
in either the original session or the concurrent sessions.

Also worth noting that an UPDATE in Postgres is a DELETE/INSERT process.
The clue is the ctid value. In Session 2 you are looking at the
original row(ctid=(0, 2) which has been marked as deleted(non-zero
xmax). In Session 3 you are looking at the new row(ctid(0, 4)).

>
> *Session1*
> testdb=# BEGIN;
> BEGIN
> testdb=# select ctid, xmin, xmax, * from numbers;
> ctid | xmin | xmax | number
> -------+--------+------+--------
> (0,2) | 519107 | 0 | 14
> (0,3) | 519112 | 0 | 23
> (2 rows)
>
> testdb=# select txid_current();
> txid_current
> --------------
> 519114
> (1 row)
>
> testdb=# update numbers set number=24 where number=14;
> UPDATE 1
> testdb=# COMMIT;
> COMMIT
>
>
> *Session 2*
> testdb=# BEGIN;
> BEGIN
> testdb=# select txid_current();
> txid_current
> --------------
> 519115
> (1 row)
>
> testdb=# select ctid, xmin, xmax, * from numbers;
> ctid | xmin | xmax | number
> -------+--------+------+--------
> (0,2) | 519107 | 0 | 14
> (0,3) | 519112 | 0 | 23
> (2 rows)
>
> testdb=# select ctid, xmin, xmax, * from numbers;
> ctid | xmin | xmax | number
> -------+--------+--------+--------
> (0,2) | 519107 | 519114 | 14
> (0,3) | 519112 | 0 | 23
> (2 rows)
>
> testdb=# update numbers set number=25 where number=14;
> UPDATE 0
> testdb=# COMMIT;
> COMMIT
>
> *Session 3*
> testdb=# select txid_current();
> txid_current
> --------------
> 519116
> (1 row)
>
> testdb=# select ctid, xmin, xmax, * from numbers;
> ctid | xmin | xmax | number
> -------+--------+--------+--------
> (0,3) | 519112 | 0 | 23
> (0,4) | 519114 | 519115 | 24
> (2 rows)
>
>
>
>
> -----
> --
> Thanks,
> Rajan.
> --
> View this message in context: http://www.postgresql-archive.org/have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969629.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com