[postgis-users] simplify crashing postgres on larger table

[Paul Ramsey]

Where I used 'sizeof' I meant 'mem_size'...

On 15-Oct-07, at 2:36 PM, Paul Ramsey wrote:

> Well, you have a bug in your sights, probably, but you haven't  
> isolated it...
> First, is it the UPDATE or the function?
> Does
>   SELECT sizeof(simplify(the_geom,500)) FROM thetable
> crash the backend?
>
> If it's the function, then, it's either the number of features and  
> bad luck with a memory leak, or a single geometry that crashes the  
> function.  Find out if it's the geometry by repeatedly dividing  
> your data set into halves and checking each half separately.  If  
> one half keeps failing, you'll eventually winnow it down to the  
> offending geometry, and you can report that back here and the fix  
> should be "easy".
>
> P.
>
>
>
> On 15-Oct-07, at 12:17 PM, ivan minčík wrote:
>
>> Halo postgis users,
>> I found that this query:
>>
>> UPDATE table SET the_geom500=simplify(the_geom,500);
>>
>> is crashing postgres database on 2 GB table. On smaller table  
>> everything is OK.
>>
>> I have tested this on 2 Debian Etch stable computers (postgresql  
>> and postgis from repositories).
>> after some 5 minutes I always get
>>
>> server closed the connection unexpectedly
>>        This probably means the server terminated abnormally
>>        before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>>
>>
>> in log there is
>>
>> 2007-10-15 21:08:47 CEST LOG:  server process (PID 5234) was  
>> terminated by signal 11
>> 2007-10-15 21:08:47 CEST LOG:  terminating any other active server  
>> processes
>> 2007-10-15 21:08:47 CEST LOG:  all server processes terminated;  
>> reinitializing
>> 2007-10-15 21:08:47 CEST FATAL:  the database system is starting up
>> 2007-10-15 21:08:47 CEST LOG:  database system was interrupted at  
>> 2007-10-15 21:08:40 CEST
>> 2007-10-15 21:08:47 CEST LOG:  checkpoint record is at 1/5BCE111C
>> 2007-10-15 21:08:47 CEST LOG:  redo record is at 1/5B546840; undo  
>> record is at 0/0; shutdown FALSE
>> 2007-10-15 21:08:47 CEST LOG:  next transaction ID: 45777; next  
>> OID: 131584
>> 2007-10-15 21:08:47 CEST LOG:  next MultiXactId: 1; next  
>> MultiXactOffset: 0
>> 2007-10-15 21:08:47 CEST LOG:  database system was not properly  
>> shut down; automatic recovery in progress
>> 2007-10-15 21:08:47 CEST LOG:  redo starts at 1/5B546840
>> 2007-10-15 21:08:50 CEST LOG:  invalid magic number 0000 in log  
>> file 1, segment 98, offset 15826944
>> 2007-10-15 21:08:50 CEST LOG:  redo done at 1/62F17F90
>> 2007-10-15 21:08:55 CEST LOG:  database system is ready
>> 2007-10-15 21:08:55 CEST LOG:  transaction ID wrap limit is  
>> 2147484146, limited by database "postgres"
>>
>> Does anybody knows what is going on?
>> thanks Ivan
>>
>>
>> _______________________________________________
>> 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