Lists: | pgsql-hackers |
---|
From: | strk(at)refractions(dot)net |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | caches lifetime with SQL vs PL/PGSQL procs |
Date: | 2005-03-16 12:04:03 |
Message-ID: | 20050316120403.GN17570@freek.keybit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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;
From: | strk(at)refractions(dot)net |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | Re: caches lifetime with SQL vs PL/PGSQL procs |
Date: | 2005-03-16 12:58:44 |
Message-ID: | 20050316125844.GA23743@freek.keybit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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;
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 |
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: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | strk(at)refractions(dot)net |
Cc: | pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | Re: caches lifetime with SQL vs PL/PGSQL procs |
Date: | 2005-03-17 23:46:04 |
Message-ID: | 28648.1111103164@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
strk(at)refractions(dot)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
From: | strk(at)refractions(dot)net |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-hackers(at)postgresql(dot)org, postgis-devel(at)postgis(dot)refractions(dot)net |
Subject: | Re: caches lifetime with SQL vs PL/PGSQL procs |
Date: | 2005-03-22 09:07:40 |
Message-ID: | 20050322090739.GG82478@freek.keybit.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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(at)refractions(dot)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