2011/10/11 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.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
>> =C2=A0 create temp table aux as select $1.*;
>> =C2=A0 execute 'update aux set ' || quote_ident($2) || ' =3D ' || quote_=
literal($3);
>> =C2=A0 select into $1 * from aux;
>> =C2=A0 drop table aux;
>> =C2=A0 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 =3D setfield(p, 'a', '20');
>
>> WARNING: =C2=A0out of shared memory
>> CONTEXT: =C2=A0SQL statement "create temp table aux as select $1.*"
>> PL/pgSQL function "setfield" line 3 at SQL statement
>> ERROR: =C2=A0out of shared memory
>> HINT: =C2=A0You might need to increase max_locks_per_transaction.
>> CONTEXT: =C2=A0SQL 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). =C2=A0Pl=
ease
> follow the advice given in the error message.
ok
On other hand - is necessary to hold a locks for dropped temporary tables?
Regards
Pavel
>
> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0regards, tom lane
>
