[BioSQL-l] [Open-bio-l] FW: Postgres version support

[Hilmar Lapp]

I'm resending this on Chris' behalf as his original was bounced by the OBDA mail agent.

(and outlook fooled me into believing I was sending to biosql-l ...)

-----Original Message-----
From: Chris Mungall [mailto:cjm@fruitfly.org]
Sent: Monday, October 14, 2002 1:50 PM
To: Hilmar Lapp
Cc: Biosql; Thomas Down
Subject: Re: Postgres version support



I think the default is not to do cascading deletes. we should probably
switch them on. it's useful not having them temporarily as it helps test
all that pointless adapter code that exists purely for the benefit of
mysql.

i'm not sure about the performance overhead. i guess you could just remove
all FKs during the bulkload. i wouldn't bother trying to hand-combine
these into your own trigger, there are more fruitful ways of spending your
time getting better performance from pg. though you could try asking on
the pg-admin mail list, they are all very helpful there, and know the guts
of pg inside out.

i'm really happy with the performance i get from pg (this is with the
whole human assembly and refseq loaded). a default pg installation is
usually pretty slow, but once you tinker with the parameters, run your
vacuum analyzes etc it gets super fast.

there is only one unsatisfactory aspect - the generic property pattern
used in biosql means all property values are stored as TEXT. this turns
out to be pretty slow for indexing. i got round this by materialising a
view that casts the text to a varchar - i used this view when doing stuff
like searching by gene name, but use the base table for getting the longer
property values (eg /note fields and whatnot)

On Mon, 14 Oct 2002, Hilmar Lapp wrote:

> I updated and fixed the transform_sql.pl script for generating the
> Postgres version from the InnoDB version of the schema with FK
> constraints. The result seems to run fine through psql (yeah, I
> finally got this up and running on my laptop this weekend). I
> committed it too.
>
> ChrisM, Thomas, you are regular Postgres users, right? I noticed
> that Postgres creates triggers for enforcing the FK constraints and
> I guess these triggers will also execute the ON DELETE CASCADE
> actions. If I do '\d bioentry' I see a whole bunch of triggers
> showing up supposedly enforcing constraints (bioentry does have a
> number of FK constraints to it):
>
> Triggers: RI_ConstraintTrigger_16829,
>            RI_ConstraintTrigger_16835,
>            RI_ConstraintTrigger_16843,
>            RI_ConstraintTrigger_16845,
>            RI_ConstraintTrigger_16849,
>            RI_ConstraintTrigger_16851,
>            RI_ConstraintTrigger_16855,
>            RI_ConstraintTrigger_16857,
>            RI_ConstraintTrigger_16879,
>            RI_ConstraintTrigger_16881,
>            RI_ConstraintTrigger_16891,
>            RI_ConstraintTrigger_16893,
>            RI_ConstraintTrigger_16915,
>            RI_ConstraintTrigger_16917
>
> Is this known to create a performance problem in Postgres? I was
> wondering whether combining them into one trigger by hand will or
> will not make a significant difference in performance. BTW does
> anyone know off hand how I can view the code of a trigger in psql?
>
> There's no data yet, and the bioperl-db adaptors don't support Pg yet.
>
> 	-h
> --
> -------------------------------------------------------------
> 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