[Gmod-tripal-devel] Chado functions do not work properly inside the chado.* schema

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

[Gmod-tripal-devel] Chado functions do not work properly inside the chado.* schema

Mara Kim-2
Hello,

The is a problem calling chado functions after importing an external
database using the instructions here:
http://tripal.info/documentation/migrate_chado

To reproduce:
Import chado db using above instructions
Start a psql session, then run the following:
    SELECT * FROM chado.reverse_complement('tata');


BEGIN EXECUTION TRACE

ERROR:  function complement_residues(text) does not exist
LINE 1: SELECT reverse_string(complement_residues($1))
                              ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.
QUERY:  SELECT reverse_string(complement_residues($1))
CONTEXT:  SQL function "reverse_complement" during inlining

END EXECUTION TRACE


The problem seems to be that the internal function call to the
'complement_residues' function is not qualified with the 'chado.*'
schema--or more precisely, that the `search_path` variable does not
contain the chado schema.

The following SQL command fixes this error:
    SET search_path TO "$user",public,chado;


--
Mara Kim

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


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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] Chado functions do not work properly inside the chado.* schema

Mara Kim-2
So the previous fix is only a temporary solution.
A persistent solution requires changing the following line in postgres.conf:
search_path = '"$user",public'

to read as follows:
search_path = '"$user",public,chado'

The line may be commented out.

Then restart Postgres

On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]> wrote:

> Hello,
>
> The is a problem calling chado functions after importing an external
> database using the instructions here:
> http://tripal.info/documentation/migrate_chado
>
> To reproduce:
> Import chado db using above instructions
> Start a psql session, then run the following:
>     SELECT * FROM chado.reverse_complement('tata');
>
>
> BEGIN EXECUTION TRACE
>
> ERROR:  function complement_residues(text) does not exist
> LINE 1: SELECT reverse_string(complement_residues($1))
>                               ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY:  SELECT reverse_string(complement_residues($1))
> CONTEXT:  SQL function "reverse_complement" during inlining
>
> END EXECUTION TRACE
>
>
> The problem seems to be that the internal function call to the
> 'complement_residues' function is not qualified with the 'chado.*'
> schema--or more precisely, that the `search_path` variable does not
> contain the chado schema.
>
> The following SQL command fixes this error:
>     SET search_path TO "$user",public,chado;
>
>
> --
> Mara Kim
>
> Ph.D. Candidate
> Computational Biology
> Vanderbilt University
> Nashville, TN



--
Mara Kim

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


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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] Chado functions do not work properly inside the chado.* schema

Mara Kim-2
A better version of this fix that doesn't involve messing with postgres.conf is:
    ALTER DATABASE drupal SET search_path = "$user",public,chado;

Assuming that 'drupal' is the database hosting tripal/chado.  This
also has the benefit of only changing the settings for the Tripal
database.

On Tue, Apr 29, 2014 at 1:53 PM, Mara Kim <[hidden email]> wrote:

> So the previous fix is only a temporary solution.
> A persistent solution requires changing the following line in postgres.conf:
> search_path = '"$user",public'
>
> to read as follows:
> search_path = '"$user",public,chado'
>
> The line may be commented out.
>
> Then restart Postgres
>
> On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]> wrote:
>> Hello,
>>
>> The is a problem calling chado functions after importing an external
>> database using the instructions here:
>> http://tripal.info/documentation/migrate_chado
>>
>> To reproduce:
>> Import chado db using above instructions
>> Start a psql session, then run the following:
>>     SELECT * FROM chado.reverse_complement('tata');
>>
>>
>> BEGIN EXECUTION TRACE
>>
>> ERROR:  function complement_residues(text) does not exist
>> LINE 1: SELECT reverse_string(complement_residues($1))
>>                               ^
>> HINT:  No function matches the given name and argument types. You
>> might need to add explicit type casts.
>> QUERY:  SELECT reverse_string(complement_residues($1))
>> CONTEXT:  SQL function "reverse_complement" during inlining
>>
>> END EXECUTION TRACE
>>
>>
>> The problem seems to be that the internal function call to the
>> 'complement_residues' function is not qualified with the 'chado.*'
>> schema--or more precisely, that the `search_path` variable does not
>> contain the chado schema.
>>
>> The following SQL command fixes this error:
>>     SET search_path TO "$user",public,chado;
>>
>>
>> --
>> Mara Kim
>>
>> Ph.D. Candidate
>> Computational Biology
>> Vanderbilt University
>> Nashville, TN
>
>
>
> --
> Mara Kim
>
> Ph.D. Candidate
> Computational Biology
> Vanderbilt University
> Nashville, TN



