Re: caches lifetime with SQL vs PL/PGSQL procs

I've tried with 7.4.3 - *good* results with both SQL and PL/PGSQL 
(actually even less that best 8.0.1: 12Mb)

I think this makes it a bug...

--strk;

On Wed, Mar 16, 2005 at 01:58:44PM +0100, strk@refractions.net wrote:
> I've tested with 8.0.1 and get same results.
> 
> --strk;
> 
> On Wed, Mar 16, 2005 at 01:04:03PM +0100, strk@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;
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org