[postgis-devel] [postgis-users] Super weird problem: Cannot insert more than ~300 rows (or ~100 kb) into a table
Paul Ramsey
pramsey at opengeo.org
Thu Jul 12 09:52:07 PDT 2012
I think sooner than later since it's a crasher in a fairly exercisable place.
P.
On Thu, Jul 12, 2012 at 12:59 AM, René Fournier <m5 at renefournier.com> wrote:
> Any ETA on 1.5.5? This bug in 1.5.4 is actually a show stopper for me... Thanks.
>
> On 2012-07-10, at 10:46 PM, Paul Ramsey wrote:
>
>> On Tue, Jul 10, 2012 at 12:24 PM, Sandro Santilli <strk at keybit.net> wrote:
>>> Better cut a 1.5.5 soon.
>>> Did the NEWS file contain a note about this already ?
>>
>> No, it was a teeny change that had no ticket :) I know, I'm bad. Has one now.
>> P.
>>
>>>
>>> --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.
>>> _______________________________________________
>>> 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
More information about the postgis-devel
mailing list