PostGIS 1.5.1 causing postmaster 8.3 crash on index creation

[nick]

Just upgraded to PostGIS 1.5.1 on a windows XP machine (from 1.3.4) with Postgres 8.3 and when creating an index on a geography column using this single statement;

CREATE INDEX t_swath_metadata_swath_bounding_key ON t_swath_metadata
USING gist (swath_bounding);

I get a client error

DEBUG:  StartTransactionCommandDEBUG:  StartTransactionDEBUG:  name:
unnamed; blockState:       DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0,
nestlvl: 1, children:DEBUG:  ProcessUtilityDEBUG:  mapped win32 error code 2
to 2
********** Error **********

and find the entire server / postmaster has crashed and no longer running. Log below;

2010-04-15 22:35:12 EST LOG:  database system was interrupted; last known up
at 2010-04-15 22:32:33 EST
2010-04-15 22:35:12 EST LOG:  database system was not properly shut down;
automatic recovery in progress
2010-04-15 22:35:13 EST LOG:  redo starts at C/F5D9EAB0
2010-04-15 22:35:13 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:35:13 EST FATAL:  the database system is starting up
2010-04-15 22:35:13 EST LOG:  unexpected pageaddr C/EEFDE000 in log file 12,
segment 246, offset 16637952
2010-04-15 22:35:13 EST LOG:  redo done at C/F6FDCC90
2010-04-15 22:35:13 EST LOG:  last completed transaction was at log time
2010-04-15 22:33:37.687+10
2010-04-15 22:35:14 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:35:14 EST FATAL:  the database system is starting up
2010-04-15 22:35:14 EST LOG:  database system is ready to accept
connections
2010-04-15 22:35:15 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:36:22 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:36:26 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:36:27 EST LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2010-04-15 22:36:35 EST LOG:  server process (PID 14600) was terminated by
exception 0xC0000005
2010-04-15 22:36:35 EST HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2010-04-15 22:36:35 EST LOG:  terminating any other active server processes
2010-04-15 22:36:35 EST WARNING:  terminating connection because of crash of
another server process
2010-04-15 22:36:35 EST DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2010-04-15 22:36:35 EST HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2010-04-15 22:36:35 EST WARNING:  terminating connection because of crash of
another server process
2010-04-15 22:36:35 EST DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2010-04-15 22:36:35 EST HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2010-04-15 22:36:35 EST LOG:  all server processes terminated;
reinitializing
2010-04-15 22:36:36 EST FATAL:  pre-existing shared memory block is still in
use
2010-04-15 22:36:36 EST HINT:  Check if there are any old server processes
still running, and terminate them.

I have been using this Postgresql instance (8.3) with PostGIS 1.3.4 for a long time previously to do similar operations on geometries and never encountered a crash like this.

I must admit there is not much to go on with that log above - raising to debug shows below:

2010-04-15 22:48:57 EST DEBUG:  StartTransactionCommand
2010-04-15 22:48:57 EST DEBUG:  StartTransaction
2010-04-15 22:48:57 EST DEBUG:  name: unnamed; blockState:       DEFAULT;
state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-04-15 22:48:57 EST DEBUG:  ProcessUtility
2010-04-15 22:48:57 EST DEBUG:  mapped win32 error code 2 to 2
2010-04-15 22:48:59 EST DEBUG:  reaping dead processes
2010-04-15 22:48:59 EST DEBUG:  server process (PID 15736) was terminated by
exception 0xC0000005
2010-04-15 22:48:59 EST HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2010-04-15 22:48:59 EST LOG:  server process (PID 15736) was terminated by
exception 0xC0000005
2010-04-15 22:48:59 EST HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2010-04-15 22:48:59 EST LOG:  terminating any other active server processes
2010-04-15 22:48:59 EST DEBUG:  sending SIGQUIT to process 13536
2010-04-15 22:48:59 EST DEBUG:  sending SIGQUIT to process 14200
2010-04-15 22:48:59 EST DEBUG:  sending SIGQUIT to process 13948
2010-04-15 22:48:59 EST DEBUG:  reaping dead processes
2010-04-15 22:48:59 EST DEBUG:  reaping dead processes
2010-04-15 22:48:59 EST DEBUG:  reaping dead processes
2010-04-15 22:48:59 EST LOG:  all server processes terminated;
reinitializing
2010-04-15 22:48:59 EST DEBUG:  shmem_exit(0)
2010-04-15 22:48:59 EST DEBUG:  invoking IpcMemoryCreate(size=36741120)
2010-04-15 22:49:00 EST FATAL:  pre-existing shared memory block is still in
use
2010-04-15 22:49:00 EST HINT:  Check if there are any old server processes
still running, and terminate them.
2010-04-15 22:49:00 EST DEBUG:  proc_exit(1)
2010-04-15 22:49:00 EST DEBUG:  shmem_exit(1)
2010-04-15 22:49:00 EST DEBUG:  exit(1)
2010-04-15 22:49:00 EST DEBUG:  logger shutting down
2010-04-15 22:49:00 EST DEBUG:  proc_exit(0)
2010-04-15 22:49:00 EST DEBUG:  shmem_exit(0)
2010-04-15 22:49:00 EST DEBUG:  exit(0)

UPDATE TO ORIGINAL EMAIL: After some thorough testing over the last days on this (I have a datafile that I can provide for pg_restore with which to reproduce the crash with single index creation statement), it only happens on Postgres 8.3, and not with Postgres 8.4.

This lessens the impact for us, providing a way around it by upgrading to 8.4, but I would have considered this bug as pretty much the most serious kind as it brings down the postmaster entirely forcing complete restart.

It also seems completely related to new geography types - we've been using geometries for a good while.

[robe]

Nick,

I tried building indexes on random geography data in my PostgreSQL 8.3 Windows 2003 install (8.3.7) to be precise and didn't run into any issues. Though I'm running 1.5.2 SVN (have to revert to 1.5.1 to be sure it wasn't an issue in 1.5.1 that was fixed in 1.5.2)

Does this happen for any geography table you try or a specific one? Also can you confirm which specific version of 8.3 you are running with

SELECT version();

There was a nasty gist bug introduced around 8.3.2 or 8.3.3 as I recall and I think fixed in 8.3.5 or something forget the exact, and that caused rather bizarre errors like all data being destroyed intermittently. If you are running an unpatched install, it could just be registering different behavior with geography.

Thanks, Regina

[pramsey]

Absent some test data to reproduce this, we can't do much.