From: | strk(at)refractions(dot)net |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Re: caches lifetime with SQL vs PL/PGSQL procs |
Date: | 2005-03-16 13:42:45 |
Message-ID: | 20050316134245.GB23743@freek.keybit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2005-03-16 13:59:09 | Re: Real-Time Vacuum Possibility |
Previous Message | strk | 2005-03-16 12:58:44 | Re: caches lifetime with SQL vs PL/PGSQL procs |