5

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.

orokusaki
  • 55,146
  • 59
  • 179
  • 257
  • 1
    Possible dupe: http://stackoverflow.com/questions/6046085/postgresql-sequence-that-ensure-a-unique-id – Tim Lehner Apr 18 '12 at 13:58
  • Not a duplicate because that's looking for a unique number. Mine is looking for the next number in sequence with respect to another field (the client field). In fact, mine states that I cannot use an auto field (i.e., serial). – orokusaki Apr 18 '12 at 13:59
  • Gotcha, I understand now. Hopefully my answer works for you (or, better yet, someone else has something more elegant). – Tim Lehner Apr 18 '12 at 14:19

2 Answers2

5

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.

  • Excellent, thanks! Questions though, A) will this lock be for the entire table, or can I lock a single row? I wouldn't want all 2000 clients to have to wait for this lock from client X to be released before they could create more tickets (imagine 2-3 tickets per second concurrency), and B) What happens (and what is the proper recover path), if a second connection queries for this number while it's locked? re: "serialize the transactions" - how would it be a performance problem when the previous ticket transactions have to complete before new ones. Is it a big problem? I will have high volume. – orokusaki Apr 18 '12 at 15:28
  • 2
    @orokusaki: As I have described: only the row for that customer will be locked. The second connection will wait until the first commits. Yes, this will decrease your performance. But you can either get the numbers like this (slow but secure) or get them fast using a sequence but then they would be unique across all customers. You have to decide what's more important –  Apr 18 '12 at 16:13
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.

ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • Aside from the issue of needing 2000 columns to do this, the fundamental problem with using a serial field is that a rolled back transaction still uses up the number that `nextval` returns, so if ticket numbers for a given client could be 1001, 1002, 1005, 1007, etc., vs maintaining a real sequence (i.e., a serial column's value is not an accurate measure of cardinality). – orokusaki Apr 18 '12 at 14:07
  • Please read my answer. I did not talk about serial columns or different columns. You can create sequences separately and fetch values from them using `nextval()` – ThiefMaster Apr 18 '12 at 14:17
  • @ThiefMaster, are you suggesting creating sequences in a trigger (inserted customer) and then necessitating dynamic SQL to select the values? (I'm a Postgres noob, so I'm not sure if any of this is even possible, but presumably the OP needs an automatic way of doing this stuff) – Tim Lehner Apr 18 '12 at 14:21
  • You could probably nicely wrap it into PL/Pgsql procedures. – ThiefMaster Apr 18 '12 at 14:23