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

[strk at refractions.net]

I've tested with 8.0.1 and get same results.

--strk;

On Wed, Mar 16, 2005 at 01:04:03PM +0100, strk at refractions.net wrote:
> 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;