--
Mara Kim

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


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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-tripal] Chado functions do not work properly inside the chado.* schema

Stephen Ficklin-2
Hi Mara,

I would recommend to avoid setting a search_path for the Drupal user or
the Drupal database if you don't need to.  It can cause issues in the
event that two tables have the same name (e.g. contact in Drupal and
contact in Chado) and can cause side-effects for others if you develop
custom code.... I speak from experience :-)  It's easy to accidentally
forget to prefix a table or fail to use the proper Tripal API call that
handles the issue.  The code may work because it can find the table in
the path, but others may have errors.

Stephen

On 4/29/2014 4:17 PM, Mara Kim wrote:

> A better version of this fix that doesn't involve messing with postgres.conf is:
>      ALTER DATABASE drupal SET search_path = "$user",public,chado;
>
> Assuming that 'drupal' is the database hosting tripal/chado.  This
> also has the benefit of only changing the settings for the Tripal
> database.
>
> On Tue, Apr 29, 2014 at 1:53 PM, Mara Kim <[hidden email]> wrote:
>> So the previous fix is only a temporary solution.
>> A persistent solution requires changing the following line in postgres.conf:
>> search_path = '"$user",public'
>>
>> to read as follows:
>> search_path = '"$user",public,chado'
>>
>> The line may be commented out.
>>
>> Then restart Postgres
>>
>> On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]> wrote:
>>> Hello,
>>>
>>> The is a problem calling chado functions after importing an external
>>> database using the instructions here:
>>> http://tripal.info/documentation/migrate_chado
>>>
>>> To reproduce:
>>> Import chado db using above instructions
>>> Start a psql session, then run the following:
>>>      SELECT * FROM chado.reverse_complement('tata');
>>>
>>>
>>> BEGIN EXECUTION TRACE
>>>
>>> ERROR:  function complement_residues(text) does not exist
>>> LINE 1: SELECT reverse_string(complement_residues($1))
>>>                                ^
>>> HINT:  No function matches the given name and argument types. You
>>> might need to add explicit type casts.
>>> QUERY:  SELECT reverse_string(complement_residues($1))
>>> CONTEXT:  SQL function "reverse_complement" during inlining
>>>
>>> END EXECUTION TRACE
>>>
>>>
>>> The problem seems to be that the internal function call to the
>>> 'complement_residues' function is not qualified with the 'chado.*'
>>> schema--or more precisely, that the `search_path` variable does not
>>> contain the chado schema.
>>>
>>> The following SQL command fixes this error:
>>>      SET search_path TO "$user",public,chado;
>>>
>>>
>>> --
>>> Mara Kim
>>>
>>> Ph.D. Candidate
>>> Computational Biology
>>> Vanderbilt University
>>> Nashville, TN
>>
>>
>> --
>> Mara Kim
>>
>> Ph.D. Candidate
>> Computational Biology
>> Vanderbilt University
>> Nashville, TN
>
>


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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-tripal] Chado functions do not work properly inside the chado.* schema

Mara Kim-2
I see.  Is there a better way to solve this problem?

On Tue, Apr 29, 2014 at 3:27 PM, Stephen Ficklin <[hidden email]> wrote:

