[Gmod-tripal-devel] Updating postgres

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[Gmod-tripal-devel] Updating postgres

Mara Kim-2
Hi Stephen,

This is a bit peripheral to what you guys are doing, but I'm stumped and i was wondering if you might have an idea what is going on.  We have updated our database from Postgres 8.4 -> 9.2.  This was done by doing a pg_dump and then restore into the new database.  Things *seem* to be intact, but when I load up a feature page, I get the following (very long) error:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function pg_catalog.substring(text, integer, bigint) does not exist LINE 1: ...max, upstream, downstream, adjfmin, adjfmax, substring(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.: SELECT featureloc_id, srcname, srcfeature_id, strand, srctypename, typename, fmin, fmax, upstream, downstream, adjfmin, adjfmax, substring(residues from (adjfmin + 1) for (upstream + (fmax - fmin) + downstream)) as residues, genus, species FROM ( SELECT FL.featureloc_id, OF.name srcname, FL.srcfeature_id, FL.strand, OCVT.name as srctypename, SCVT.name as typename, FL.fmin, FL.fmax, OO.genus, OO.species, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN 0 ELSE FL.fmin - :upstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmin - :downstream <= 0 THEN 0 ELSE FL.fmin - :downstream END END as adjfmin, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :downstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :upstream END END as adjfmax, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN FL.fmin ELSE :upstream END ELSE CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :upstream END END as upstream, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :downstream END ELSE CASE WHEN FL.fmin - :downstream <= 0 THEN FL.fmin ELSE :downstream END END as downstream, OF.residues FROM chado.featureloc FL INNER JOIN chado.feature SF on FL.feature_id = SF.feature_id INNER JOIN chado.cvterm SCVT on SF.type_id = SCVT.cvterm_id INNER JOIN chado.feature OF on FL.srcfeature_id = OF.feature_id INNER JOIN chado.cvterm OCVT on OF.type_id = OCVT.cvterm_id INNER JOIN chado.organism OO on OF.organism_id = OO.organism_id WHERE SF.feature_id = :feature_id and NOT (OF.residues = '' or OF.residues IS NULL)) as tbl1 ; Array ( [:upstream] => 0 [:downstream] => 0 [:feature_id] => 311333958 ) inchado_query() (line 1321 of /var/www/html/sites/all/modules/tripal/tripal_core/api/tripal_core.chado_query.api.inc).


I have tried `drush cc all`, `drush updatedb` to no effect.

--
Mara Kim

Ph.D. Candidate
Computational Biology
Vanderbilt University
Nashville, TN

------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce.
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel
Reply | Threaded
Open this post in threaded view
|

Re: [Gmod-tripal-devel] Updating postgres

Stephen Ficklin-2
Hi Mara,

It looks like the problem occurs when the SQL statement calls the PostrgeSQL substring() function.  Here's the relevant part of error message:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function pg_catalog.substring(text, integer, bigint) does not exist LINE 1: ...max, upstream, downstream, adjfmin, adjfmax, substring(... ^ HINT: No function matches the given name and argument types.

I think the problem may be the 'bigint' as the third argument to the substring command.  According to the documentation that looks like it should be an 'int'.  Just curious, how large is the reference sequence that the feature is aligned to?

Stephen


On 9/16/2014 4:17 PM, Mara Kim wrote:
Hi Stephen,

This is a bit peripheral to what you guys are doing, but I'm stumped and i was wondering if you might have an idea what is going on.  We have updated our database from Postgres 8.4 -> 9.2.  This was done by doing a pg_dump and then restore into the new database.  Things *seem* to be intact, but when I load up a feature page, I get the following (very long) error:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function pg_catalog.substring(text, integer, bigint) does not exist LINE 1: ...max, upstream, downstream, adjfmin, adjfmax, substring(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.: SELECT featureloc_id, srcname, srcfeature_id, strand, srctypename, typename, fmin, fmax, upstream, downstream, adjfmin, adjfmax, substring(residues from (adjfmin + 1) for (upstream + (fmax - fmin) + downstream)) as residues, genus, species FROM ( SELECT FL.featureloc_id, OF.name srcname, FL.srcfeature_id, FL.strand, OCVT.name as srctypename, SCVT.name as typename, FL.fmin, FL.fmax, OO.genus, OO.species, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN 0 ELSE FL.fmin - :upstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmin - :downstream <= 0 THEN 0 ELSE FL.fmin - :downstream END END as adjfmin, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :downstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :upstream END END as adjfmax, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN FL.fmin ELSE :upstream END ELSE CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :upstream END END as upstream, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :downstream END ELSE CASE WHEN FL.fmin - :downstream <= 0 THEN FL.fmin ELSE :downstream END END as downstream, OF.residues FROM chado.featureloc FL INNER JOIN chado.feature SF on FL.feature_id = SF.feature_id INNER JOIN chado.cvterm SCVT on SF.type_id = SCVT.cvterm_id INNER JOIN chado.feature OF on FL.srcfeature_id = OF.feature_id INNER JOIN chado.cvterm OCVT on OF.type_id = OCVT.cvterm_id INNER JOIN chado.organism OO on OF.organism_id = OO.organism_id WHERE SF.feature_id = :feature_id and NOT (OF.residues = '' or OF.residues IS NULL)) as tbl1 ; Array ( [:upstream] => 0 [:downstream] => 0 [:feature_id] => 311333958 ) inchado_query() (line 1321 of /var/www/html/sites/all/modules/tripal/tripal_core/api/tripal_core.chado_query.api.inc).


