<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div>I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index...</div><div><br></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>CREATE INDEX address_location ON addresses USING GIST (location);</div><div><br></div><div>...it won't crash. (Inserts thousands of rows without a problem.) With that index in place, it will always crash after ~300 rows. And here's the log when it does crash:</div><blockquote class="webkit-indent-blockquote" style="margin: 0 0 0 40px; border: none; padding: 0px;"><div><br></div><div><div>LOG: server process (PID 98414) was terminated by signal 11: Segmentation fault</div></div><div><div>LOG: terminating any other active server processes</div></div><div><div>WARNING: terminating connection because of crash of another server process</div></div><div><div>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.</div></div><div><div>HINT: In a moment you should be able to reconnect to the database and repeat your command.</div></div><div><div>LOG: all server processes terminated; reinitializing</div></div><div><div>LOG: database system was interrupted; last known up at 2012-07-04 10:44:15 CEST</div></div><div><div>LOG: database system was not properly shut down; automatic recovery in progress</div></div><div><div>LOG: redo starts at 0/78E4A50</div></div><div><div>LOG: record with zero length at 0/7B52580</div></div><div><div>LOG: redo done at 0/7B52540</div></div><div><div>LOG: last completed transaction was at log time 2012-07-04 10:44:40.712517+02</div></div><div><div>LOG: database system is ready to accept connections</div></div></blockquote><div><br><div><div>On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><div>On 03/07/12 13:20, René Fournier wrote:<br><br><blockquote type="cite">So, it seems that the table gets full and corrupted to some extent.<br></blockquote><blockquote type="cite">After my import script inserts ~290 rows, and then postgres crashes...<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> mydb=# select count(*) from addresses;INSERT INTO addresses (<br></blockquote><blockquote type="cite"> account_id, territory_id, location ) VALUES ( 1, 75,<br></blockquote><blockquote type="cite"> ST_GeomFromText('POINT(-114.267388 51.089941)') );<br></blockquote><blockquote type="cite"> count<br></blockquote><blockquote type="cite"> -------<br></blockquote><blockquote type="cite"> 284<br></blockquote><blockquote type="cite"> (1 row)<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> The connection to the server was lost. Attempting reset: Failed.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">So, can't insert any more rows...<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> !> delete from addresses where id > 50;<br></blockquote><blockquote type="cite"> You are currently not connected to a database.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Postgres client/connection is crashed.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> !> \q<br></blockquote><blockquote type="cite"> Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres<br></blockquote><blockquote type="cite"> mydb psql (9.1.4)<br></blockquote><blockquote type="cite"> Type "help" for help.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> mydb=# delete from addresses where id > 50;<br></blockquote><blockquote type="cite"> DELETE 234<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">Deleting rows works...<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> mydb=# select count(*) from addresses;INSERT INTO addresses (<br></blockquote><blockquote type="cite"> account_id, territory_id, location ) VALUES ( 1, 75,<br></blockquote><blockquote type="cite"> ST_GeomFromText('POINT(-114.267388 51.089941)') );<br></blockquote><blockquote type="cite"> count<br></blockquote><blockquote type="cite"> -------<br></blockquote><blockquote type="cite"> 50<br></blockquote><blockquote type="cite"> (1 row)<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> The connection to the server was lost. Attempting reset: Failed.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">SELECT and DELETE work, but I can't insert any new rows, until...<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> !> \q<br></blockquote><blockquote type="cite"> Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres<br></blockquote><blockquote type="cite"> mydb psql (9.1.4)<br></blockquote><blockquote type="cite"> Type "help" for help.<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> mydb=# vacuum;vacuum full;vacuum full analyze;<br></blockquote><blockquote type="cite"> VACUUM<br></blockquote><blockquote type="cite"> VACUUM<br></blockquote><blockquote type="cite"> NOTICE: no notnull values, invalid stats<br></blockquote><blockquote type="cite"> VACUUM<br></blockquote><blockquote type="cite"> mydb=# select count(*) from addresses;INSERT INTO addresses (<br></blockquote><blockquote type="cite"> account_id, territory_id, location ) VALUES ( 1, 75,<br></blockquote><blockquote type="cite"> ST_GeomFromText('POINT(-114.267388 51.089941)') );<br></blockquote><blockquote type="cite"> count<br></blockquote><blockquote type="cite"> -------<br></blockquote><blockquote type="cite"> 50<br></blockquote><blockquote type="cite"> (1 row)<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite"> INSERT 0 1<br></blockquote><blockquote type="cite"> mydb=#<br></blockquote><blockquote type="cite"><br></blockquote><blockquote type="cite">So, it appears there's some weird corruption going on... Still, not sure<br></blockquote><blockquote type="cite">what to try next. My PostGIS is via Macports, not sure how to enable the<br></blockquote><blockquote type="cite">debug mode...<br></blockquote><br>Hi René,<br><br>I think that you need to create a new bug on the PostGIS bug tracker and upload a file that causes the crash on your system, i.e. it can be run using "psql -d postgis_db -f crash.sql" so that we can try and reproduce what you are seeing.<br><br>Also as a matter of interest, do you see anything interesting in the PostgreSQL log file at the time of the crash?<br><br><br>ATB,<br><br>Mark.<br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>http://postgis.refractions.net/mailman/listinfo/postgis-users<br></div></blockquote></div><br></div></body></html>