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

Tue Jul 10 09:50:41 PDT 2012

Since others get crashes on 1.5.4, I tested *exactly* that version and
lo, it does in fact crash. The current 1.5 stable branch does not
crash, so finding the different might not be hard... (also points to
needing a 1.5.5)
P.

On Tue, Jul 10, 2012 at 1:00 AM, René Fournier <m5 at renefournier.com> wrote:
> Ticket submitted:
>
>         http://trac.osgeo.org/postgis/ticket/1903
>
> But the crash problem seems not to happen for everyone. At least, the attached SQL file will crash my PostgreSQL 1.5.4 on Mac OS X 10.7.4 every time. But not for Paul. Would some of you be able to test it on your setup, to see if we can spot a pattern or connection? 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users