Re: [postgis-users] Union as an aggregate - Mailing list pgsql-general

From strk
Subject Re: [postgis-users] Union as an aggregate
Date
Msg-id 20030930122130.B35904@freek.keybit.net
Whole thread Raw
Responses Re: [postgis-users] Union as an aggregate
List pgsql-general
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;

pgsql-general by date:

Previous
From: Bjørn T Johansen
Date:
Subject: Re: Time problem again?
Next
From: Bjørn T Johansen
Date:
Subject: Problem with lock?