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 need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related document.

I initially came up with something like:

current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);

But there is a race condition!

I'm trying to solve it with pg_advisory_lock, but the documentation is a bit scarce and I don't fully understand it, and I don't want to lock something by mistake.

Is the following acceptable, or am I doing it wrong, or is there a better solution?

SELECT pg_advisory_lock(123);
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(123);

Shouldn't I lock the document row (key1) for a given operation (key2) instead? So that would be the proper solution:

SELECT pg_advisory_lock(id, 1) FROM documents WHERE id = 123;
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(id, 1) FROM documents WHERE id = 123;

Maybe I'm not used to PostgreSQL and a SERIAL can be scoped, or maybe a sequence and nextval() would do the job better?

share|improve this question
I don't understand what you mean with "for a given operation" and where "key2" came from. – Trygve Laugstøl Aug 8 '13 at 12:20
Your locking strategy looks OK if you want pessimistic locking, but I would use pg_advisory_xact_lock so all the locks are automatically released on COMMIT/ROLLBACK. – Trygve Laugstøl Aug 8 '13 at 12:23
up vote 1 down vote accepted

Assuming you store all revisions of the document in a table, an approach would be to not store the revision number but calculate it based on the number of revisions stored in the table.

It is, essentially, a derived value, not something that you need to store.

A window function can be used to calculate the revision number, something like

row_number() over (partition by document_id order by <change_date>)

and you'll need a column something like change_date to keep track of the order of the revisions.

On the other hand, if you just have revision as a property of the document and it indicates "how many times the document has changed", then I would go for the optimistic locking approach, something like:

update documents
set revision = revision + 1
where document_id = <id> and revision = <old_revision>;

If this updates 0 rows, then there has been intermediate update and you need to inform the user of this.

In general, try to keep your solution as simple as possible. In this case by

  • avoiding the use of explicit locking functions unless absolutely necessary
  • having fewer database objects (no per document sequences) and storing fewer attributes (don't store the revision if it can be calculated)
  • using a single update statement rather than a select followed by an insert or update
share|improve this answer
Indeed, I don't need to store the value when it can be computed. Thanks for reminding me! – Julien Portalier Aug 9 '13 at 7:52
Actually, in my context, older revisions will be deleted at some point, so I can't compute it or the revision number would decrease :) – Julien Portalier Aug 9 '13 at 8:08

SEQUENCE is guaranteed to be unique, and your use-case looks applicable if your number of documents isn't too high (else you have a lot of sequences to manage). Use the RETURNING clause to get the value that was generated by the sequence. For example, using 'A36' as a document_id:

  • Per document, you could create a sequence to track the increment.
  • Managing the sequences will need to be handled with some care. You could perhaps keep a separate table containing the document names and the sequence associated with that document_id to reference when inserting/updating the document_revisions table.

     CREATE SEQUENCE d_r_document_a36_seq;
     INSERT INTO document_revisions (document_id, rev)
     VALUES ('A36',nextval('d_r_document_a36_seq')) RETURNING rev;
share|improve this answer
Thanks for the formatting deszo, I didn't notice that how bad it looked when I pasted in my comments. – bma Aug 8 '13 at 15:05
A sequence is a bad counter if you want the next value to be previous + 1 as they don't run within the transaction. – Trygve Laugstøl Aug 8 '13 at 16:45
Eh? Sequences are atomic. That's why I suggested a sequence per document. They are also not guaranteed to be gap-free, as rollbacks don't de-increment the sequence after it has incremented. I'm not saying that proper locking is not a good solution, only that sequences present an alternative. – bma Aug 8 '13 at 17:47
Thanks! Sequences are definitely the way to go if I need to store the revision number. – Julien Portalier Aug 9 '13 at 7:54

This is often solved with optimistic locking:

SELECT version, x FROM foo;

version | foo
    123 | ..

UPDATE foo SET x=?, version=124 WHERE version=123

If the update returns 0 rows updated, you've missed your update because someone else already update the row.

share|improve this answer
Thanks! This is a good when you need to keep a counter of updates on a document! But I need an unique revision number for each rows in the document_revisions table, which won't be updated, and must be the follower of the previous revision (ie. the revision number of previous row + 1). – Julien Portalier Aug 9 '13 at 7:50
Hm, why can't you use this technique then? This is the only method (other than pessimistic locking) that will give you a gap-less sequence. – Trygve Laugstøl Aug 9 '13 at 10:57

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.