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
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) |