I have procedures that create temporary tables every time. When those procedures are called many times in a single transaction I get an "out of shared memory error". This happens even if the temporary tables are correctly dropped as shown in the example below. Does postgres retain a lock to a dropped object? How may I prevent this? It's not very convenient in that case to recycle the temp tables. With max_locks_per_transaction = 10 it stops after 1063 create tables. With the default setting of 64 it stops after 4314. CREATE OR REPLACE FUNCTION infiniteloop() RETURNS void AS $$ DECLARE mycounter integer := 0; BEGIN WHILE true LOOP EXECUTE 'CREATE TEMP TABLE t AS SELECT 1'; EXECUTE 'DROP TABLE t'; mycounter := mycounter + 1; RAISE NOTICE '% at %', mycounter, timeofday(); END LOOP; END $$ LANGUAGE PLPGSQL; select * from infiniteloop(); CREATE FUNCTION psql:t.sql:17: NOTICE: 1 at Mon Feb 13 13:44:42.320362 2006 JST psql:t.sql:17: NOTICE: 2 at Mon Feb 13 13:44:42.346362 2006 JST [...] psql:t.sql:17: NOTICE: 1062 at Mon Feb 13 13:45:17.595428 2006 JST psql:t.sql:17: NOTICE: 1063 at Mon Feb 13 13:45:17.634428 2006 JST psql:t.sql:17: WARNING: out of shared memory CONTEXT: SQL statement "CREATE TEMP TABLE t AS SELECT 1" PL/pgSQL function "infiniteloop" line 6 at execute statement psql:t.sql:17: ERROR: out of shared memory HINT: You may need to increase max_locks_per_transaction. CONTEXT: SQL statement "CREATE TEMP TABLE t AS SELECT 1" PL/pgSQL function "infiniteloop" line 6 at execute statement
