How to keep an unique counter per row with PostgreSQL?

(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)

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.

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;
  

Given that postgresql supports isolation levels, you can go for serializable and consider the typical "better forgiveness than permission" approach to avoid locks. Here example in RoR:

  begin
    Item.transaction(isolation: :serializable) do 
      #Item.lock("FOR NO KEY UPDATE").... not needed 
      Item.increment_counter(:frequency_now, itemid) 
    end
  rescue
    retry
  end

FOR rowdata1 IN

    select   employee_id, accrual_group_code, accrual_type, start_date,accru_date from z_123  where (doc_id =0 or doc_id is null ) order by accru_date  
            LOOP        counter = counter+1;

            update  z_123 set company_id = 2 , doc_id= counter where employee_id = rowdata1.employee_id
                            and  accrual_group_code = rowdata1.accrual_group_code and  accrual_type = rowdata1.accrual_type and accru_date = rowdata1.accru_date;                                
            END LOOP;