Re: [Gmod-tripal-devel] [Gmod-schema] Problems with featureloc table

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

Re: [Gmod-tripal-devel] [Gmod-schema] Problems with featureloc table

Cannon, Ethalinda K [COM S]
Thanks Ryan. I had a suspicion it had something to do with the schema name but didn't know it was possible in Postgres to set a default schema. We will explore a similar solution to yours.

I'm curious, though, why Tripal doesn't (appear) to set the default schema itself, so I'm posting this on the Tripal mailing list too. Again, this seems pretty fundamental and unlikely to be a bug in Tripal, so I'm wondering what I did wrong during installation.

Ethy


________________________________________
From: Mccormick, Ryan F [[hidden email]]
Sent: Tuesday, May 28, 2013 3:18 PM
To: Ethalinda K Cannon [GDCBA]
Cc: Weeks, Nathan T [ITACD]; Steven Cannon; [hidden email]
Subject: Re: [Gmod-schema] Problems with featureloc table

Ethy,

Regarding the create_point() not existing error, I think I had a similar problem previously. I think it stemmed from the function create_point() being defined in the chado schema within the drupal database, but it's not within the client search path. To get around it, I added the chado schema to the search_path variable in the pgsql config file (under the client connection defaults section); it seemed to resolve the problem. The Tripal/Chado developers may have a more appropriate/elegant fix though.

Ryan McCormick

----- Original Message -----
From: "Ethalinda K Cannon [GDCBA]" <[hidden email]>nn
To: [hidden email]
Cc: "Nathan T Weeks [ITACD]" <[hidden email]>, "Steven Cannon" <[hidden email]>
Sent: Tuesday, 28 May, 2013 2:57:19 PM
Subject: [Gmod-schema] Problems with featureloc table

I don't know if this belongs on the Chado or Tripal mail list:

I'm getting an inexplicable error when attempting to add a record to the featureloc table, inexplicable because inserting a record into featureloc is such a fundamental task that this error can't possibly be due to a bug in the Chado schema or in Tripal. But I can't see what I'm doing wrong. Here's what happens when I try to load coordinates for a chromosome :

drupal=# INSERT INTO chado.featureloc
drupal-#   (feature_id, srcfeature_id, fmin, fmax)
drupal-# VALUES
drupal-#   ((SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
drupal(#    (SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
drupal(#    0, 52205531);
ERROR:  function create_point(integer, integer) does not exist
LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000))
                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000))
CONTEXT:  SQL function "boxrange" during inlining

I get the same error when attempting to SELECT records from featureloc. The Chado schema was loaded via Tripal and the function create_point(integer, integer) does in fact exist.

I tried using the Tripal feature loader (Administer/Tripal Management/Features/Import a GFF3 file) using a GFF file but that failed without reporting errors. Is there an error log lurking somewhere that I haven't found?

Ethy


------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema
------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
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] [Gmod-schema] Problems with featureloc table

Stephen Ficklin-2
Hi Ethy,

By default, postgres always searches the 'public' schema for tables and
will not search other schemas unless explicitly indicated in the SQL
statement (e.g. SELECT * FROM chado.featureloc).  Because Chado is not
in the 'public' schema you will need to set the 'search_path' variable
for the user or session of postgres if you are interacting with Chado
from any other tool (e.g. psql). This document will explain how to do
that: http://tripal.info/node/28.

Also, I read below that the GFF loader did not complete and did not
return errors.   If it does encounter an error it should return a
message both to the command-line and to the Drupal recent logs.  Could
it be that the job timed out in the middle of the load, or a connection
was interrupted pre-empting the job?  Let me know and I'll work with you
on it.

Hope that helps,
Stephen



On 5/29/2013 9:06 AM, Cannon, Ethalinda K [GDCBA] wrote:

