Re: caches lifetime with SQL vs PL/PGSQL procs

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(dot)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;