Thread: 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@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;

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

strk@refractions.net writes:
> 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...

You haven't actually provided a test case that would let someone else
reproduce the problem ...
        regards, tom lane

It is embarassing for me, but I could not reproduce the bug. :(
Maybe I just ended up with a corrupted database (or I was just too tired).
Behaviour seems to be the same for both SQL and pl/pgsql functions on 
a new database (and I got rid of the old one).

Sorry.

--strk;

On Thu, Mar 17, 2005 at 06:46:04PM -0500, Tom Lane wrote:
> strk@refractions.net writes:
> > 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...
> 
> You haven't actually provided a test case that would let someone else
> reproduce the problem ...
> 
>             regards, tom lane