[ensembl-dev] Experience running Ensembl on PostgreSQL?

[William Spooner]

Hi Kiran, Reece,

Back in the day (five years or more ago), Gramene successfully ran Ensembl on Oracle. Ongoing maintenance, however, was not feasible, and Gramene eventually reverted to MySQL. Things may well have improved, but there's still sure to be MySQL-specific SQL littered throughout the code, as Kiran notes. More significant than that IMHO is the web/registry/adaptor database configuration, which is certainly MySQL specific.

Will

On 9 Mar 2011, at 23:45, Kiran Mukhyala wrote:

> 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
> 
> _______________________________________________
> Dev mailing list
> Dev at ensembl.org
> http://lists.ensembl.org/mailman/listinfo/dev

--
William Spooner
whs at eaglegenomics.com
http://www.eaglegenomics.com