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
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"? |