[ensembl-dev] Experience running Ensembl on PostgreSQL?

[Kiran Mukhyala]

Hi Reece,

I have considered using both Postgres and Oracle in the past and here's my 2
cents (someone can correct me if I am wrong) -
Some of the Ensembl API modules construct the SQL statements in a MySQL
specific manner and would require changes to make it work with PostgreSQL.

For example, the table JOIN syntax in the Ensembl API makes use of MySQL's
extension to the SQL standard (list of tables in parentheses), like in
Bio::EnsEMBL::DBSQL::DBEntryAdaptor
SELECT  xref.xref_id,
            xref.dbprimary_acc,
            xref.display_label,
            xref.version,
            exDB.dbprimary_acc_linkable,
            exDB.display_label_linkable,
            exDB.priority,
            exDB.db_name,
            exDB.db_display_name,
            exDB.db_release,
            es.synonym,
            xref.info_type,
            xref.info_text,
            exDB.type,
            exDB.secondary_db_name,
            exDB.secondary_db_table,
            xref.description
    FROM    (xref, external_db exDB)
    LEFT JOIN external_synonym es ON
            es.xref_id = xref.xref_id
    WHERE   xref.xref_id = ?
    AND     xref.external_db_id = exDB.external_db_id

which I think is not supported in PostgreSQL.

This was atleast true in ensembl-59 but the code might have changed since
then.
I have also noticed that the build scripts and utility tools that are part
of Ensembl code have been optimized for MySQL and would require substantial
effort to port to Postgres.

-Kiran


On Wed, Mar 9, 2011 at 1:31 AM, Glenn Proctor <glenn at ebi.ac.uk> wrote:

> We've not done it in-house, but I would be very interested to hear
> what experiences you have with using Postgres as a backend to Ensembl.
>
> Although we use MySQL, I would prefer Ensembl to be as
> database-agnostic as possible, at least at the API level. If there are
> things that we can change to make this easier I'd be happy to consider
> them.
>
> Glenn.
>
>
> On Wed, Mar 9, 2011 at 4:36 AM, Reece Hart <reece at harts.net> wrote:
> > I'm considering trying to run Ensembl on PostgreSQL. Does anyone have
> > experience with this already? Is anyone else interested?
> > Thanks,
> > Reece
> >
> > _______________________________________________
> > Dev mailing list
> > Dev at ensembl.org
> > http://lists.ensembl.org/mailman/listinfo/dev
> >
> >
>
> _______________________________________________
> Dev mailing list
> Dev at ensembl.org
> http://lists.ensembl.org/mailman/listinfo/dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20110309/e7f2c8aa/attachment.html>