Thread: "out of shared memory error" with temp tables
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
A Gattiker <agattik@gmail.com> writes: > 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? Yes, and you can't, because the object is not actually dropped until end of transaction. I'd suggest rethinking your temp table use: that design is going to bloat the system catalogs enormously, even if you weren't running out of lock space. regards, tom lane