> Thanks Ryan. I had a suspicion it had something to do with the schema name but didn't know it was possible in Postgres to set a default schema. We will explore a similar solution to yours.
>
> I'm curious, though, why Tripal doesn't (appear) to set the default schema itself, so I'm posting this on the Tripal mailing list too. Again, this seems pretty fundamental and unlikely to be a bug in Tripal, so I'm wondering what I did wrong during installation.
>
> Ethy
>
>
> ________________________________________
> From: Mccormick, Ryan F [[hidden email]]
> Sent: Tuesday, May 28, 2013 3:18 PM
> To: Ethalinda K Cannon [GDCBA]
> Cc: Weeks, Nathan T [ITACD]; Steven Cannon; [hidden email]
> Subject: Re: [Gmod-schema] Problems with featureloc table
>
> Ethy,
>
> Regarding the create_point() not existing error, I think I had a similar problem previously. I think it stemmed from the function create_point() being defined in the chado schema within the drupal database, but it's not within the client search path. To get around it, I added the chado schema to the search_path variable in the pgsql config file (under the client connection defaults section); it seemed to resolve the problem. The Tripal/Chado developers may have a more appropriate/elegant fix though.
>
> Ryan McCormick
>
> ----- Original Message -----
> From: "Ethalinda K Cannon [GDCBA]" <[hidden email]>nn
> To: [hidden email]
> Cc: "Nathan T Weeks [ITACD]" <[hidden email]>, "Steven Cannon" <[hidden email]>
> Sent: Tuesday, 28 May, 2013 2:57:19 PM
> Subject: [Gmod-schema] Problems with featureloc table
>
> I don't know if this belongs on the Chado or Tripal mail list:
>
> I'm getting an inexplicable error when attempting to add a record to the featureloc table, inexplicable because inserting a record into featureloc is such a fundamental task that this error can't possibly be due to a bug in the Chado schema or in Tripal. But I can't see what I'm doing wrong. Here's what happens when I try to load coordinates for a chromosome :
>
> drupal=# INSERT INTO chado.featureloc
> drupal-#   (feature_id, srcfeature_id, fmin, fmax)
> drupal-# VALUES
> drupal-#   ((SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
> drupal(#    (SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
> drupal(#    0, 52205531);
> ERROR:  function create_point(integer, integer) does not exist
> LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000))
>                      ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000))
> CONTEXT:  SQL function "boxrange" during inlining
>
> I get the same error when attempting to SELECT records from featureloc. The Chado schema was loaded via Tripal and the function create_point(integer, integer) does in fact exist.
>
> I tried using the Tripal feature loader (Administer/Tripal Management/Features/Import a GFF3 file) using a GFF file but that failed without reporting errors. Is there an error log lurking somewhere that I haven't found?
>
> Ethy
>
>
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> Gmod-tripal-devel mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel


------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
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] [Gmod-schema] Problems with featureloc table

Cannon, Ethalinda K [COM S]
Hi Stephen,

I haven't made much progress toward finding an error message. When I try loading the GFF, I get the following output immediately from the job launcher:

**************
Tripal Job Launcher
Running as user ' . admin . '
-------------------
Calling: tripal_feature_load_gff3(/Users/ethalindacannon/Work-legumes/data/gff/phavu_chrs.gff, 24, 10, 0, 1, 0, 0, 1, , , 0, , , , 62)

NOTE: Loading of this GFF file is performed using a database transaction.
If the load fails or is terminated prematurely then the entire set of
insertions/updates is rolled back and will not be found in the database

Opening <...>/gff/phavu_chrs.gff
$
**************


The jobs page reports "Error"' and "Job has terminated unexpectedly." There are no messages on the page, /admin/reports/dblog, in the apache error log, or in the system log (postgres is configure to report errors to the system log) .

Would it help for you to see the file I am trying to load?

Ethy


________________________________________
From: Stephen Ficklin [[hidden email]]
Sent: Wednesday, May 29, 2013 3:20 PM
To: Cannon, Ethalinda K [GDCBA]
Cc: [hidden email]; [hidden email]; Weeks, Nathan T [ITACD]; Steven Cannon
Subject: Re: [Gmod-tripal-devel] [Gmod-schema] Problems with featureloc table

Hi Ethy,

By default, postgres always searches the 'public' schema for tables and
will not search other schemas unless explicitly indicated in the SQL
statement (e.g. SELECT * FROM chado.featureloc).  Because Chado is not
in the 'public' schema you will need to set the 'search_path' variable
for the user or session of postgres if you are interacting with Chado
from any other tool (e.g. psql). This document will explain how to do
that: http://tripal.info/node/28.

Also, I read below that the GFF loader did not complete and did not
return errors.   If it does encounter an error it should return a
message both to the command-line and to the Drupal recent logs.  Could
it be that the job timed out in the middle of the load, or a connection
was interrupted pre-empting the job?  Let me know and I'll work with you
on it.

Hope that helps,
Stephen



On 5/29/2013 9:06 AM, Cannon, Ethalinda K [GDCBA] wrote:

