Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 5 down vote accepted

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.

share|improve this answer
What would you do instead, use a 'version' column and a sequence? – Jack Douglas Apr 26 '13 at 18:18
That certainly sounds more sane. – Peter Eisentraut Apr 28 '13 at 1:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.