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