How to keep an unique counter per row with PostgreSQL? 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?
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;