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@xxxxxxxxx> 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@xxxxxxxxx> wrote: >> On Wed, Jul 1, 2009 at 6:04 PM, Merrick<merrick@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