> Hi Mara,
>
> I would recommend to avoid setting a search_path for the Drupal user or the
> Drupal database if you don't need to.  It can cause issues in the event that
> two tables have the same name (e.g. contact in Drupal and contact in Chado)
> and can cause side-effects for others if you develop custom code.... I speak
> from experience :-)  It's easy to accidentally forget to prefix a table or
> fail to use the proper Tripal API call that handles the issue.  The code may
> work because it can find the table in the path, but others may have errors.
>
> Stephen
>
>
> On 4/29/2014 4:17 PM, Mara Kim wrote:
>>
>> A better version of this fix that doesn't involve messing with
>> postgres.conf is:
>>      ALTER DATABASE drupal SET search_path = "$user",public,chado;
>>
>> Assuming that 'drupal' is the database hosting tripal/chado.  This
>> also has the benefit of only changing the settings for the Tripal
>> database.
>>
>> On Tue, Apr 29, 2014 at 1:53 PM, Mara Kim <[hidden email]> wrote:
>>>
>>> So the previous fix is only a temporary solution.
>>> A persistent solution requires changing the following line in
>>> postgres.conf:
>>> search_path = '"$user",public'
>>>
>>> to read as follows:
>>> search_path = '"$user",public,chado'
>>>
>>> The line may be commented out.
>>>
>>> Then restart Postgres
>>>
>>> On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]>
>>> wrote:
>>>>
>>>> Hello,
>>>>
>>>> The is a problem calling chado functions after importing an external
>>>> database using the instructions here:
>>>> http://tripal.info/documentation/migrate_chado
>>>>
>>>> To reproduce:
>>>> Import chado db using above instructions
>>>> Start a psql session, then run the following:
>>>>      SELECT * FROM chado.reverse_complement('tata');
>>>>
>>>>
>>>> BEGIN EXECUTION TRACE
>>>>
>>>> ERROR:  function complement_residues(text) does not exist
>>>> LINE 1: SELECT reverse_string(complement_residues($1))
>>>>                                ^
>>>> HINT:  No function matches the given name and argument types. You
>>>> might need to add explicit type casts.
>>>> QUERY:  SELECT reverse_string(complement_residues($1))
>>>> CONTEXT:  SQL function "reverse_complement" during inlining
>>>>
>>>> END EXECUTION TRACE
>>>>
>>>>
>>>> The problem seems to be that the internal function call to the
>>>> 'complement_residues' function is not qualified with the 'chado.*'
>>>> schema--or more precisely, that the `search_path` variable does not
>>>> contain the chado schema.
>>>>
>>>> The following SQL command fixes this error:
>>>>      SET search_path TO "$user",public,chado;
>>>>
>>>>
>>>> --
>>>> Mara Kim
>>>>
>>>> Ph.D. Candidate
>>>> Computational Biology
>>>> Vanderbilt University
>>>> Nashville, TN
>>>
>>>
>>>
>>> --
>>> Mara Kim
>>>
>>> Ph.D. Candidate
>>> Computational Biology
>>> Vanderbilt University
>>> Nashville, TN
>>
>>
>>
>
>



--
Mara Kim

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


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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-tripal] Chado functions do not work properly inside the chado.* schema

Stephen Ficklin-2
Are you trying to access Chado tables via psql, phppgadmin or some other
application?   If so, I think the best recommendation would be to create
a new user account in PostgreSQL for that application or user.  I think
that's good protocol anyway so you can set permissions for each
user/application and disable accounts easily when needed.  Then set the
search_path for the specific user.   You can do that with this command:

ALTER USER [username] SET search_path TO public,chado

or you can swap the order of the schemas if you need or just leave
'chado' if you don't really need the Drupal tables

For Tripal you can leave the Drupal user's search_path as the default,
which is just 'public'.  Tripal sorts out the proper tables to use so it
doesn't need a search_path setting.

Stephen



On 4/29/2014 4:28 PM, Mara Kim wrote:

