Date: Sun, 23 May 2004 22:32:49 +0400
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
Message-ID: <20040523183249.GC54296@dyatel.antar.bryansk.ru>
References: <20040522175419.GB54296@dyatel.antar.bryansk.ru>
	<24527.1085329698@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=koi8-r
Content-Disposition: inline
In-Reply-To: <24527.1085329698@sss.pgh.pa.us>
User-Agent: Mutt/1.4.2.1i

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