Re: Better to use a Sequence?

From: Alan Gutierrez <ajglist_at_izzy.net>
Date: Wed, 08 Jan 2003 15:50:33 GMT
Message-ID: <dNXS9.12576$t06.351357_at_news2.east.cox.net>

Alan Gutierrez wrote:
> Given this hackeneyed example:
>
> create table customer_order (order_id integer, primary key (order_id));
> create table customer_order_item (
> order_id integer, order_item_id integer,
> primary key (order_id, order_item_id),
> foreign key (order_id) references (order)
> );
>
> insert into customer_order_item (order_id, order_item_id)
> select 1, max (coalesce(order_item_id, 0)) + 1
> from customer_order_item
> where order_id = 1;
>
> I found that in a transaction a data race will cause the looser of the race
> to abort and rollback.

Found a solution from this article.

http://groups.google.com/groups?th=f13020586061bcfb

This two step procedure works:

insert into customer_order_item (order_id, order_item_id) values (1, 0) \g
update customer_order_item

    set order_item_id = (select max(order_item_id) + 1

                           from cusomter_order_item as coi
                          where coi.order_id = order_id)

  where order_id = 1 and order_item_id = 0 \g

The insert prevents a rollback when two transactions attempt to insert a new order item for the same order.

Maybe if someone searches Google on sequence versus select max, unique identifier, autoincrement, increment, etc. this will save them some time. I had to dig through a lot of religious arguments about seqeunces to find it.

Alan Gutierrez Received on Wed Jan 08 2003 - 16:50:33 CET