From: | Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | unclear behavior xmax/xmin |
Date: | 2018-11-18 17:13:28 |
Message-ID: | CA+t6e1mvt0Fw-d2_vgKHBO=Wn-yFinAq7DTDjC-wbfD=+mmjCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-11-18 19:04:49 | Re: unclear behavior xmax/xmin |
Previous Message | Achilleas Mantzios | 2018-11-18 13:38:02 | Re: postgresql history and timelines |