Обсуждение: "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