Strange cleanup behaviour with FOR UPDATE

Question

I wonder if xmax value should not be set to zero after UPDATE operation even if FOR UPDATE clause is used in a subquery?

I have created test table:

CREATE TABLE def.dummy
(
  id serial NOT NULL,
  nazwa text,
  CONSTRAINT pk_dummy PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

and inserted some values:

INSERT INTO def.dummy(
            nazwa)
    VALUES ('1'),('2'),('3');

Now when I'm trying to do UPDATE with subquery:

UPDATE def.dummy AS sp
SET nazwa = 'changed' FROM
(SELECT id, nazwa, xmax, xmin
 FROM def.dummy
          WHERE id=1
AND xmax = 0
LIMIT 1 FOR UPDATE) AS get_set
WHERE get_set.id = sp.id;
RETURNING sp.*;

After commiting transaction xmax value doesn't reset to 0 but stays as:

Is it correct behaviour? If yes, why xmax isn't set to zero after operation?

Answer 1

Yes, that behavior is correct. Xmax is also used for lockers, not only updaters, and it's up to the reader of the value to ensure that it ignores a non-zero value when the LOCK bits are set in t_infomask (see src/include/access/htup_details.h for values of the bits in t_infomask). You can't access t_infomask from the regular SQL interface though, but see the pageinspect extension which gives you access to it.

Note that tuples can be locked by foreign key checks also, so it's not just FOR UPDATE that would cause nonzero values to appear in Xmax.