btree_gist in postgres 8.4.3

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

btree_gist in postgres 8.4.3

kkarra@stanford.edu
Hi,

I have always had trouble running postprocess "create-bioseg-location-index" (don't have the exact error handy). 

Modifying the SQL in BiosegIndexTask.java to remove locatedonid let me get past the error and that is what I have been doing thus far. Overlap and flanking queries have been working and so, I thought that it was fine. 

String indexSql = "CREATE INDEX location_object_bioseg ON location "
            + "USING gist (bioseg_create(intermine_start, intermine_end))"; //locatedonid,

But, in 0.97, spanUpload using 'Regions' Tab is not returning any results. In looking at the logs, it seems to me that not creating the right index could be the problem..?
I think I followed the instructions for Bioseg and gist intstallation. I may not have done it right. I am wondering if someone can help me figure out as to how I can check that. 


Here is what I get now...

psql (8.4.3)
Type "help" for help.

***unmodified SQL***

yeastmine=#  create index location_object_bioseg_test ON location USING gist(locatedonid, bioseg_create(intermine_start, intermine_end));
ERROR:  data type integer has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

*** SQL I have been using ***

yeastmine=# create index location_object_bioseg_test ON location USING gist(bioseg_create(intermine_start, intermine_end));
CREATE INDEX
yeastmine=# 


If I try to install btree_gist.sql now, I get a ton of errors..

[scone:postgresql-8.4.3/contrib/btree_gist] postgresql% psql template1 < btree_gist.sql 
SET
NOTICE:  type "gbtreekey4" is not yet defined
DETAIL:  Creating a shell type definition.
ERROR:  could not access file "$libdir/btree_gist": No such file or directory
ERROR:  type gbtreekey4 does not exist
ERROR:  function gbtreekey4_in(cstring) does not exist
NOTICE:  type "gbtreekey8" is not yet defined
DETAIL:  Creating a shell type definition.
ERROR:  could not access file "$libdir/btree_gist": No such file or directory
ERROR:  type gbtreekey8 does not exist


Thanks,
-Kalpana



_______________________________________________
dev mailing list
[hidden email]
http://mail.intermine.org/cgi-bin/mailman/listinfo/dev
Reply | Threaded
Open this post in threaded view
|

Re: btree_gist in postgres 8.4.3

Richard Smith
It looks like installing gist is the problem.  When OS are you using?

On debian you should do (you may need root or sudo):

apt-get install postgresql-contrib
cd /usr/share/postgresql/8.4/contrib/
psql template1 <btree_gist.sql

Not that this will only install gist in databases you create from this
point on (template1 is copied when you create a new database).  If you
need to add into an existing database do:

psql my-database <btree_gist.sql


What happens when you do \df in psql?

This should show you the available functions, do you see gbtreekey...?


Richard.





On 05/07/2011 23:24, Kalpana Karra wrote:

> Hi,
>
> I have always had trouble running postprocess "create-bioseg-location-index" (don't have the exact error handy).
>
> Modifying the SQL in BiosegIndexTask.java to remove locatedonid let me get past the error and that is what I have been doing thus far. Overlap and flanking queries have been working and so, I thought that it was fine.
>
> String indexSql = "CREATE INDEX location_object_bioseg ON location "
>              + "USING gist (bioseg_create(intermine_start, intermine_end))"; //locatedonid,
>
> But, in 0.97, spanUpload using 'Regions' Tab is not returning any results. In looking at the logs, it seems to me that not creating the right index could be the problem..?
> I think I followed the instructions for Bioseg and gist intstallation. I may not have done it right. I am wondering if someone can help me figure out as to how I can check that.
>
>
> Here is what I get now...
>
> psql (8.4.3)
> Type "help" for help.
>
> ***unmodified SQL***
>
> yeastmine=#  create index location_object_bioseg_test ON location USING gist(locatedonid, bioseg_create(intermine_start, intermine_end));
> ERROR:  data type integer has no default operator class for access method "gist"
> HINT:  You must specify an operator class for the index or define a default operator class for the data type.
>
> *** SQL I have been using ***
>
> yeastmine=# create index location_object_bioseg_test ON location USING gist(bioseg_create(intermine_start, intermine_end));
> CREATE INDEX
> yeastmine=#
>
>
> If I try to install btree_gist.sql now, I get a ton of errors..
>
> [scone:postgresql-8.4.3/contrib/btree_gist] postgresql% psql template1<  btree_gist.sql
> SET
> NOTICE:  type "gbtreekey4" is not yet defined
> DETAIL:  Creating a shell type definition.
> ERROR:  could not access file "$libdir/btree_gist": No such file or directory
> ERROR:  type gbtreekey4 does not exist
> ERROR:  function gbtreekey4_in(cstring) does not exist
> NOTICE:  type "gbtreekey8" is not yet defined
> DETAIL:  Creating a shell type definition.
> ERROR:  could not access file "$libdir/btree_gist": No such file or directory
> ERROR:  type gbtreekey8 does not exist
>
>
> Thanks,
> -Kalpana
>
>
>
>
>
>
> _______________________________________________
> dev mailing list
> [hidden email]
> http://mail.intermine.org/cgi-bin/mailman/listinfo/dev


_______________________________________________
dev mailing list
[hidden email]
http://mail.intermine.org/cgi-bin/mailman/listinfo/dev