Re: [postgis-users] Union as an aggregate

strk <strk(at)keybit(dot)net> writes:
> dblasby wrote:
>> 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.

Yes. There's a special definition for aggregate behavior when the
transition function is strict and the initial value is null. As Dave
observes, such a combination would have no useful purpose if we did
not special-case it. The special definition is "take the first
non-null input as the initial transition value, and then apply the
transition function for each subsequent non-null input". This does
exactly what's needed for MAX and MIN, and is probably about what
you'd want for a geometric union operator too.

> 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.

On some platforms top's report of memory used can be misleading, because
it begins to count each page of shared memory against a process when the
process first touches that page. So if you have a big scan that touches
more and more of the shared buffers, the reported process size goes up
--- but there's really no memory leak. Try a plain "select count(*)"
against your table and see if you see the same change in reported size.
Alternatively, if the reported size continues to increase well beyond
your shared memory allocation, then I'd believe that as evidence of a
leak.

regards, tom lane