[postgis-devel] Re: [HACKERS] caches lifetime with SQL vs PL/PGSQL procs

[strk at refractions.net]

I've committed the change in lwpostgis.sql.in
to workaround the bug in postgresql 8.0.{0,1}.

I kept SCRIPTS_VERSION to 0.2.1 as it is already a MINOR
increment since RC3. MINOR increments can be put back in sync
commenting out transaction lines (BEGIN,END) and sourcing the
file again.

--strk;

On Wed, Mar 16, 2005 at 02:42:45PM +0100, strk at refractions.net wrote:
> 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.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.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.org
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel