Under what scenario can value in xmin column be greater than in xmax column-CodePudding

I am currently having an issue, that a transaction trying to delete some rows is simply hanging. I found rows, which xmax value was set to that transaction ID. Funny enough, there are a handful of rows, where xmax is lower than xmin. What is more, the mentioned xmax value was set on rows with xmin larger than backend_xmin value associated with the mentioned transaction. I would be very grateful for a hint or explanation, why xmin is greated than xmax.

An extract of my data is as follows

tableoid   cmax xmax       cmin xmin        ctid        id
15979773    1   1838047967  1   1838054693  (10508,58)  391992038
15979773    1   1838047967  1   1838071633  (10525,48)  391833021
15979773    1   1838047967  1   1838075176  (10548,9)   391294625
15979773    1   1838047967  1   1838075891  (10543,28)  391590003
15979773    1   1838047967  1   1838076423  (10539,37)  391871511
15979773    1   1838047967  1   1838076905  (10539,39)  392007013
15979773    1   1838047967  1   1838078116  (10543,47)  391833029
15979773    1   1838047967  1   1838078121  (10540,56)  391855133
15979773    1   1838047967  1   1838078123  (10541,54)  391867004
15979773    1   1838047967  1   1838078125  (10550,27)  391503024

Status of the transaction in xmax is:

SELECT pg_xact_status (xid8 '1838047967');
in progress

xid addresses did not get wrapped around yet, as after starting new transaction its xid is bigger than above values:

begin;
    SELECT * FROM txid_current();
end;
commit;

1848210508

I unfortunately cannot use pageinspect

Version of Postres I am running is 14.3

CodePudding user response：

xmax can have several meanings. If you can see the row, that could mean that

a transaction updated or deleted the row, but got rolled back
a transaction locked the row, but did not modify it

Seeing that xmax is slightly lower than xmin, it must be that there are long running transactions (not nice). One of these long running transactions locked or updated a row that was created by a transaction that started later (which is possible in READ COMMITTED isolation level).

Unless you experience errors that indicate data corruption, I'd say that there is nothing to worry about. Perhaps see that your transactions don't take quite that long – long transactions cause all kind of trouble in databases.