[postgis-users] Union as an aggregate

strk strk at keybit.net
Tue Sep 30 03:21:30 PDT 2003


dblasby wrote:
> strk wrote:
> > Dave, I made no special 'unite' code, just sql create command:
> > CREATE AGGREGATE unite (
> > 	sfunc = GeomUnion,
> > 	basetype = geometry,
> > 	stype = geometry
> > );
> 
> Hum - this shouldnt do anything.
> 
> The aggregate should have "null" as its initial value.
> 
> GeomUnion(null,<geometry>) --> null.
> 
> So, you'll end up with null *very* quickly because 
> GeomUnion(null,<geometry>) returns null without any actual execution.

It looks like postgres is behaving differently since I always get
a not-null result.

> 
> Are you running this on a big table?  At worst I'd expect this to use up 
> memory the same size all the geometries in the table.
> 

Geometry set size is about 2MB:
  gis=# select sum(mem_size(the_geom)) from world where gid < 50; 
  2148316

Initial postmaster process status is:
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  566 pgsql     15   0  3288 3288  2684 S     0.0  0.4   0:00 postmaster

After query:
  gis=# select box(unite(the_geom)) from plmshp02_1 where gid < 50;
  (180,83.1138763427734),(-180,-90)

The postmaster grew of about 66M (having touched a 78MB upper limit):
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  566 pgsql     19   0 71472  69M  3396 S     0.0  9.2   3:35 postmaster

If I run that again, *exactly the same query*:
  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  566 pgsql     14   0  126M 126M  3396 S     0.0 16.8   7:13 postmaster

It looks like someone is leaking memory, either postgres, postgis or geos.
Does it sound correct ?

PS: postgres is 7.3.4, postgis and geos are latest cvs.
--strk;



More information about the postgis-users mailing list