[postgis-users] simplify crashing postgres on larger table

Paul Ramsey pramsey at refractions.net
Mon Oct 15 14:36:52 PDT 2007


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




More information about the postgis-users mailing list