--
-- Test procedures to create a lot of tables, and to SELECT from them,
-- to test how many locks you can acquire.
--
-- Usage:
--
-- 1. First create enough test tables. 20000 is enough to saturate the
--    lock table in default configuration.
--
--    CALL locktest.create_test_tables(20000);
--
-- 2. To measure how many locks you can take:
--
--    postgres=# CALL locktest.select_test_tables();
--    NOTICE:  got error at 17535: out of shared memory
--    CALL
--
--    Make note of the number it prints. That's how many locks it was
--    able to acquire before the lock table filled up.
--
--
-- To see the size of the total shared memory usage:
--
-- SELECT SUM(allocated_size) FROM pg_shmem_allocations;
--

create schema if not exists locktest;

-- Create tables
create or replace procedure locktest.create_test_tables(num_parts integer) as $$
begin
  for i in 1..num_parts loop
    execute format('create table locktest.parttest%s()', i);
    if i % 1000 = 0 then
      raise notice 'progress: % / %', i, num_parts;
      commit;
    end if;
  end loop;
  commit;
end;
$$ language plpgsql;

-- Helper to drop the tables in batches, to clean up after the
-- test. Plain "DROP SCHEMA locktest CASCADE" would work too, but with
-- the defaults it will run out of lock space too.
create or replace procedure locktest.drop_test_tables(num_parts integer) as $$
begin
  for i in 1..num_parts loop
    execute format('drop table locktest.parttest%s', i);
    if i % 1000 = 0 then
      raise notice 'progress: % / %', i, num_parts;
      commit;
    end if;
  end loop;
  commit;
end;
$$ language plpgsql;

-- Measure
create or replace procedure locktest.select_test_tables() as $$
declare
  i integer;
begin
  i = 1;
  begin
    loop
      execute format('select 1 from locktest.parttest%s', i);
      i = i + 1;
    end loop;
  exception
    when others then
      raise notice 'got error at %: %', i, sqlerrm;
  end;
end;
$$ language plpgsql;

-- To create test tables:
-- CALL locktest.create_test_tables(20000);

-- To measure:
-- CALL locktest.select_test_tables();

-- To clean up:
-- call locktest.drop_test_tables(20000);
-- drop schema locktest cascade;
