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

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org