postgresql - General DB counter

Question

I would like to implement solution where my version will be incremented by 1 for each data type while saving to DB and it will be thread safe.

My ideas: Idea 1: I created 2 sequences type1_seq and type2_seq and I used nextVal() function but nextVal is never rollbacked in case of exception so sometimes I had gap in my counter e.g 1,2,4.

Idea 2: Before saving new record I should used query(e.g findMaxVersionByDataType()) to get max version by data type and increment it by 1 but it is not thread safe. Optimistic locking is useless in this case I think because as far as I understand I need to block saving records by other threads in window of time right after calling findMaxVersionByDataType() till the persisting record by my thread.

Do you know any good performance solution?

question from:https://stackoverflow.com/questions/65830182/general-db-counter

Answer 1

There is no good performance solution for a gap-less sequence. At a minimum you need to store the sequence on a table and lock the row (or table) on each access until the transaction commits. Gaps are normal and expected with sequences. It is best to get used to, accept it, and move-on. You can of course create a view that derives the version on the fly. See example.

create view type_view as 
    select  id, type_cd, row_number() over (partition by type_cd) as version 
      from type_data
    order by id;