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

I was hoping there would be a way to add a field the sequence table
postgresql automatically generates so I could rely on whatever
mechanism postgresql uses to avoid the problems described thus far.

I should have included more info, it's highly likely that multiple
users will be accessing using same customer_id when creating orders
thus deadlocks would be an issue I would like to avoid.

Having the sequence be gapless would not be a requirement.

Thank you.

Merrick

On Wed, Jul 1, 2009 at 6:01 PM, Scott Marlowe<scott.marlowe@gmail.com> wrote:
> 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.
>