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
