How to use Xmin in PostgreSQL Queries

January 23, 2023

You might be aware that Postgres uses invisible columns to track concurrent row access and modifications. My  mvcc talk covers much of this.


Someone at a conference mentioned they were using the invisible xmin column to perform updates. I was initially skeptical of this approach, but once he explained the purpose, it made sense, e.g.:


INSERT INTO mvcc_test VALUES (1, 'Sal');
SELECT * FROM mvcc_test;
 x | name
 1 | Sal
SELECT xmin, * FROM mvcc_test;
 xmin | x | name
  715 | 1 | Sal
UPDATE mvcc_test SET name = 'Sally' WHERE x = 1 AND xmin = 715;
SELECT xmin, * FROM mvcc_test;
 xmin | x | name
  716 | 1 | Sally


Why would you do this? Normally this would be done using select ... for update. However, what if you want to do the update without locking the row between select and update, and are willing to discard the update if the row has already been updated by another session? This is a case where using xmin in updates makes sense.


(Article originally published on Bruce's blog on Wednesday, October 4, 2017

Share this

Relevant Blogs

An Overview of PostgreSQL Indexes

SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023

Postgres AT TIME ZONE Explained

I saw AT TIME ZONE used in a query, and found it confusing. I read the Postgres documentation and was still confused, so I played with some queries and finally figured it...
January 24, 2023

More Blogs

PostgreSQL and Golang Tutorial

SUMMARY: This article covers how to use PostgreSQL together with the programming language Golang (Go). After showing how to get started with Go’s Object Relational Mapper, it...
January 24, 2023