[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgis-devel
Subject:    [postgis-devel] Re: caches lifetime with SQL vs PL/PGSQL procs
From:       strk () refractions ! net
Date:       2005-03-16 12:58:44
Message-ID: 20050316125844.GA23743 () freek ! keybit ! net
[Download RAW message or body]

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;

[prev in list] [next in list] [prev in thread] [next in thread]