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