[postgis-devel] caches lifetime with SQL vs PL/PGSQL procs

[strk at refractions.net]

On postgresql-8.0.0 I've faced a *really* weird behavior.

A simple query (single table - simple function call - no index),
makes postgres process grow about as much as the memory size required
to keep ALL rows in memory.

The invoked procedure call doesn't leak.
It's IMMUTABLE.
Calls other procedures (not leaking).

Now.
One of the other procedures it calls is an 'SQL' one.
Replacing it with a correponding 'PL/PGSQL' implementation
drastically reduces memory occupation:

	SQL:       220Mb
	PL/PGSQL:   13Mb

The function body is *really* simple:

-- SQL
CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS
'SELECT proj4text::text FROM spatial_ref_sys WHERE srid= $1'
LANGUAGE 'sql' IMMUTABLE STRICT; 

-- PL/PGSQL
CREATE OR REPLACE FUNCTION get_proj4_from_srid(integer) RETURNS text AS
' BEGIN
        RETURN proj4text::text FROM spatial_ref_sys WHERE srid= $1;
END
' LANGUAGE 'plpgsql' IMMUTABLE STRICT; 


Is this expected ?

--strk;