6

In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus the corresponding Account id. But I would also like to assign each one an Account-specific id - just a number starting at 1 for each Account's first Customer and incrementing for each additional Customer - simply as an easy way for users in an Account to reference a Customer in the UI.

I can think of two possible solutions:

  1. Use the max() aggregate function to obtain the last Customer id used for the Account and increment it

    • Pros
      • Relatively simple (conceptually, at least)
    • Cons
      • Can result in an id being reused if the newest Customer is deleted
      • Need to handle concurrent inserts calculating the same id for the new records
  2. Use a sequence

    • Pros
      • Specifically designed to handle incrementing the value
      • Remembers the last-used value, even if the corresponding record is deleted
    • Cons
      • Requires a separate sequence to be created for each new Account somehow (and then be deleted if/when the Account is deleted), otherwise each Account would end up with non-contiguous values in its Customer id sequence

UPDATE

To clarify the last point, some gaps in the sequence of ids are always going to occur; as soon as a Customer is deleted, for example, but those are to be expected. Aside from that though, a user adding a couple of new Customers would generally expect them to have consecutive ids even if they were added days apart.

OTOH, using a single sequence for all Customers could almost certainly see regular, unexplained gaps introduced by the ids assigned to the Customers created for other Accounts in the meantime.

I'm not a Postgres expert, so I'm concerned about how robustly I could implement either of these, especially as I've struggled to find any examples to follow.

Is there a generally accepted way of doing this?

  • 1
    Let's add to 2/cons that seauences are not designed to produce contiguous numbers. With a rolled back transaction, the number issued will not reappear (unless you reset the sequence, but then the problem in 1/cons/1 comes up). – dezso Feb 19 '15 at 10:57
  • @dezso Still, we could "lower" the specifications and do not mind the occasional gap in the sequence. It would be nice if there was a simple way to implement this (a "multiple" (per account) sequence.) – ypercubeᵀᴹ Feb 19 '15 at 11:16
  • Ah, good point, @dezso - that needs clarifying (see updated question). – David G Feb 19 '15 at 17:13
3

How about using a column in the table account called max_customer_id and updating that every time there's a new customer? That should be the simplest solution available that I can think of.

With this solution you have to take care of the concurrency. Take a look at the document PostgreSQL Concurrency issues:

If two concurrent transactions try to insert rows having the same key value, then the second one will block until the first one finishes. If the first transaction commits, the second one must abort because of the uniqueness constraint; but if the first one aborts the second one can proceed.

If you want to assign a serial number to each row in a table, you might try
INSERT INTO mytable (id, ...) VALUES( (SELECT MAX(id) + 1 FROM mytable), ...);
This will not work safely unless you take an explicit lock on the whole table, which will prevent concurrent insertions. (It’ll also be quite slow, because MAX scans the whole table in PostgreSQL.)

Alternatively you could place the value in a separate table. This table would contain the account_id and maximum account_specific_id in use for that account. That way you could handle the concurrency easier with UPDATEs. Same source:

A variant is to use a single-row table to hold the next ID number to assign:
SELECT next FROM mytable counter FOR UPDATE; UPDATE mytable counter SET next = $next + 1; INSERT INTO mytable (id, ...) VALUES($next, ...);
This works (as long as you use FOR UPDATE), but you still have the problem that only one insertion transaction can proceed at a time. The implicit write lock on the counter row is the bottleneck.
[The author goes on recommending sequences.]

The number 2 option with sequences is not however recommended in this case as new sequences are not meant to be routinely inserted in a production environment.

  • A reasonable, pragmatic approach. I see the need to use a transaction to ensure that the max_customer_id value and the new customer record are treated as an atomic operation. But would the transaction also deal with concurrency? Unless the max_customer_id value is locked - implicitly by transaction or explicitly by the query? - there's always a risk that two concurrent updates would both read the original value and then set it to the same incremented value and add their customer. The second transaction may then fail if there's a unique constraint on the customer id, but that's not ideal. – David G Feb 26 '15 at 23:27
  • Ok, added some info about concurrency. – Simo Kivistö Feb 27 '15 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.