Обсуждение: memory leak in postgresql

Hello

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

Regards

Pavel Stehule

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
>   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.

            regards, tom lane

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
>