> I see.  Is there a better way to solve this problem?
>
> On Tue, Apr 29, 2014 at 3:27 PM, Stephen Ficklin <[hidden email]> wrote:
>> Hi Mara,
>>
>> I would recommend to avoid setting a search_path for the Drupal user or the
>> Drupal database if you don't need to.  It can cause issues in the event that
>> two tables have the same name (e.g. contact in Drupal and contact in Chado)
>> and can cause side-effects for others if you develop custom code.... I speak
>> from experience :-)  It's easy to accidentally forget to prefix a table or
>> fail to use the proper Tripal API call that handles the issue.  The code may
>> work because it can find the table in the path, but others may have errors.
>>
>> Stephen
>>
>>
>> On 4/29/2014 4:17 PM, Mara Kim wrote:
>>> A better version of this fix that doesn't involve messing with
>>> postgres.conf is:
>>>       ALTER DATABASE drupal SET search_path = "$user",public,chado;
>>>
>>> Assuming that 'drupal' is the database hosting tripal/chado.  This
>>> also has the benefit of only changing the settings for the Tripal
>>> database.
>>>
>>> On Tue, Apr 29, 2014 at 1:53 PM, Mara Kim <[hidden email]> wrote:
>>>> So the previous fix is only a temporary solution.
>>>> A persistent solution requires changing the following line in
>>>> postgres.conf:
>>>> search_path = '"$user",public'
>>>>
>>>> to read as follows:
>>>> search_path = '"$user",public,chado'
>>>>
>>>> The line may be commented out.
>>>>
>>>> Then restart Postgres
>>>>
>>>> On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]>
>>>> wrote:
>>>>> Hello,
>>>>>
>>>>> The is a problem calling chado functions after importing an external
>>>>> database using the instructions here:
>>>>> http://tripal.info/documentation/migrate_chado
>>>>>
>>>>> To reproduce:
>>>>> Import chado db using above instructions
>>>>> Start a psql session, then run the following:
>>>>>       SELECT * FROM chado.reverse_complement('tata');
>>>>>
>>>>>
>>>>> BEGIN EXECUTION TRACE
>>>>>
>>>>> ERROR:  function complement_residues(text) does not exist
>>>>> LINE 1: SELECT reverse_string(complement_residues($1))
>>>>>                                 ^
>>>>> HINT:  No function matches the given name and argument types. You
>>>>> might need to add explicit type casts.
>>>>> QUERY:  SELECT reverse_string(complement_residues($1))
>>>>> CONTEXT:  SQL function "reverse_complement" during inlining
>>>>>
>>>>> END EXECUTION TRACE
>>>>>
>>>>>
>>>>> The problem seems to be that the internal function call to the
>>>>> 'complement_residues' function is not qualified with the 'chado.*'
>>>>> schema--or more precisely, that the `search_path` variable does not
>>>>> contain the chado schema.
>>>>>
>>>>> The following SQL command fixes this error:
>>>>>       SET search_path TO "$user",public,chado;
>>>>>
>>>>>
>>>>> --
>>>>> Mara Kim
>>>>>
>>>>> Ph.D. Candidate
>>>>> Computational Biology
>>>>> Vanderbilt University
>>>>> Nashville, TN
>>>>
>>>>
>>>> --
>>>> Mara Kim
>>>>
>>>> Ph.D. Candidate
>>>> Computational Biology
>>>> Vanderbilt University
>>>> Nashville, TN
>>>
>>>
>>
>
>


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
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-tripal] Chado functions do not work properly inside the chado.* schema

Mara Kim-2
Ah that makes sense.  I already have separate roles for all my
applications so this should be an easy fix.  Thanks!

On Tue, Apr 29, 2014 at 3:34 PM, Stephen Ficklin <[hidden email]> wrote:

