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
