Re: What is the safest way to get last insert ID of a serial column ?

> I have read the FAQ's in the users lounge but I am still confused as
> to how to get the last insert ID from a serial column.
> As an example, say I have the following code (its in Perl)...
>
> ... do an insert into the order table.... then ....
>
> $last_ID = $dbh->selectcol_arrayref("select order_id from order where
> name='$data{'name'}'");
> $last_ID = pop @$last_ID;
>
> This will give me the ID of the LAST order in the orders table which
> matches with the "name" given to the order.
> The problem is, this is part of a transaction, so I dont think the
> above code is safe.
> In the above example, the user creates an order (for which they
> provide a name - free text).
>
> If user-1 starts a transaction, and so does user-2, if both users have
> reached the insert part of the code, the last id assigned will be that
> of user-2... I want the last insert ID of the user whom is carrying
> out this transaction.
>
> I hope this makes sense.
> To summarise, I am looking for a SAFE method of getting the last
> insert ID of a serial field, whilest working with transactions.
>

You need to use sequences, and both nextval() and currval()

My perl is real rusty, so I can't help you there.

Basically, you need to create a sequence ('seq1'), then
INSERT INTO order VALUES (nextval('seq1'));
INSERT INTO other VALUES (currval('seq1'));

Those two inserts will insert the same value.

nextval and currval() will never give the same number to
two separate connections.