Обсуждение: unclear behavior xmax/xmin
Hey,
I did the next tests on postgresql 9.6 with the default transaction isolation level.
- I started transaction A and deleted one row but I didnt commit. Afterwards I rolledback the transaction but the xmax of the row was set to the txid of transaction A. I opened a new session and run VACUUM on the table. However, that record wasnt deleted but the xmax was still set to transaction`s A txid. Is that a bug ? the vacuum process shouldnt delete that row because it has an old xmax (there arent any younger transactions). In addition, I'm still capable of seeing the record (because of the rollback..) but my txid is bigger then the xmax of the record. ? Is that a bug ?
Example :
postgres=# create table trn as select generate_series(1,2);
SELECT 2
postgres=# start transaction;
START TRANSACTION
postgres=# select txid_current();
txid_current
--------------
140154775
(1 row)
postgres=# select *,xmin,xmax from trn ;
generate_series | xmin | xmax
-----------------+-----------+------
1 | 140154774 | 0
2 | 140154774 | 0
(2 rows)
postgres=# delete from trn where generate_series=1;
DELETE 1
postgres=# select *,xmin,xmax from trn ;
generate_series | xmin | xmax
-----------------+-----------+------
2 | 140154774 | 0
(1 row)
postgres=# rollback;
ROLLBACK
postgres=# select *,xmin,xmax from trn ;
generate_series | xmin | xmax
-----------------+-----------+-----------
1 | 140154774 | 140154775
2 | 140154774 | 0
-When I run an update basically its just like running an insert and delete (the xmax of the old row is changed to the transaction`s txid and the new row contains xmin same as txid of the current transaction and xmax is set to 0). However, when I query the table (didnt commit yet) I dont see the old record. Is there a way to see it ? Why is that ? I thought that I should see the table with a non zero value in the xmax col.
Example :
postgres=# select *,xmin,xmax from trn ;
generate_series | xmin | xmax
-----------------+-----------+-----------
1 | 140154774 | 140154775
2 | 140154774 | 0
(2 rows)
postgres=# start transaction;
START TRANSACTION
postgres=# update trn set generate_series=3 where generate_series=1;
UPDATE 1
postgres=# select *,xmin,xmax from trn ;
generate_series | xmin | xmax
-----------------+-----------+------
2 | 140154774 | 0
3 | 140154776 | 0
(2 rows)
Mariel Cherkassky wrote: > - I started transaction A and deleted one row but I didnt commit. > Afterwards I rolledback the transaction but the xmax of the row was set > to the txid of transaction A. I opened a new session and run VACUUM on the table. > However, that record wasnt deleted but the xmax was still set to > transaction`s A txid. Is that a bug ? the vacuum process shouldnt delete > that row because it has an old xmax (there arent any younger transactions). > In addition, I'm still capable of seeing the record (because of the rollback..) > but my txid is bigger then the xmax of the record. ? Is that a bug ? I wrote about that a whil ago, see https://www.cybertec-postgresql.com/en/whats-in-an-xmax/ > -When I run an update basically its just like running an insert and delete > (the xmax of the old row is changed to the transaction`s txid and the new row > contains xmin same as txid of the current transaction and xmax is set to 0). > However, when I query the table (didnt commit yet) I dont see the old record. > Is there a way to see it ? Why is that ? I thought that I should see the table > with a non zero value in the xmax col. In the transaction that ran the UPDATE, you will of course see the updated row. Everything else would be inconsistent. You have to start a second database session if you want to see the previous row version. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Hi, On 2018-11-18 19:13:28 +0200, Mariel Cherkassky wrote: > - I started transaction A and deleted one row but I didnt commit. > Afterwards I rolledback the transaction but the xmax of the row was set to > the txid of transaction A. I opened a new session and run VACUUM on the > table. However, that record wasnt deleted but the xmax was still set to > transaction`s A txid. Is that a bug ? No. A vacuum will not necessarily immeditely process a row/page if has recently been modified. > the vacuum process shouldnt delete > that row because it has an old xmax (there arent any younger transactions). > In addition, I'm still capable of seeing the record (because of the > rollback..) but my txid is bigger then the xmax of the record. ? Is that a > bug ? No, it's not. For visiblity checks it's not just the xid that counts, but also whether this xid committed. bool HeapTupleSatisfiesMVCC(HeapTuple htup, Snapshot snapshot, Buffer buffer) ... if (!(tuple->t_infomask & HEAP_XMAX_COMMITTED)) { if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple))) { if (HeapTupleHeaderGetCmax(tuple) >= snapshot->curcid) return true; /* deleted after scan started */ else return false; /* deleted before scan started */ } if (XidInMVCCSnapshot(HeapTupleHeaderGetRawXmax(tuple), snapshot)) return true; if (!TransactionIdDidCommit(HeapTupleHeaderGetRawXmax(tuple))) { /* it must have aborted or crashed */ SetHintBits(tuple, buffer, HEAP_XMAX_INVALID, InvalidTransactionId); return true; } > -When I run an update basically its just like running an insert and delete > (the xmax of the old row is changed to the transaction`s txid and the new > row contains xmin same as txid of the current transaction and xmax is set > to 0). However, when I query the table (didnt commit yet) I dont see the > old record. Is there a way to see it ? Why is that ? I thought that I > should see the table with a non zero value in the xmax col. You can use the pageinspect extension to see such rows. We can't normally show such rows, because it'd violate transactional semantics. What are you trying to achieve? Just exploring the system? Greetings, Andres Freund
Just exploring the database and trying to understand the logic behind things :)
So after a rollback why the xmax isnt changed back to 0 ? Seems like it should be set again to 0.
Thanks a lot Andres !
בתאריך יום א׳, 18 בנוב׳ 2018 ב-22:24 מאת Andres Freund <andres@anarazel.de>:
Hi,
On 2018-11-18 19:13:28 +0200, Mariel Cherkassky wrote:
> - I started transaction A and deleted one row but I didnt commit.
> Afterwards I rolledback the transaction but the xmax of the row was set to
> the txid of transaction A. I opened a new session and run VACUUM on the
> table. However, that record wasnt deleted but the xmax was still set to
> transaction`s A txid. Is that a bug ?
No. A vacuum will not necessarily immeditely process a row/page if has
recently been modified.
> the vacuum process shouldnt delete
> that row because it has an old xmax (there arent any younger transactions).
> In addition, I'm still capable of seeing the record (because of the
> rollback..) but my txid is bigger then the xmax of the record. ? Is that a
> bug ?
No, it's not. For visiblity checks it's not just the xid that counts,
but also whether this xid committed.
bool
HeapTupleSatisfiesMVCC(HeapTuple htup, Snapshot snapshot,
Buffer buffer)
...
if (!(tuple->t_infomask & HEAP_XMAX_COMMITTED))
{
if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple)))
{
if (HeapTupleHeaderGetCmax(tuple) >= snapshot->curcid)
return true; /* deleted after scan started */
else
return false; /* deleted before scan started */
}
if (XidInMVCCSnapshot(HeapTupleHeaderGetRawXmax(tuple), snapshot))
return true;
if (!TransactionIdDidCommit(HeapTupleHeaderGetRawXmax(tuple)))
{
/* it must have aborted or crashed */
SetHintBits(tuple, buffer, HEAP_XMAX_INVALID,
InvalidTransactionId);
return true;
}
> -When I run an update basically its just like running an insert and delete
> (the xmax of the old row is changed to the transaction`s txid and the new
> row contains xmin same as txid of the current transaction and xmax is set
> to 0). However, when I query the table (didnt commit yet) I dont see the
> old record. Is there a way to see it ? Why is that ? I thought that I
> should see the table with a non zero value in the xmax col.
You can use the pageinspect extension to see such rows. We can't
normally show such rows, because it'd violate transactional semantics.
What are you trying to achieve? Just exploring the system?
Greetings,
Andres Freund
Hi, (Please don't top quote on this list, we do inline responses here) On 2018-11-18 22:28:24 +0200, Mariel Cherkassky wrote: > Just exploring the database and trying to understand the logic behind > things :) > So after a rollback why the xmax isnt changed back to 0 ? Seems like it > should be set again to 0. Because that'd mean that a rollback would have to re-process every page that the transaction has touched, which'd be pretty expensive for large transactions. And we couldn't rely on it being 0, because there's also the case that the machine crashed. Greetings, Andres Freund