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

[Robin Chauhan]

IDoes it not make sense to index on 2 geom columns in one index?  If I
am searching for 'trip' records whose trp_org is within box x AND
whose trp_dest is within box y, would such an index not be useful?

I think I've narrowed (one of) my problems down to this particular
index with 2 geoms:

create index trp_loc_index on trips using GIST (trp_org_geom
GIST_GEOMETRY_OPS, trp_dest_geom GIST_GEOMETRY_OPS);

When I had the index in place, and attempted to update the values of
the trp_org_geom column like so, it crashes:

Update trips set trp_org_geom=GeomFromText( 'POINT (' ||trp_org_lat||'
'||trp_org_long ||')'::text , -1 );
  server closed the connection unexpectedly

When I dropped the index, the above command worked fine.  When I
subsequently tried to add the index, it crashes again.

If such an index would not improve performance, I am willing to just
avoid it and get on with my project.  But if it would, I intend to
track down the issue.

Thanks,
-Robin



On Mon, 31 Jan 2005 07:36:23 -0000, Mark Cave-Ayland
<m.cave-ayland at webbased.co.uk> wrote:
> Hi Robin,
> 
> It sounds as if you've got quite close :) As you rightly point out, you need
> to attach gdb to the forked process on the server as opposed to the PID of
> the postmaster. This is the procedure I use when debugging crashes:
> 
> Client
> ------
> 
> psql -d mydb
> mydb=# select pg_backend_pid();
> 
> (This will give you the PID of the backend)
> 
> Server
> ------
> 
> gdb
> (gdb) attach <pid of backend>
> (gdb) cont
> 
> Client
> ------
> 
> mydb=# select my_crashing_function();
> 
> Server
> ------
> 
> (gdb) bt
> 
> Note that even compiling without --enable-debug should still be enough to
> give you the symbols of the functions. Another option may be to try manually
> loading the symbols from the postgres executable using the symbol-file
> command. This should be done just before the attach command, e.g.
> 
> (gdb) symbol-file /path/to/postgres
> 
> 
> 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
> 
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net
> > [mailto:postgis-users-bounces at postgis.refractions.net] On
> > Behalf Of Robin Chauhan
> > Sent: 30 January 2005 18:13
> > To: Mark Cave-Ayland
> > Cc: PostGIS Users Discussion
> > Subject: Re: [postgis-users] Crash when creating index on (
> > int, geom )
> >
> >
> > I eventually discovered that I was meant to attach gdb to the
> > particular postgres instance, not the postmaster.  However
> > I'm not sure the bt output is very useful.  Would I need to
> > rebuild postgres with debug enabled to get anything more meaningful?
> >
> > (gdb) attach 30603
> > Attaching to Pid 30603
> > 0x2ac26b22 in ?? ()
> > (gdb)
> > (gdb) cont
> > Continuing.
> > Program received signal SIGSEGV, Segmentation fault.
> > 0x8082f0f in ?? ()
> > (gdb) bt
> > #0  0x8082f0f in ?? ()
> > #1  0x815fe3f in ?? ()
> > #2  0x2ae553af in ?? ()
> > #3  0x81604b5 in ?? ()
> > #4  0x8071b7c in ?? ()
> > #5  0x807112f in ?? ()
> > #6  0x806e574 in ?? ()
> > #7  0x806e4c6 in ?? ()
> > #8  0x806e3d6 in ?? ()
> > #9  0x8160d7c in ?? ()
> > #10 0x807b4d1 in ?? ()
> > #11 0x80cfe5d in ?? ()
> > #12 0x80cc6fc in ?? ()
> > #13 0x80cc2c3 in ?? ()
> > #14 0x80cb82d in ?? ()
> > #15 0x81175b2 in ?? ()
> > #16 0x8115c67 in ?? ()
> > #17 0x8116c09 in ?? ()
> > #18 0x80ff013 in ?? ()
> > #19 0x80fe8b3 in ?? ()
> > #20 0x80fdb7d in ?? ()
> > #21 0x80fd750 in ?? ()
> > #22 0x80dd93e in ?? ()
> > #23 0x2ab8c9cb in ?? ()
> > (gdb)
> >
> >
> > On Sat, 29 Jan 2005 17:07:09 -0500, Robin Chauhan
> > <robin.chauhan at gmail.com> wrote:
> > > Hello Mark,
> > > I'm attempting to generate a backtrace as you suggested.  I
> > was able
> > > to attach gdb to the postmaster process, but I dont know
> > how to make
> > > gdb break when the failure occurs -- I dont know where I
> > would set a
> > > breakpoint or watchpoint to be able to catch this event.
> > When I Can
> > > you give me a hint or pointer?  I think no core file is being
> > > generated from the crash, because the postmaster and psql processes
> > > both continue to run.
> > >
> > > Should I be debugging the psql process and not the
> > postmaster process?
> > > I tried causing this crashing both while attached to psql and
> > > postmaster but again I dont know how to cause gdb to stop
> > at a useful
> > > place to see the backtrace of the crash, and not just of the normal
> > > running of postmaster.   This is noob territory for me, so please
> > > excuse the ignorant questions.
> > > Many thanks,
> > > -Robin
> 
>