Re: 1 Sequence per Row i.e. each customer's first order starts at 1

On Wed, Jul 1, 2009 at 6:04 PM, Merrick<merrick@gmail.com> wrote:
> I have been using postgresql for 8 years in web projects and ran into
> a problem that I could not find a solution for in the archives or
> through Google.
>
> Here is a generalized example of what I want to happen. I have a
> customers table, and an orders table. I would like for each customer
> to have orders that start at 1 and move up sequentially. I realize
> it's probably not efficient to create a new sequence for each

Yeah, plus sequences aren't guaranteed to always give a gapless
sequence due to rollbacks etc.

> customer, so am looking for alternate ways to accomplish the same
> thing. Below is an illustrated example of the outcome I would like. I
> would also like similar functionality to a sequence so duplicate
> order_id's are not generated. Please keep in mind that for what I am
> developing, having each customer's orders start at 1 is more of a need
> than a want.

The simplest method is to do something like:

begin;
select * from sometable where cust_id=99 order by order_id desc for update;

to lock all the customer records for cust_id 99, then take the first
record, which should have the highest order_id, grab that increment it
and then insert the new record  and commit; the transaction.  Assuming
your customers aren't ordering dozens of things a second, this should
work with minimal locking contention.