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.:
CREATE TABLE mvcc_test (x INTEGER PRIMARY KEY, name TEXT);
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 @ https://momjian.us/main/blogs/pgblog/2017.html#October_4_2017)