Skip site navigation (1) Skip section navigation (2)

Re: [postgis-users] Union as an aggregate

From: strk <strk(at)keybit(dot)net>
To: David Blasby <dblasby(at)refractions(dot)net>
Cc: postgis-users(at)postgis(dot)refractions(dot)net,pgsql-general(at)postgresql(dot)org
Subject: Re: [postgis-users] Union as an aggregate
Date: 2003-09-30 10:21:30
Message-ID: 20030930122130.B35904@freek.keybit.net (view raw or flat)
Thread:
Lists: 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;

Responses

pgsql-general by date

Next:From: Bjørn T JohansenDate: 2003-09-30 10:33:31
Subject: Problem with lock?
Previous:From: Bjørn T JohansenDate: 2003-09-30 09:54:37
Subject: Re: Time problem again?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group