3 ms·

We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, loca

We have a similar system (multi-tenant database) where each tenant (account) has objects that have unique identifiers for that specific account (customers, locations, jobs, invoices, etc). 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.

peterejhamilton 5y ago
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 :)
- leftnode 5y ago
  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.
garblegarble 5y ago
>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?
- CodeWriter23 5y ago
  Or bounce an exception for insert duplicate on a unique index.

peterejhamilton 5y ago

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

leftnode 5y ago
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.

garblegarble 5y ago

>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?

CodeWriter23 5y ago
Or bounce an exception for insert duplicate on a unique index.