Re: Query plan question, and a memory leak

Greg Stark <gsstark(at)mit(dot)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