Re: Query plan question, and a memory leak - Mailing list pgsql-general

Greg Stark <gsstark@mit.edu> writes:
> 2) The version of the query at the bottom appears to trigger a big memory
>    leak.

> -- make_box(longitude, latitude, distance) --
> CREATE OR REPLACE FUNCTION make_box(float,float,float) RETURNS box AS
> 'SELECT box(point(long-d_long,lat-d_lat),point(long+d_long,lat+d_lat))
>    FROM (SELECT $1 AS long, $2 AS lat,
>          $3*1000::float/1852::float/60::float as d_lat,
>          $3*1000::float/1852::float/60::float/cos(radians($2)) as d_long
>         ) as x'
> LANGUAGE SQL
> STRICT IMMUTABLE;

I fooled around with this, and was able to verify a per-row memory leak
in 7.3 --- but it's gone in CVS tip, probably because of the work I did
recently to prevent memory leaks in SQL functions.

My guess is that you could work around the leak in 7.3 by flattening the
query in make_box into a single SELECT, instead of being cute with a
sub-select.

            regards, tom lane