Why xmax system column in postgres table is not zero for non-deleted row?

Question

I have following table:

       Table "public.product"
 Column |  Type   |                      Modifiers
--------+---------+------------------------------------------------------
 id     | integer | not null default nextval('product_id_seq'::regclass)
 name   | text    | not null
Indexes:
    "product_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "stock" CONSTRAINT "stock_product_id_fkey" FOREIGN KEY (product_id) REFERENCES product(id)

This table has 2 records :-

postgres=# select xmin, xmax, cmin, cmax, * from product;
  xmin   |  xmax   | cmin | cmax | id |  name
---------+---------+------+------+----+---------
 3126848 | 3126856 |    0 |    0 |  1 | Parle-G
 3126849 | 3126858 |    0 |    0 |  2 | Pepsi
(2 rows)

I have following questions related to xmax and cmin columns:-

1. As per postgres docs, xmax stores the id of the transaction, which deleted this row. But since my row is not yet deleted, then why does it contain non-zero value.

2. Postgres docs says that cmin contains the sequence number of statement in the transaction, which has created this row. As per SQL standard, every statement is a transaction on its own, so in that way, it must contains non-zero number for cmin column.

Please Correct me, where I am wrong. Thanks in advance.

Answer 1

I'll answer your second question first, since that is simple:

cmin starts counting at zero, so a cmin of zero means that this was the first statement in the transaction. If all your transactions consist of a single statement, you can expect cmin to be zero throughout.

About the meaning of xmax:

Apart from storing the number of the transaction that deleted the row, this system column is also used to store row locks. These are stored on the row itself rather than in shared memory to avoid overflows of the lock table.

Row locks are stored in two different ways:

• If only a single transaction holds a lock on a row, the transaction number is stored in xmax.

• If more than one transaction has a (shared) lock on a row, a multixact structure is created, and its number is stored in xmax.

There is no conflict between the different uses of xmax, because the database has additional information:

• Additional flags in the table row (that are not visible as system columns) disambiguate the meaning of xmax.

• The commit log stores if a transaction has committed or not. Only rows deleted by a committed transaction are really deleted.

To sum it up, if you see a non-zero value in xmax, that can mean four things:

• The row has been deleted or updated, and your query is running with a snapshot older than the modifying transaction.

• The row has been deleted or updated by a transaction that was rolled back.

• The row is locked or has been locked at some point in the past, and you see the transaction ID of the locking transaction.

• Like above, but you see a multixact value. You can use the function pg_get_multixact_members to see which transactions belong to it.

You might be interested in reading my blog post where I explore this question in more detail.