[postgis-users] Crash when creating index on ( int, geom )

[Robin Chauhan]

Mark,

I tried duplicating the table and got the same reuslt with the new
table.  Thanks for your instructions on getting a backtrace using gdb.

Before I tried that, I also attempted to install a more updated
version of btree_gist from here:
   http://www.sai.msu.su/~megera/postgres/gist/btree_gist/btree_gist-7.4.tar.gz

...which in the README says it can work on postgres 7.3 and up (I have
7.3.2).  Unfortunately I was not able compile it:

btree_common.c: In function `gbtreekey_in':
btree_common.c:18: warning: implicit declaration of function `ereport'
btree_common.c:19: warning: implicit declaration of function `errcode'
btree_common.c:19: `ERRCODE_FEATURE_NOT_SUPPORTED' undeclared (first
use in this function)
btree_common.c:19: (Each undeclared identifier is reported only once
btree_common.c:19: for each function it appears in.)
btree_common.c:20: warning: implicit declaration of function `errmsg'
btree_common.c: In function `gbtreekey_out':
btree_common.c:29: `ERRCODE_FEATURE_NOT_SUPPORTED' undeclared (first
use in this function)
make: *** [btree_common.o] Error 1

For now I am using the separate indexes as suggested by Markus which
is working very well, but I will try to track this down further.

-Robin



On Fri, 21 Jan 2005 16:27:03 -0000, Mark Cave-Ayland
<m.cave-ayland at webbased.co.uk> wrote:
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On
> > Behalf Of Robin Chauhan
> > Sent: 21 January 2005 15:52
> > To: PostGIS Users Discussion
> > Subject: [postgis-users] Crash when creating index on ( int, geom )
> >
> >
> > I have items table with:
> >
> >  item_pool    | integer                     | not null default '-1'
> >  item_geom  | geometry                 |
> >
> > Indexing (item_geom, item_pool) works:
> >
> > create index item_pool_index on items using gist ( item_geom
> > gist_geometry_ops, item_pool ); CREATE INDEX
> >
> > But indexing in the opposite order does not (and crashes the server):
> >
> > create index item_pool_index2 on trips using gist (
> > item_pool, item_geom gist_geometry_ops );  server closed the
> > connection unexpectedly  This probably means the server
> > terminated abnormally
> >
> > My system:
> >
> > cp_test=# select postgis_version();
> >             postgis_version
> > ---------------------------------------
> >  0.9 USE_GEOS=0 USE_PROJ=0 USE_STATS=1
> > cp_test=# select version();
> >                                version
> > ---------------------------------------------------------------------
> >  PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC
> > egcs-2.91.66 (1 row)
> >
> > I have also installed contrib/btree_gist
> >
> > Is this known behaviour?  Should I be able to index (
> > integer, geom )?
> >
> > The effect I want is that the performance of spatial queries
> > for items (which are points) in one pool, are not affected
> > much by the how many items there are in other pools.
> >
> > Thanks!
> > -Robin Chauhan
> 
> 
> Hi Robin,
> 
> There should be no problem creating a multi-column GiST index like you are
> attempting to do with btree_gist. I think there are probably two things that
> could cause this, data corruption or a bug in the btree_gist code.
> 
> For data corruption, you can test this by creating a duplicate table using
> CREATE TABLE items_copy AS SELECT * FROM items and then try building the
> indices on this copy of the table. If this works, then somehow your data has
> been corrupted.
> 
> If it doesn't work, then it's likely to be a bug in btree_gist. In this
> case, you need to attach a gdb instance to your backend and when it crashes,
> get a backtrace using bt. You'll then need to write an email to
> pgsql-hackers list explaining how to reproduce the problem and include the
> backtrace - with that information it shouldn't be too difficult for one of
> the developers to find the bug and fix it.
> 
> Kind regards,
> 
> Mark.
> 
> ------------------------
> WebBased Ltd
> South West Technology Centre
> Tamar Science Park
> Plymouth
> PL6 8BT
> 
> T: +44 (0)1752 791021
> F: +44 (0)1752 791023
> W: http://www.webbased.co.uk
> 
>