I have tried `drush cc all`, `drush updatedb` to no effect.

--
Mara Kim

Ph.D. Candidate
Computational Biology
Vanderbilt University
Nashville, TN


------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce.
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk


_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel


------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce.
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel
Reply | Threaded
Open this post in threaded view
|

Re: [Gmod-tripal-devel] Updating postgres

Mara Kim-2
Hi Stephen,

You were right about the call to substring.  Turns out it was another one of our custom modifications that was going awry, specifically changing the type of seqlen in feature to bigint.  Turns out the fix was as simple as redoing the original UPDATE COLUMN on the feature table.

Thanks for the excellent help, as always! :)

On Tue, Sep 16, 2014 at 3:44 PM, Stephen Ficklin <[hidden email]> wrote:
Hi Mara,

It looks like the problem occurs when the SQL statement calls the PostrgeSQL substring() function.  Here's the relevant part of error message:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function pg_catalog.substring(text, integer, bigint) does not exist LINE 1: ...max, upstream, downstream, adjfmin, adjfmax, substring(... ^ HINT: No function matches the given name and argument types.

I think the problem may be the 'bigint' as the third argument to the substring command.  According to the documentation that looks like it should be an 'int'.  Just curious, how large is the reference sequence that the feature is aligned to?

Stephen



On 9/16/2014 4:17 PM, Mara Kim wrote:
Hi Stephen,

This is a bit peripheral to what you guys are doing, but I'm stumped and i was wondering if you might have an idea what is going on.  We have updated our database from Postgres 8.4 -> 9.2.  This was done by doing a pg_dump and then restore into the new database.  Things *seem* to be intact, but when I load up a feature page, I get the following (very long) error:

PDOException: SQLSTATE[42883]: Undefined function: 7 ERROR: function pg_catalog.substring(text, integer, bigint) does not exist LINE 1: ...max, upstream, downstream, adjfmin, adjfmax, substring(... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.: SELECT featureloc_id, srcname, srcfeature_id, strand, srctypename, typename, fmin, fmax, upstream, downstream, adjfmin, adjfmax, substring(residues from (adjfmin + 1) for (upstream + (fmax - fmin) + downstream)) as residues, genus, species FROM ( SELECT FL.featureloc_id, OF.name srcname, FL.srcfeature_id, FL.strand, OCVT.name as srctypename, SCVT.name as typename, FL.fmin, FL.fmax, OO.genus, OO.species, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN 0 ELSE FL.fmin - :upstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmin - :downstream <= 0 THEN 0 ELSE FL.fmin - :downstream END END as adjfmin, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :downstream END WHEN FL.strand < 0 THEN CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen ELSE FL.fmax + :upstream END END as adjfmax, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmin - :upstream <= 0 THEN FL.fmin ELSE :upstream END ELSE CASE WHEN FL.fmax + :upstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :upstream END END as upstream, CASE WHEN FL.strand >= 0 THEN CASE WHEN FL.fmax + :downstream > OF.seqlen THEN OF.seqlen - FL.fmax ELSE :downstream END ELSE CASE WHEN FL.fmin - :downstream <= 0 THEN FL.fmin ELSE :downstream END END as downstream, OF.residues FROM chado.featureloc FL INNER JOIN chado.feature SF on FL.feature_id = SF.feature_id INNER JOIN chado.cvterm SCVT on SF.type_id = SCVT.cvterm_id INNER JOIN chado.feature OF on FL.srcfeature_id = OF.feature_id INNER JOIN chado.cvterm OCVT on OF.type_id = OCVT.cvterm_id INNER JOIN chado.organism OO on OF.organism_id = OO.organism_id WHERE SF.feature_id = :feature_id and NOT (OF.residues = '' or OF.residues IS NULL)) as tbl1 ; Array ( [:upstream] => 0 [:downstream] => 0 [:feature_id] => 311333958 ) inchado_query() (line 1321 of /var/www/html/sites/all/modules/tripal/tripal_core/api/tripal_core.chado_query.api.inc).


I have tried `drush cc all`, `drush updatedb` to no effect.

--
Mara Kim

Ph.D. Candidate
Computational Biology
Vanderbilt University
Nashville, TN


------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce.
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk


_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel




--
Mara Kim

Ph.D. Candidate
Computational Biology
Vanderbilt University
Nashville, TN

------------------------------------------------------------------------------
Want excitement?
Manually upgrade your production database.
When you want reliability, choose Perforce.
Perforce version control. Predictably reliable.
http://pubads.g.doubleclick.net/gampad/clk?id=157508191&iu=/4140/ostg.clktrk
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel