[postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table

[René Fournier]

I've narrowed the point at which a crash will always happen: If I simply create the table *without* the index...

	CREATE INDEX address_location ON addresses USING GIST (location);

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

LOG:  server process (PID 98414) was terminated by signal 11: Segmentation fault
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted; last known up at 2012-07-04 10:44:15 CEST
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/78E4A50
LOG:  record with zero length at 0/7B52580
LOG:  redo done at 0/7B52540
LOG:  last completed transaction was at log time 2012-07-04 10:44:40.712517+02
LOG:  database system is ready to accept connections

On 2012-07-03, at 6:31 PM, Mark Cave-Ayland wrote:

> On 03/07/12 13:20, René Fournier wrote:
> 
>> So, it seems that the table gets full and corrupted to some extent.
>> After my import script inserts ~290 rows, and then postgres crashes...
>> 
>>    mydb=# select count(*) from addresses;INSERT INTO addresses (
>>    account_id, territory_id, location ) VALUES ( 1, 75,
>>    ST_GeomFromText('POINT(-114.267388 51.089941)') );
>>    count
>>    -------
>>    284
>>    (1 row)
>> 
>>    The connection to the server was lost. Attempting reset: Failed.
>> 
>> So, can't insert any more rows...
>> 
>> 
>>    !> delete from addresses where id > 50;
>>    You are currently not connected to a database.
>> 
>> 
>> Postgres client/connection is crashed.
>> 
>>    !> \q
>>    Tue Jul 03 14:15:28 -- rene /opt/local/var/db:: psql -U postgres
>>    mydb psql (9.1.4)
>>    Type "help" for help.
>> 
>>    mydb=# delete from addresses where id > 50;
>>    DELETE 234
>> 
>> Deleting rows works...
>> 
>> 
>>    mydb=# select count(*) from addresses;INSERT INTO addresses (
>>    account_id, territory_id, location ) VALUES ( 1, 75,
>>    ST_GeomFromText('POINT(-114.267388 51.089941)') );
>>    count
>>    -------
>>    50
>>    (1 row)
>> 
>>    The connection to the server was lost. Attempting reset: Failed.
>> 
>> 
>> SELECT and DELETE work, but I can't insert any new rows, until...
>> 
>> 
>>    !> \q
>>    Tue Jul 03 14:15:39 -- rene /opt/local/var/db:: psql -U postgres
>>    mydb psql (9.1.4)
>>    Type "help" for help.
>> 
>>    mydb=# vacuum;vacuum full;vacuum full analyze;
>>    VACUUM
>>    VACUUM
>>    NOTICE: no notnull values, invalid stats
>>    VACUUM
>>    mydb=# select count(*) from addresses;INSERT INTO addresses (
>>    account_id, territory_id, location ) VALUES ( 1, 75,
>>    ST_GeomFromText('POINT(-114.267388 51.089941)') );
>>    count
>>    -------
>>    50
>>    (1 row)
>> 
>>    INSERT 0 1
>>    mydb=#
>> 
>> So, it appears there's some weird corruption going on... Still, not sure
>> what to try next. My PostGIS is via Macports, not sure how to enable the
>> debug mode...
> 
> Hi René,
> 
> 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.
> 
> Also as a matter of interest, do you see anything interesting in the PostgreSQL log file at the time of the crash?
> 
> 
> ATB,
> 
> Mark.
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120704/eb0be62d/attachment.html>