> Are you trying to access Chado tables via psql, phppgadmin or some other
> application?   If so, I think the best recommendation would be to create a
> new user account in PostgreSQL for that application or user.  I think that's
> good protocol anyway so you can set permissions for each user/application
> and disable accounts easily when needed.  Then set the search_path for the
> specific user.   You can do that with this command:
>
> ALTER USER [username] SET search_path TO public,chado
>
> or you can swap the order of the schemas if you need or just leave 'chado'
> if you don't really need the Drupal tables
>
> For Tripal you can leave the Drupal user's search_path as the default, which
> is just 'public'.  Tripal sorts out the proper tables to use so it doesn't
> need a search_path setting.
>
> Stephen
>
>
>
>
> On 4/29/2014 4:28 PM, Mara Kim wrote:
>>
>> I see.  Is there a better way to solve this problem?
>>
>> On Tue, Apr 29, 2014 at 3:27 PM, Stephen Ficklin <[hidden email]>
>> wrote:
>>>
>>> Hi Mara,
>>>
>>> I would recommend to avoid setting a search_path for the Drupal user or
>>> the
>>> Drupal database if you don't need to.  It can cause issues in the event
>>> that
>>> two tables have the same name (e.g. contact in Drupal and contact in
>>> Chado)
>>> and can cause side-effects for others if you develop custom code.... I
>>> speak
>>> from experience :-)  It's easy to accidentally forget to prefix a table
>>> or
>>> fail to use the proper Tripal API call that handles the issue.  The code
>>> may
>>> work because it can find the table in the path, but others may have
>>> errors.
>>>
>>> Stephen
>>>
>>>
>>> On 4/29/2014 4:17 PM, Mara Kim wrote:
>>>>
>>>> A better version of this fix that doesn't involve messing with
>>>> postgres.conf is:
>>>>       ALTER DATABASE drupal SET search_path = "$user",public,chado;
>>>>
>>>> Assuming that 'drupal' is the database hosting tripal/chado.  This
>>>> also has the benefit of only changing the settings for the Tripal
>>>> database.
>>>>
>>>> On Tue, Apr 29, 2014 at 1:53 PM, Mara Kim <[hidden email]>
>>>> wrote:
>>>>>
>>>>> So the previous fix is only a temporary solution.
>>>>> A persistent solution requires changing the following line in
>>>>> postgres.conf:
>>>>> search_path = '"$user",public'
>>>>>
>>>>> to read as follows:
>>>>> search_path = '"$user",public,chado'
>>>>>
>>>>> The line may be commented out.
>>>>>
>>>>> Then restart Postgres
>>>>>
>>>>> On Tue, Apr 29, 2014 at 1:43 PM, Mara Kim <[hidden email]>
>>>>> wrote:
>>>>>>
>>>>>> Hello,
>>>>>>
>>>>>> The is a problem calling chado functions after importing an external
>>>>>> database using the instructions here:
>>>>>> http://tripal.info/documentation/migrate_chado
>>>>>>
>>>>>> To reproduce:
>>>>>> Import chado db using above instructions
>>>>>> Start a psql session, then run the following:
>>>>>>       SELECT * FROM chado.reverse_complement('tata');
>>>>>>
>>>>>>
>>>>>> BEGIN EXECUTION TRACE
>>>>>>
>>>>>> ERROR:  function complement_residues(text) does not exist
>>>>>> LINE 1: SELECT reverse_string(complement_residues($1))
>>>>>>                                 ^
>>>>>> HINT:  No function matches the given name and argument types. You
>>>>>> might need to add explicit type casts.
>>>>>> QUERY:  SELECT reverse_string(complement_residues($1))
>>>>>> CONTEXT:  SQL function "reverse_complement" during inlining
>>>>>>
>>>>>> END EXECUTION TRACE
>>>>>>
>>>>>>
>>>>>> The problem seems to be that the internal function call to the
>>>>>> 'complement_residues' function is not qualified with the 'chado.*'
>>>>>> schema--or more precisely, that the `search_path` variable does not
>>>>>> contain the chado schema.
>>>>>>
>>>>>> The following SQL command fixes this error:
>>>>>>       SET search_path TO "$user",public,chado;
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Mara Kim
>>>>>>
>>>>>> Ph.D. Candidate
>>>>>> Computational Biology
>>>>>> Vanderbilt University
>>>>>> Nashville, TN
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Mara Kim
>>>>>
>>>>> Ph.D. Candidate
>>>>> Computational Biology
>>>>> Vanderbilt University
>>>>> Nashville, TN
>>>>
>>>>
>>>>
>>>
>>
>>
>
>



--
Mara Kim

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


------------------------------------------------------------------------------
"Accelerate Dev Cycles with Automated Cross-Browser Testing - For FREE
Instantly run your Selenium tests across 300+ browser/OS combos.  Get
unparalleled scalability from the best Selenium testing platform available.
Simple to use. Nothing to install. Get started now for free."
http://p.sf.net/sfu/SauceLabs
_______________________________________________
Gmod-tripal-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-tripal-devel