Is there better way to 'touch' a row than performing a dummy update?

I use the xmin system column to implement a form of optimistic locking, and sometimes need to 'touch' the row to bump xmin without actually updating the row. I currently just do a 'dummy' update:

create table t(id integer);
insert into t(id) values(1);
insert into t(id) values(2);

select xmin::text from t where id=1
/*    
|  XMIN |
---------
| 87159 |
*/

update t set id=id where id=1

select xmin::text from t where id=1
/*
|  XMIN |
---------
| 87196 |
*/

(SQL Fiddle)

I'm curious whether there is another way of bumping the xid without doing an update, analogous to the unix touch command?

Well, if you want to change the xmin, then it needs to be ensured that other transactions can still see the old version of the row, so you need to make a copy with the new xmin, which you do with an UPDATE. You can reduce the impact of this by updating a non-indexed column, to get the benefit of HOT.

Of course, this whole line of thinking is dubious, and perhaps you should be using some other facility altogether.

StackExchange Database Administrators Q#40815, answer score: 7