[Bioperl-l] Re: [BioSQL-l] PostgreSQL & lookup & insert in bioperl-db

[Hilmar Lapp]

On Wednesday, February 26, 2003, at 09:08  PM, Elia Stupka wrote:

>> 3) Separate the look-up into PL/PgSQL code in the database. Instead 
>> of issuing a direct INSERT you call a stored procedure that looks up 
>> the entry and inserts it if not yet present. The advantage of this 
>> option is that it keeps the bioperl-db layout largely unaffected and 
>> hence treats all RDBMSs relatively equal (i.e., cares least about 
>> RDBMS idiosyncrasies). The downside is that this means that the Pg 
>> version of Biosql can't be fully auto-generated anymore from the 
>> MySQL version (I don't want to auto-generate the PL/PgSQL procedures 
>> from a MySQL schema definition). Also, people say PL/PgSQL is not the 
>> fastest thing out there.
>
> My two cents... is it a big deal not to be able to auto-generate 
> Postgres?

I don't think it is. It has been convenient at the hackathon to bring 
the Pg schema up to speed within half an hour (29 minutes of which was 
fixing the transform script which was incapable of parsing a DECIMAL 
data type :), but given the imminent schema freeze it's really not an 
issue.

>  It seems to be this would be teh cleanest of the three, as long as 
> Postgres people are happy to load things from scratch rather than from 
> MySQL....
>

Well, I forgot to mention another downside: someone has got to learn 
writing PL/PgSQL stored procedures, and I'm afraid that someone is 
going to be me ...

Just as a note, this was actually also the main advice given by the 
Postgres list where I posted the problem a while ago. The other was to 
wait until nested transactions are implemented, which amazingly enough 
is being worked on ... (but not scheduled before 7.4.x)

	-hilmar
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------