"out of shared memory error" with temp tables

From: A Gattiker <agattik(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: "out of shared memory error" with temp tables
Date: 2006-02-13 04:59:21
Message-ID: e6b92cce0602122059o4cf7f372td88b3c44cb40aa26@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message indu ss 2006-02-13 05:22:59 Re: Compile of Pgmail function fails
Previous Message Stephan Szabo 2006-02-13 04:38:38 Re: Why does an ON SELECT rule have to be named "_RETURN"?