Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
On Mon, Jul 3, 2017 at 10:39 AM, rajanwrote: > Thanks, Jeff. > > Now I am going back to my old question. > > Even though *Session 2* fails to update with UPDATE 0 message, its txid is > saved in xmax of updated(by *Session 1*) tuple. > > As it becomes an old txid, how come new txids are able to view it? > The database can see everything. That is its job. It constructs the principles of ACID out of non-ACID components. But once you use pageinspect or select the system columns mxin and xmax, you start to peek through that illusion. Cheers, Jeff
[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
Thanks, Jeff. Now I am going back to my old question. Even though *Session 2* fails to update with UPDATE 0 message, its txid is saved in xmax of updated(by *Session 1*) tuple. As it becomes an old txid, how come new txids are able to view it? - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969857.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
On Mon, Jul 3, 2017 at 3:02 AM, rajanwrote: > Thanks for the explanation. > > will I be able to view the information using this function, > SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0)); > > Also, please let me know which column I should refer for viewing the > pointer. > It is 't_ctid' Cheers, Jeff
[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
Thanks for the explanation. will I be able to view the information using this function, SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0)); Also, please let me know which column I should refer for viewing the pointer. - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969767.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
On Sat, Jul 1, 2017 at 8:55 PM, rajanwrote: > Thanks, Jeff. That helps understanding it 50%. > > *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple > is > marked for deletion. It means that *(0,2) never exists* when Session 2 is > trying to perform the update. > That it never exists is an appearance presented to the user. The database system works hard to maintain that illusion but the database system itself sees through the illusion. It blocks on (0,2) waiting for session 1 to commit, and then once that happens session 2 goes and finds the new version of that row ((0,4) in this case) and locks it. If you use pageinspect, you can see that (0,2) has left a pointer behind pointing to (0,4) to make it easy to find. Cheers, Jeff
[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
Thanks, Jeff. That helps understanding it 50%. *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple is marked for deletion. It means that *(0,2) never exists* when Session 2 is trying to perform the update. In that case, how *Session 3's new row (0,4)* contains the xmax as *Session 2's txid*. - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969661.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
On Sat, Jul 1, 2017 at 6:32 PM, rajanwrote: > hello, > > thanks for replies, Adrian, Steven. > > >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. > > I understand this and I am executing my statements inside a Transaction > block so the xid is not incremented when calling it. > > >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)). > > Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115 > in which the update failed with *UPDATE 0* . This is where I can not > understand, > 1. Row (0,4) is updated with correct value and (0,3) is not visible in > Session 2, which is good. > 2. but in *Session 3* (0,4) also carries xmax which means what? Is it also > marked for deletion? It can't be, right? > When session 2 encounters the locked row which meets the criterion for the update, it has to wait for the locking transaction to finish. At that point it locks the row (by writing its transaction into the xmax, and setting a flag not visible to you, unless you use pgeinspect) and then re-evaluates if it still meets the criterion. Since it doesn't meet the criterion anymore, it doesn't finish updating the tuple. Cheers, Jeff
[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions
hello, thanks for replies, Adrian, Steven. >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. I understand this and I am executing my statements inside a Transaction block so the xid is not incremented when calling it. >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)). Yes. But why (ctid(0,4)) in *Session 3* carries the xmax of the txid 519115 in which the update failed with *UPDATE 0* . This is where I can not understand, 1. Row (0,4) is updated with correct value and (0,3) is not visible in Session 2, which is good. 2. but in *Session 3* (0,4) also carries xmax which means what? Is it also marked for deletion? It can't be, right? - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Re-have-trouble-understanding-xmin-and-xmax-with-update-operations-from-two-different-sessions-tp5969644p5969656.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general