5

I've been learning MVCC in PostgreSQL. As I understood, when the row is updated, a new row will be added and the xmax value in old row will have the value of the xmin of the newly added row. When I checked it in the table I have in my Postgres DB, the xmin and xmax values are the same. What does it mean?

   xmin   |   xmax   |    id    |     serial     | paid 
----------+----------+----------+----------------+------
 54569114 | 54569114 | 11099324 | 76605297311437 | t
 54569111 | 54569111 | 11099323 | 38131108141783 | t
 54569101 | 54569101 | 11099322 | 49399633274146 | t
 54569092 |        0 | 11099321 | 44672543705101 | f
 54569090 | 54569090 | 11099320 | 21324499293647 | t
 54569083 |        0 | 11099319 | 82878160317074 | f
 54569079 | 54569079 | 11099318 | 31091666079121 | t
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Sarvar Nishonboyev
  • 12,262
  • 10
  • 69
  • 70

1 Answers1

5

xmax does double duty: it is used to mark visibility (together with xmin, but it is also used to hold row locks.

To disambiguate between these two uses the status of the transaction and further “invisible” flags in the tuple are used.

So if you see a row where the two values are identical, the transaction that created the row also locked the row. This is normally a sign that the tuple was created with INSERT ... ON CONFLICT.

See this answer and my blog post for more information.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Some records have `0` value in `xmax`. `xmax` values are becoming identical after updating a row. If you notice, paid is true on the rows where `xmin` and `xmax` are identical. Because this is invoice table, and when payment is made, invoice will got updated. – Sarvar Nishonboyev Feb 21 '19 at 21:27
  • 1
    As I said, the transaction that created the row must have locked it. Try to find out the exact sequence of SQL statements, then you can understand how it happened. – Laurenz Albe Feb 22 '19 at 05:01