1

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: xmax after update

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

Borys
  • 2,676
  • 2
  • 24
  • 37

1 Answers1

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.

alvherre
  • 2,499
  • 20
  • 22
  • thank you for your answer. do you know maybe where can I find those informations? in documentation could not find anything about foreign key checks for instance. – Borys Jun 24 '14 at 16:39
  • 1
    I don't think we document this kind of thing; it would become a problem whenever we wanted to change the underlying representation in the future, because if we documented it, people would become dependent on a particular way of doing it. Of course, you can always read the source code, which for PostgreSQL is pretty well documented. – alvherre Jun 24 '14 at 20:43