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

On Thu, Jul 2, 2009 at 1:04 AM, Merrick<merrick(at)gmail(dot)com> wrote:
> 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
> customer, so am looking for alternate ways to accomplish the same
> thing.

You could have a last_order_num in the customer's main record and when you
issue a new order do something like

UPDATE customer
SET last_order_num = last_order_num+1
WHERE customer_id = :1
RETURNING last_order_num

Then use that value in a subsequent insert -- preferrably in the same
transaction so if it rolls back you restore the old last_order_num.

You should realize this will lock the customer record so if there are
other queries in the transaction which use the customer record you have to
be careful about deadlock risks. The other downside is it could create
a lot of update traffic on the customer table so it could require a lot of
careful vacuuming there.

--
greg
http://mit.edu/~gsstark/resume.pdf