shared memory/max_locks_per_transaction error

From: "Kynn Jones" <kynnjo(at)gmail(dot)com>
To: "pgsql-general General" <pgsql-general(at)postgresql(dot)org>
Subject: shared memory/max_locks_per_transaction error
Date: 2008-03-14 21:38:04
Message-ID: c2350ba40803141438r21f0e190p623e7db64c209601@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I've written a PL/pgSQL function that is supposed to create a whole bunch
(~4000) tables:
CREATE OR REPLACE FUNCTION create_tables () RETURNS void
AS $$
DECLARE
_s RECORD;
_t TEXT;
BEGIN
DROP TABLE IF EXISTS base CASCADE;
CREATE TABLE base ( /* omit lengthy definition */ );

FOR _s IN SELECT a, b FROM some_helper_table LOOP
_t := _s.a || '__' || _s.b;
EXECUTE 'DROP TABLE IF EXISTS "' || _t || '" CASCADE;';
EXECUTE 'CREATE TABLE "' || _t || '" ( LIKE base )';
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

but after executing the loop around 3500 times, it fails with an "out of
shared memory" error and the hint "you may need to increase
max_locks_per_transaction."

Initially I didn't know what our max_locks_per_transaction was (nor even a
typical value for it), but in light of the procedure's failure after 3500
iterations, I figured that it was 3500 or so. In fact ours is only 64 (the
default), so I'm now thoroughly confused.

Is there a way to force the release of locks within the loop?

TIA!

Kynn

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Webb Sprague 2008-03-14 22:36:03 Re: Making a schema "read-only" (was Unexpected message in grant/revoke script)
Previous Message Erik Jones 2008-03-14 21:16:03 Re: Making a schema "read-only" (was Unexpected message in grant/revoke script)