Re: memory leak in postgresql

2011/10/11 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I found a following issue (tested on PostgreSQL 9.2)
>
>> CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text)
>> RETURNS anyelement
>> LANGUAGE plpgsql
>> AS $function$
>> begin
>>   create temp table aux as select $1.*;
>>   execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3);
>>   select into $1 * from aux;
>>   drop table aux;
>>   return $1;
>> end;
>> $function$
>
>> create type mypoint as (a int, b int);
>
>> create table omega(p mypoint);
>
>> insert into omega select mypoint '(10,20)' from generate_series(1,100000);
>
>> update omega set p = setfield(p, 'a', '20');
>
>> WARNING:  out of shared memory
>> CONTEXT:  SQL statement "create temp table aux as select $1.*"
>> PL/pgSQL function "setfield" line 3 at SQL statement
>> ERROR:  out of shared memory
>> HINT:  You might need to increase max_locks_per_transaction.
>> CONTEXT:  SQL statement "create temp table aux as select $1.*"
>> PL/pgSQL function "setfield" line 3 at SQL statement
>
> This is not a memory leak, this is a "your transaction is holding too
> many locks" problem (namely, one lock for each transient table).  Please
> follow the advice given in the error message.

ok

On other hand - is necessary to hold a locks for dropped temporary tables?

Regards

Pavel

>
>                        regards, tom lane
>