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

[Sandro Santilli]

Better cut a 1.5.5 soon.
Did the NEWS file contain a note about this already ?

--strk;

On Tue, Jul 10, 2012 at 09:53:56AM -0700, Paul Ramsey wrote:
> Indeed, there has been a small change since 1.5.4 that moves some
> testing for unknowns *below* the null test (where they should be)
> avoiding the null pointer reference that is causing the crash.
> 
> Index: postgis/geography_gist.c
> ===================================================================
> --- postgis/geography_gist.c    (revision 9721)
> +++ postgis/geography_gist.c    (revision 10037)
> @@ -267,17 +267,15 @@
>                 return 0.0;
>         }
> 
> -       if ( gidx_is_unknown(a) && gidx_is_unknown(b) )
> -       {
> -               return 0.0;
> -       }
> -
>         if ( a == NULL || gidx_is_unknown(a) )
>                 return gidx_volume(b);
> 
>         if ( b == NULL || gidx_is_unknown(b) )
>                 return gidx_volume(a);
> 
> +       if ( gidx_is_unknown(a) && gidx_is_unknown(b) )
> +               return 0.0;
> +
>         /* Ensure 'a' has the most dimensions. */
>         gidx_dimensionality_check(&a, &b);
> 
> 
> On Tue, Jul 10, 2012 at 9:50 AM, Paul Ramsey <pramsey at opengeo.org> wrote:
> > 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.