Skip to content
Webinar Series: Oracle Migration • Sept 27 • Register Now
Webinar Series: EDB Postgres Distributed • Oct 12 & 26 • Register Now


How to use Xmin in PostgreSQL Queries

Bruce Momjian12/5/2017

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

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business value o ...