From: Alexander M. Pravking <fduch@antar.bryansk.ru>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Memory usage on subselect
Date: Sun, 23 May 2004 22:32:49 +0400
Message-ID: <20040523183249.GC54296@dyatel.antar.bryansk.ru> (raw)
In-Reply-To: <24527.1085329698@sss.pgh.pa.us>
References: <20040522175419.GB54296@dyatel.antar.bryansk.ru>
<24527.1085329698@sss.pgh.pa.us>
On Sun, May 23, 2004 at 12:28:18PM -0400, Tom Lane wrote:
> "Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> > I've got a memory usage problem when I try to do a subselect on the same
> > table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query:
> > ...
> > The "services" table is indexed only on "id" column and has about a
> > thousand tuples. sreq(integer, text, timestamptz) is a strict immutable
> > function written in SQL.
>
> IIRC, there were intraquery memory leaks associated with SQL-language
> functions until fairly recently. Can you try your problem case on 7.4?
> Or see if you can rewrite the sreq function in plpgsql.
Thanks, Tom, both 7.4.1 and plpgsql function on 7.3.4 work well. However,
plpgsql function is a little slower, but that's another story.
I also tried to turn off IMMUTABLE for the function, but it did not
affect memory usage on 7.3.4.
BTW, after cancelling the original query postgres freed all the memory,
and used ~7M again, so the leak was not "forever".
Good luck, thansk again ;)
--
Fduch M. Pravking
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fduch@antar.bryansk.ru, tgl@sss.pgh.pa.us
Subject: Re: Memory usage on subselect
In-Reply-To: <20040523183249.GC54296@dyatel.antar.bryansk.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox