[postgis-users] Fwd: [HACKERS] Crash when inserting gist records, or creating index on ( int, geom )

[Robin Chauhan]

Hi everyone,

I shared my crashing problem with the pgsql hackers list, and Tom Lane
suggested using at least version 7.4.7 because he knows there were
some bugs in gist index code before then.

I wanted to share this thread with you in case it is useful to anyone.
Tom's response is at the bottom.

Thanks,
-Robin

----------------

I tried asking on postgis-users at postgis.refractions.net, and Mark
Cave-Ayland suggested
I ask you folks.

I installed PostGIS, which appeared to work just fine.  The I
installed contrib/btree_gist, and since then I've had issues.

Some background:  My postgres setup is on Red Hat linux, installed
from RPMs.  It is a shared hosting environment, and postgres source
was not installed.  So to build PostGIS , I checked the version
number,  and downloaded the corresponding source.

cp_test=# select version();
                             version
---------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66
(1 row)

I installed PostGIS 0.91:

cp_test=# select postgis_version();
          postgis_version
---------------------------------------
0.9 USE_GEOS=0 USE_PROJ=0 USE_STATS=1

I ran postgis.sql without a problem.  I also installed
contrib/btree_gist but I get a crash when creating a gist index:

cp_test=# create index trp_pool_index2 on trips using gist (
trp_matchpool, trp_org_geom gist_geometry_ops );
server closed the connection unexpectedly
       This probably means the server terminated abnormally
       before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Also, there is now another way to crash it: the first update query
works, the second causes a crash:

cp_test=#  Update trips set trp_org_geom=GeomFromText( 'POINT ('
||trp_org_lat||' '||trp_org_long ||')'::text , -1 ) where
tripid=355038;
UPDATE 1

cp_test=#  Update trips set trp_org_geom=GeomFromText( 'POINT ('
||trp_org_lat||' '||trp_org_long ||')'::text , -1 ) where
tripid=355034;
server closed the connection unexpectedly
       This probably means the server terminated abnormally
       before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!#

Note the record that causes the crash has currently an empty
trp_org_geom, the other does not:

cp_test=# select
tripid,trp_matchpool,trp_org_lat,trp_org_long,trp_org_geom,trp_dest_geom
from trips where tripid in (355038,355034);
tripid | trp_matchpool | trp_org_lat | trp_org_long |
trp_org_geom            |            trp_dest_geom
--------+---------------+-------------+--------------+------------------------------------+-------------------------------------
355038 |            -1 |    43.55059 |   -80.232725 |
SRID=-1;POINT(43.55059 -80.232725) | SRID=-1;POINT(43.769255
-79.409203)
355034 |          -104 |   46.977101 |   -70.552437 |
                  |
(2 rows)

My trips table is as follows:

                                      Table "public.trips"
     Column      |            Type             |                     Modifiers
------------------+-----------------------------+----------------------------------------------------
tripid           | integer                     | not null default
nextval('trips_tripid_seq'::text)
...
trp_matchpool    | integer                     | not null default '-1'
...
trp_org_lat      | double precision            | not null default '0'
trp_org_long     | double precision            | not null default '0'
trp_dest_lat     | double precision            | not null default '0'
trp_dest_long    | double precision            | not null default '0'
...
trp_org_geom     | geometry                    |
trp_dest_geom    | geometry                    |
...
Indexes: trips_pkey primary key btree (tripid),
        personid_trips_index btree (personid),
        trp_loc_index gist (trp_org_geom, trp_dest_geom),
        trp_org_index gist (trp_org_geom)
Check constraints: "enforce_srid_trp_org_geom" (srid(trp_org_geom) = -1)
                  "enforce_geotype_trp_org_geom"
((geometrytype(trp_org_geom) = 'POINT'::text) OR (trp_org_geom IS
NULL))
                  "enforce_srid_trp_dest_geom" (srid(trp_dest_geom) = -1)
                  "enforce_geotype_trp_dest_geom"
((geometrytype(trp_dest_geom) = 'POINT'::text) OR (trp_dest_geom IS
NULL))

Here is the bt from gdb:

(gdb) symbol-file /usr/bin/postgres
Reading symbols from /usr/bin/postgres...done.

(gdb) attach 13220
Attaching to Pid 13220
0x2ac26b22 in ?? ()

(gdb) cont
Continuing.

Program received signal SIGSEGV, Segmentation fault.
0x8082f0f in rt_box_union ()
(gdb) bt
#0  0x8082f0f in rt_box_union ()
#1  0x815fe3f in DirectFunctionCall2 ()
#2  0x2ae553af in ?? ()
#3  0x81604b5 in FunctionCall3 ()
#4  0x8071b7c in gistpenalty ()
#5  0x807112f in gistchoose ()
#6  0x806e574 in gistlayerinsert ()
#7  0x806e4c6 in gistdoinsert ()
#8  0x806e3d6 in gistinsert ()
#9  0x8160d7c in OidFunctionCall6 ()
#10 0x807b4d1 in index_insert ()
#11 0x80cfe5d in ExecInsertIndexTuples ()
#12 0x80cc6fc in ExecUpdate ()
#13 0x80cc2c3 in ExecutePlan ()
#14 0x80cb82d in ExecutorRun ()
#15 0x81175b2 in ProcessQuery ()
#16 0x8115c67 in pg_exec_query_string ()
#17 0x8116c09 in PostgresMain ()
#18 0x80ff013 in DoBackend ()
#19 0x80fe8b3 in BackendStartup ()
#20 0x80fdb7d in ServerLoop ()
#21 0x80fd750 in PostmasterMain ()
#22 0x80dd93e in main ()
#23 0x2ab8c9cb in ?? ()
(gdb)

Would you have any advice for me?

Many thanks,
-Robin Chauhan

---------- Forwarded message ----------
From: Tom Lane <tgl at sss.pgh.pa.us>
Date: Thu, 03 Feb 2005 01:32:03 -0500
Subject: Re: [HACKERS] Crash when inserting gist records, or creating
index on ( int, geom )
To: Robin Chauhan <robin.chauhan at gmail.com>
Cc: pgsql-hackers at postgresql.org


Robin Chauhan <robin.chauhan at gmail.com> writes:
> I installed PostGIS, which appeared to work just fine.  The I
> installed contrib/btree_gist, and since then I've had issues.

> PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66

> Would you have any advice for me?

Try a more modern Postgres.  I note in the CVS logs bug fixes for NULLs
in gist indexes as recently as 7.4.6.

If you can reproduce this on 7.4.7 or 8.0 then I'd be interested to look
at a test case ...

                        regards, tom lane