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. > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
