What is the proper way to manually sequence a column in Postgres?

Question

I have a SaaS pet project for invoicing. In it, I want my clients to each start with ticket number 1001. Clearly, I can't use a simple auto field in Postgres and just add 1000 to the value, because all my clients will share the same database and the same tickets table.. I've tried using an integer column type and querying (pseudo SQL) SELECT LATEST number FROM tickets WHERE client_id = [current client ID] to get the latest number, and then using that number + 1 to get the next number. The problem is that with concurrency, it's easily possible for two tickets to end with the same number this way. the number I need to be able to do this within Django or with raw SQL (vs using Bash or anything else of the sort).

I'm not looking for a way to force my example to work. I'm just looking for a solution to my the problem of needing independently incrementing ticket numbers for each client.

Answer 1

I don't think there is a "cheap" solution to this problem. The only solution that is safe (but not necessarily fast) in a multi-user environment is to have a "counter" table with one row for each customer.

Each transaction has to first lock the customer's entry before inserting a new ticket, something like this:

UPDATE cust_numbers
  SET current_number = current_number + 1
WHERE cust_id = 42
RETURNING current_number;

That will do three things in one step

1. increase the current "sequential" number for that customer
2. lock the row so other transactions doing the same will have to wait for a lock
3. return the new value of that column.

With that new number you can now insert a new ticket. If the transaction is committed, it will also release the lock on the cust_numbers table, thus other transactions "waiting for a number" can proceed.

You could wrap the two steps (update.. returning & the insert) into a single stored function so that the logic behind this is centralized. Your application would only call select insert_ticket(...) without knowing how the ticket number is generated.

You might also want to create a trigger on the customer table to automatically insert a row into the cust_numbers table when a new customer is created.

The disadvantage of this is that you effectively serialize the transactions that are inserting new tickets for the same customer. Depending on the volumn of inserts in your system this might turn out to be a performance problem.

Edit
Another disadvantage of this is, that you are not forced to insert tickets that way which might lead to problems if e.g. a new developer forgets about this.

Answer 2

You can create sequences for each customer and then set the column's value to nextval('name_of_the_sequence'). This is actually how serial works; the only difference in your case would be that you don't use a default value for the column and have more than one sequence.

Creating those sequences choosing the correct one when inserting a new row could be done nicely through a PL/Pgsql procedure.