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(at)refractions(dot)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(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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org