On Fri, Nov 29, 2002 at 05:24:20PM +0100, tgl@sss.pgh.pa.us wrote:
>
> Han Holl <han.holl@prismant.nl> writes:
> > Postgres seems to free the palloc'd memory at the end of the
> > select statement,
>
> It should do so sooner than that. Can you provide a self-contained
> example?
I have tried to do that and failed. The problem proved to have
nothing to do with palloc (I was too hasty there), but there
_is_ something fishy here:
Given table:
CREATE TABLE "rubdefs" (
"srto" character(1) NOT NULL,
"titel" character varying(40),
"rubnr" smallint
);
and the function:
CREATE FUNCTION "rubton" (text,text) RETURNS smallint AS '
select rubnr from rubdefs where srto = $1 and titel = $2
' LANGUAGE 'sql' WITH ( iscachable );
A query like:
select count( rubton(udps.srto, 'adres')) from udps where srto = 'T';
on a view will use (leak) approximately 64 bytes per tuples visited,
per column. My original query had 230 columns and 450000 tuples, so
it would have required about 6000 Mb.
Rewriting the mapping function as:
create function rubton2(text, text) returns int as '
declare
rsl integer;
begin
select into rsl rubnr from rubdefs where srto = $1 and titel = $2;
return rsl;
end
' language 'plpgsql';
solves the problem (but is slightly slower).
So it looks like using the pure SQL version here is not a good idea.
I'm quite willing to produce an example of this behaviour, but I
suspect that it's 'known behaviour' to experts.
Cheers,
Han Holl
>
> regards, tom lane
>
