Re:Postgres sequences can skip 32 unexpectedly
by peterejhamilton on Friday July 16, 2021 @11:45AM
(#27857502)
Nice - we're using a very similar approach now (procedure that runs just before creation) which I think will last us a good while. Glad to know it's worked out well for you :)
Re:Postgres sequences can skip 32 unexpectedly
by leftnode on Friday July 16, 2021 @12:02PM
(#27857709)
Another added benefit is that you can build a simple interface to allow end users to adjust their sequences (or our support staff in this instance).
In our system, by default, all objects start at 1000. If a new account is created, and they want to increase a sequence to some value (say they already have 5000 invoices in QuickBooks and they want to start all new invoices at 10000 so they know every invoice #IN10000 and higher was created in our system), we have a simple interface that one of our support staff can go to arbitrarily increase the next value.
Re:Postgres sequences can skip 32 unexpectedly
by garblegarble on Friday July 16, 2021 @02:46PM
(#27859736)
>We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well.
I assume that means you also have to only allow one TX to be in-flight at a time adding a new record whose ID is generated from a given sequence?
Re:Postgres sequences can skip 32 unexpectedly
by CodeWriter23 on Friday July 16, 2021 @04:05PM
(#27860629)
Or bounce an exception for insert duplicate on a unique index.
Customer #C1010 may have 2 locations #L1899 and #L8443 and many invoices #IN1940 and #IN2399 for example.
When we first built the system, I considered using native Postgres sequences to track these, but decided against them because of how they are affected during a rollback. In our system, each account has a record in a table that controls the next value of the sequence.
We have an event in our ORM to automatically generate the next sequence value as part of the transaction so if the transaction is rolled back, the next sequence value is as well. Sure, it requires locking the sequence record but it's a very small table and generating a sequence is quick. We wrapped everything up in a stored procedure named generate_sequence() which returns the next value of the sequence and increments it. It's scaled to millions of records quite well without issue.