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

[René Fournier]

Hi Paul,

Just submitted a ticket as you suggested: http://trac.osgeo.org/postgis/ticket/1903
Thanks!

On 2012-07-04, at 11:46 PM, Paul Ramsey wrote:

> This is all well and good, but we need to know which 300 rows to load.
> If you have a load file, just strip out the first 301 (or whatever)
> rows, confirm that the file makes things go "boom" and then create a
> ticket on the tracker with the dump file attached.
> 
> http://trac.osgeo.org/
> 
> Thanks!
> 
> P.
> 
> On Wed, Jul 4, 2012 at 2:01 AM, René Fournier <m5 at renefournier.com> wrote:
>> 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
>> 
>> 
>> 
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
> _______________________________________________
> 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/20120706/08cf0c26/attachment.html>