> Thanks Ryan. I had a suspicion it had something to do with the schema name but didn't know it was possible in Postgres to set a default schema. We will explore a similar solution to yours.
>
> I'm curious, though, why Tripal doesn't (appear) to set the default schema itself, so I'm posting this on the Tripal mailing list too. Again, this seems pretty fundamental and unlikely to be a bug in Tripal, so I'm wondering what I did wrong during installation.
>
> Ethy
>
>
> ________________________________________
> From: Mccormick, Ryan F [[hidden email]]
> Sent: Tuesday, May 28, 2013 3:18 PM
> To: Ethalinda K Cannon [GDCBA]
> Cc: Weeks, Nathan T [ITACD]; Steven Cannon; [hidden email]
> Subject: Re: [Gmod-schema] Problems with featureloc table
>
> Ethy,
>
> Regarding the create_point() not existing error, I think I had a similar problem previously. I think it stemmed from the function create_point() being defined in the chado schema within the drupal database, but it's not within the client search path. To get around it, I added the chado schema to the search_path variable in the pgsql config file (under the client connection defaults section); it seemed to resolve the problem. The Tripal/Chado developers may have a more appropriate/elegant fix though.
>
> Ryan McCormick
>
> ----- Original Message -----
> From: "Ethalinda K Cannon [GDCBA]" <[hidden email]>nn
> To: [hidden email]
> Cc: "Nathan T Weeks [ITACD]" <[hidden email]>, "Steven Cannon" <[hidden email]>
> Sent: Tuesday, 28 May, 2013 2:57:19 PM
> Subject: [Gmod-schema] Problems with featureloc table
>
> I don't know if this belongs on the Chado or Tripal mail list:
>
> I'm getting an inexplicable error when attempting to add a record to the featureloc table, inexplicable because inserting a record into featureloc is such a fundamental task that this error can't possibly be due to a bug in the Chado schema or in Tripal. But I can't see what I'm doing wrong. Here's what happens when I try to load coordinates for a chromosome :
>
> drupal=# INSERT INTO chado.featureloc
> drupal-#   (feature_id, srcfeature_id, fmin, fmax)
> drupal-# VALUES
> drupal-#   ((SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
> drupal(#    (SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
> drupal(#    0, 52205531);
> ERROR:  function create_point(integer, integer) does not exist
> LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000))
>                      ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000))
> CONTEXT:  SQL function "boxrange" during inlining
>
> I get the same error when attempting to SELECT records from featureloc. The Chado schema was loaded via Tripal and the function create_point(integer, integer) does in fact exist.
>
> I tried using the Tripal feature loader (Administer/Tripal Management/Features/Import a GFF3 file) using a GFF file but that failed without reporting errors. Is there an error log lurking somewhere that I haven't found?
>
> Ethy
>
>
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> Gmod-schema mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-schema
> ------------------------------------------------------------------------------
> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
> Get 100% visibility into your production application - at no cost.
> Code-level diagnostics for performance bottlenecks with <2% overhead
> Download for free and get started troubleshooting in minutes.
> http://p.sf.net/sfu/appdyn_d2d_ap1
> _______________________________________________
> Gmod-tripal-devel mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel

------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
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] [Gmod-schema] Problems with featureloc table

Stephen Ficklin-2
Hi Ethy,

Yes, if you can share the file I can try loading it at my end to test.  
So, if I understand correctly, you launched the job and then it just
quit and put you back to the command prompt with no output and no
warnings?  Have you been able to load any GFF file before the one you
are trying?

Thanks,
Stephen

On 5/30/2013 10:59 AM, Cannon, Ethalinda K [GDCBA] wrote:

