memory leak in postgresql

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