How to create multiple sequences in one table?

Question

I have a table "receipts". I have columns customer_id (who had the receipt) and receipt_number. The receipt_number should start on 1 for each customer and be a sequence. This means that customer_id and receipt_number will be unique. How can I elegantly do this. Can I use the built-in sequeance functionality with CREATE SEQUENCE or similar? It seems like I would have to create a sequence for each customer, which of course is not an elegant solution.

EDIT: There must be a thread-safe and idiot-secure way to do this. It should be quite a simple/common need.

Answer 1

SEQUENCE does not guarantee there are no gaps. For example, one transaction might generate a new number and then abort (due to a bug or a power failure or whatever...). The next transaction would then blindly get the next number, not the one that was "lost".

It would be best if your client application did not depend on "no gaps" assumption in the firs place. You could, however, minimize gaps like this:

1. SELECT MAX(receipt_number) FROM receipts WHERE customer_id = :ci
2. INSERT INTO receipts(customer_id, receipt_number) VALUES (:ci, aboveresult+1), or just insert 1 if step 1 returned NULL.
3. If step 2 returned a PK violation^*, retry from the beginning.

^*Because a concurrent transaction has gone through the same process and committed.

As long as rows are just added and not deleted, this should prevent any gaps, even in a concurrent environment.

---

BTW, you can "condense" steps 1 and 2 like this:

INSERT INTO receipts (customer_id, receipt_number)
SELECT :ci, COALESCE(MAX(receipt_number), 0) + 1
FROM receipts
WHERE customer_id = :ci;

[SQL Fiddle]

The index underneath the PK {customer_id, receipt_number} should ensure that the SELECT part of this query is satisfied efficiently.