> Hi Stephen,
>
> I haven't made much progress toward finding an error message. When I try loading the GFF, I get the following output immediately from the job launcher:
>
> **************
> Tripal Job Launcher
> Running as user ' . admin . '
> -------------------
> Calling: tripal_feature_load_gff3(/Users/ethalindacannon/Work-legumes/data/gff/phavu_chrs.gff, 24, 10, 0, 1, 0, 0, 1, , , 0, , , , 62)
>
> NOTE: Loading of this GFF file is performed using a database transaction.
> If the load fails or is terminated prematurely then the entire set of
> insertions/updates is rolled back and will not be found in the database
>
> Opening <...>/gff/phavu_chrs.gff
> $
> **************
>
>
> The jobs page reports "Error"' and "Job has terminated unexpectedly." There are no messages on the page, /admin/reports/dblog, in the apache error log, or in the system log (postgres is configure to report errors to the system log) .
>
> Would it help for you to see the file I am trying to load?
>
> Ethy
>
>
> ________________________________________
> From: Stephen Ficklin [[hidden email]]
> Sent: Wednesday, May 29, 2013 3:20 PM
> To: Cannon, Ethalinda K [GDCBA]
> Cc: [hidden email]; [hidden email]; Weeks, Nathan T [ITACD]; Steven Cannon
> Subject: Re: [Gmod-tripal-devel] [Gmod-schema] Problems with featureloc table
>
> Hi Ethy,
>
> By default, postgres always searches the 'public' schema for tables and
> will not search other schemas unless explicitly indicated in the SQL
> statement (e.g. SELECT * FROM chado.featureloc).  Because Chado is not
> in the 'public' schema you will need to set the 'search_path' variable
> for the user or session of postgres if you are interacting with Chado
> from any other tool (e.g. psql). This document will explain how to do
> that: http://tripal.info/node/28.
>
> Also, I read below that the GFF loader did not complete and did not
> return errors.   If it does encounter an error it should return a
> message both to the command-line and to the Drupal recent logs.  Could
> it be that the job timed out in the middle of the load, or a connection
> was interrupted pre-empting the job?  Let me know and I'll work with you
> on it.
>
> Hope that helps,
> Stephen
>
>
>
> On 5/29/2013 9:06 AM, Cannon, Ethalinda K [GDCBA] wrote:
>> Thanks Ryan. I had a suspicion it had something to do with the schema name but didn't know it was possible in Postgres to set a default schema. We will explore a similar solution to yours.
>>
>> I'm curious, though, why Tripal doesn't (appear) to set the default schema itself, so I'm posting this on the Tripal mailing list too. Again, this seems pretty fundamental and unlikely to be a bug in Tripal, so I'm wondering what I did wrong during installation.
>>
>> Ethy
>>
>>
>> ________________________________________
>> From: Mccormick, Ryan F [[hidden email]]
>> Sent: Tuesday, May 28, 2013 3:18 PM
>> To: Ethalinda K Cannon [GDCBA]
>> Cc: Weeks, Nathan T [ITACD]; Steven Cannon; [hidden email]
>> Subject: Re: [Gmod-schema] Problems with featureloc table
>>
>> Ethy,
>>
>> Regarding the create_point() not existing error, I think I had a similar problem previously. I think it stemmed from the function create_point() being defined in the chado schema within the drupal database, but it's not within the client search path. To get around it, I added the chado schema to the search_path variable in the pgsql config file (under the client connection defaults section); it seemed to resolve the problem. The Tripal/Chado developers may have a more appropriate/elegant fix though.
>>
>> Ryan McCormick
>>
>> ----- Original Message -----
>> From: "Ethalinda K Cannon [GDCBA]" <[hidden email]>nn
>> To: [hidden email]
>> Cc: "Nathan T Weeks [ITACD]" <[hidden email]>, "Steven Cannon" <[hidden email]>
>> Sent: Tuesday, 28 May, 2013 2:57:19 PM
>> Subject: [Gmod-schema] Problems with featureloc table
>>
>> I don't know if this belongs on the Chado or Tripal mail list:
>>
>> I'm getting an inexplicable error when attempting to add a record to the featureloc table, inexplicable because inserting a record into featureloc is such a fundamental task that this error can't possibly be due to a bug in the Chado schema or in Tripal. But I can't see what I'm doing wrong. Here's what happens when I try to load coordinates for a chromosome :
>>
>> drupal=# INSERT INTO chado.featureloc
>> drupal-#   (feature_id, srcfeature_id, fmin, fmax)
>> drupal-# VALUES
>> drupal-#   ((SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
>> drupal(#    (SELECT feature_id FROM chado.feature WHERE uniquename='Pv01'),
>> drupal(#    0, 52205531);
>> ERROR:  function create_point(integer, integer) does not exist
>> LINE 1: SELECT box (create_point(0, $1), create_point($2,500000000))
>>                       ^
>> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
>> QUERY:  SELECT box (create_point(0, $1), create_point($2,500000000))
>> CONTEXT:  SQL function "boxrange" during inlining
>>
>> I get the same error when attempting to SELECT records from featureloc. The Chado schema was loaded via Tripal and the function create_point(integer, integer) does in fact exist.
>>
>> I tried using the Tripal feature loader (Administer/Tripal Management/Features/Import a GFF3 file) using a GFF file but that failed without reporting errors. Is there an error log lurking somewhere that I haven't found?
>>
>> Ethy
>>
>>
>> ------------------------------------------------------------------------------
>> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
>> Get 100% visibility into your production application - at no cost.
>> Code-level diagnostics for performance bottlenecks with <2% overhead
>> Download for free and get started troubleshooting in minutes.
>> http://p.sf.net/sfu/appdyn_d2d_ap1
>> _______________________________________________
>> Gmod-schema mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-schema
>> ------------------------------------------------------------------------------
>> Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
>> Get 100% visibility into your production application - at no cost.
>> Code-level diagnostics for performance bottlenecks with <2% overhead
>> Download for free and get started troubleshooting in minutes.
>> http://p.sf.net/sfu/appdyn_d2d_ap1
>> _______________________________________________
>> Gmod-tripal-devel mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel


------------------------------------------------------------------------------
Introducing AppDynamics Lite, a free troubleshooting tool for Java/.NET
Get 100% visibility into your production application - at no cost.
Code-level diagnostics for performance bottlenecks with <2% overhead
Download for free and get started troubleshooting in minutes.
http://p.sf.net/sfu/appdyn_d2d_ap1
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel