[postgis-users] simplify crashing postgres on larger table

[ivan minčík]

thanks a lot,
if I understand well, I tried to run SELECT 
sizeof(simplify(the_geom,500)) FROM my_table;
result is that it crashed in 5 seconds....



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
>