10

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?

  • 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
  • 2
    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
2

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
  • 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
  • 1
    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
3

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;
    
  • 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
  • 1
    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
  • 1
    Thanks! Sequences are definitely the way to go if I need to store the revision number. – Julien Portalier Aug 9 '13 at 7:54
  • 2
    Note that having huge amounts of sequences is a major hit on performance, since a sequence is essentially a table with one row. You can read more about that here – Magnuss Mar 9 '18 at 10:45
2

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.

  • 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
  • 1
    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
2

(I came to this question when trying to re-discover an article about this topic. Now that I've found it, I'm posting it here in case others are in pursuit of an alternate option to the currently chosen answer—windowing with row_number())

I have this same use case. For each record inserted into a specific project in our SaaS we need a unique, incrementing number which can be generated in the face of concurrent INSERTs and is ideally gapless.

This article describes a nice solution, which I'll summarise here for ease and posterity.

  1. Have a separate table which acts as the counter to provide the next value. It will have two columns, document_id and counter. counter will be DEFAULT 0 Alternatively, if you already have a document entity that groups all versions, a counter could be added there.
  2. Add a BEFORE INSERT trigger to the document_versions table which atomically increments the counter (UPDATE document_revision_counters SET counter = counter + 1 WHERE document_id = ? RETURNING counter) and then sets NEW.version to that counter value.

Alternatively, you might be able to use a CTE to do this at the application layer (though I prefer it to be a trigger for consistency's sake):

WITH version AS (
  UPDATE document_revision_counters
    SET counter = counter + 1 
    WHERE document_id = 1
    RETURNING counter
)

INSERT 
  INTO document_revisions (document_id, rev, other_data)
  SELECT 1, version.counter, 'some other data'
  FROM "version";

This is similar in principle to how you were trying to solve it initially, except that by modifying a counter row in a single statement it blocks reads of the stale value until the INSERT is committed.

Here's a transcript from psql showing this in action:

scratch=# CREATE TABLE document_revisions (document_id integer, rev integer, other_data text, PRIMARY KEY (document_id, rev));
CREATE TABLE

scratch=# CREATE TABLE document_revision_counters (document_id integer PRIMARY KEY, counter integer DEFAULT 0);
CREATE TABLE

scratch=# WITH version AS (
    INSERT INTO document_revision_counters (document_id) VALUES (2)
      ON CONFLICT (document_id)
      DO UPDATE SET counter = document_revision_counters.counter + 1
      RETURNING counter;
  )
  INSERT 
    INTO document_revisions (document_id, rev, other_data)
    SELECT 2, version.counter, 'doc 1 v1'
    FROM "version";
INSERT 0 1

scratch=# WITH version AS (
    INSERT INTO document_revision_counters (document_id) VALUES (2)
      ON CONFLICT (document_id)
      DO UPDATE SET counter = document_revision_counters.counter + 1
      RETURNING counter;
  )
  INSERT 
    INTO document_revisions (document_id, rev, other_data)
    SELECT 2, version.counter, 'doc 1 v2'
    FROM "version";
INSERT 0 1

scratch=# WITH version AS (
    INSERT INTO document_revision_counters (document_id) VALUES (2)
      ON CONFLICT (document_id)
      DO UPDATE SET counter = document_revision_counters.counter + 1
      RETURNING counter;
  )
  INSERT 
    INTO document_revisions (document_id, rev, other_data)
    SELECT 2, version.counter, 'doc 2 v1'
    FROM "version";
INSERT 0 1

scratch=# SELECT * FROM document_revisions;
 document_id | rev | other_data 
-------------+-----+------------
           2 |   1 | doc 1 v1
           2 |   2 | doc 1 v2
           2 |   1 | doc 2 v1
(3 rows)

As you can see, you have to be careful about how INSERTs happen, hence the trigger version, which looks like this:

CREATE OR REPLACE FUNCTION set_doc_revision()
RETURNS TRIGGER AS $$ BEGIN
  WITH version AS (
    INSERT INTO document_revision_counters (document_id, counter) VALUES (NEW.document_id, 1)
    ON CONFLICT (document_id)
    DO UPDATE SET counter = document_revision_counters.counter + 1
    RETURNING counter
  )

  SELECT INTO NEW.rev counter FROM version; RETURN NEW; END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER set_doc_revision BEFORE INSERT ON document_revisions
FOR EACH ROW EXECUTE PROCEDURE set_doc_revision();

That makes INSERTs much more straight forward and the integrity of the data more robust in face of INSERTs originating from arbitrary sources:

scratch=# INSERT INTO document_revisions (document_id, other_data) VALUES (1, 'baz');
INSERT 0 1

scratch=# INSERT INTO document_revisions (document_id, other_data) VALUES (1, 'foo');
INSERT 0 1

scratch=# INSERT INTO document_revisions (document_id, other_data) VALUES (1, 'bar');
INSERT 0 1

scratch=# INSERT INTO document_revisions (document_id, other_data) VALUES (42, 'meaning of life');
INSERT 0 1

scratch=# SELECT * FROM document_revisions;
 document_id | rev |   other_data    
-------------+-----+-----------------
           1 |   1 | baz
           1 |   2 | foo
           1 |   3 | bar
          42 |   1 | meaning of life
(4 rows)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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