[Open-bio-l] Re: [Bioperl-l] biosql primary key generation

[Hilmar Lapp]

Yeah, I've seen that. I guess you're talking about 
BaseAdaptor::get_last_id($table). That's definitely a possibility 
too.

I have to admit that I have a cultural dislike of the approach to 
get the ID after the insert. I've worked with several professional 
consultants in the past, and just everybody fetched the ID before 
the insert.

Apart from a personal dislike, these are the potential problems I 
can see:
1) The approach relies on the sequence.currval pseudo-column being 
connection-isolated by the RDBMS. I don't know how safe it is to 
assume that's a given, but maybe it is safe. (In Oracle it is true.)
2) It is thread-safe only if different threads use different 
connections.
3) Generally, the client code must retrieve the ID immediately after 
the insert.
4) The approach is hardly if at all amenable to dispensing IDs in 
bulk. (This may or may not be needed to boost performance of large 
uploads.)

None of these is a deal-breaker. OTOH, none of these apply to 
fetching the ID before the insert instead of after.

A compromise could be to structure the code such that both 
approaches are possible, depending on the driver being used and its 
implementation. There's an idea forming in my mind, maybe that's 
what I'll go for.

	-hilmar

On Sunday, August 11, 2002, at 09:23  AM, Chris Mungall wrote:

>
> have a look at the postgres version of the db - there is a script that
> takes every mysql autoincrement and turns it into a sequence, and also
> adds the corresponding CREATE SEQUENCE statement. if you look at the
> bioperl-db code there is a switch up in the BaseAdapter that does the
> Right Thing depending on whichever dbms is being used
>
> On Sun, 11 Aug 2002, Hilmar Lapp wrote:
>
>> I do have triggers in Oracle on primary key fields, that's not the
>> problem. The problem is controlling the primary key value from the
>> /application/ (or library for that matter) such that you can return
>> it to the caller who requested the insert.
>>
>> 	-hilmar
>>
>> On Saturday, August 10, 2002, at 09:42  PM, Todd Richmond wrote:
>>
>>> On 8/10/02 10:23 PM, "Hilmar Lapp" <hlapp@gnf.org> wrote:
>>>
>>>> The problem with the topic is that the MySQL idiosyncratic way of
>>>> generating PKs behind the scenes via AUTO_INCREMENT fields is
>>>> completely incompatible with any other reasonable RDBMS, including
>>>> Postgres and Oracle.
>>>
>>> Is the problem that Oracle and Postgres don't have AUTO_INCREMENT
>>> or that
>>> MySQL doesn't support sequences? Because you can duplicate
>>> AUTO_INCREMENT in
>>> Oracle using a sequence and an insert trigger, so that it's no
>>> different
>>> than MySQL.
>>>
>>> Tpdd
>>>
>>> --
>>> Todd Richmond, PhD
>>> http://cellwall.stanford.edu/todd
>>> email: todd@verdant.stanford.edu
>>>
>>>
>>>
>> --
>> -------------------------------------------------------------
>> Hilmar Lapp                            email: lapp at gnf.org
>> GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
>> -------------------------------------------------------------
>>
>> _______________________________________________
>> Open-Bio-l mailing list
>> Open-Bio-l@open-bio.org
>> http://open-bio.org/mailman/listinfo/open-bio-l
>>
>
>
--
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------