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

I'm sure a trigger could be written to do what I just said.

On Wed, Jul 1, 2009 at 7:46 PM, Merrick<merrick@gmail.com> wrote:
> 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.
>>
>



--
When fascism comes to America, it will be intolerance sold as diversity.