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

[Mark Cave-Ayland]

> -----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