fsm-copy-test-v2.sql

-- XXX run this first

-- ALTER SYSTEM SET fsync TO 'off';
-- SELECT pg_reload_conf();

-- DROP TABLE copy_test_setup;

-- CREATE TABLE copy_test_setup (
--     value int
-- ) WITH (autovacuum_enabled=false);

-- DO $$
-- BEGIN
-- RAISE NOTICE 'Creating tables and inserting one tuple';
--   FOR i IN 1..100 LOOP
--     EXECUTE FORMAT(
--       'CREATE UNLOGGED TABLE IF NOT EXISTS %I (LIKE copy_test_setup) WITH (autovacuum_enabled=false)',
--       'copy_test' || i
--     );
--     EXECUTE FORMAT(
--       'INSERT INTO %I VALUES (0)',
--       'copy_test' || i
--     );
--   END LOOP;
-- END;
-- $$;


-- Fill up to some number of pages
DO $$
DECLARE curtid tid;
num int;
BEGIN
num = 1;
  LOOP
    INSERT INTO copy_test_setup VALUES (num) RETURNING ctid INTO curtid;
    -- on my system, 1 block can fit 226 of these records; leave room for "0" tuple
    EXIT WHEN curtid >= tid '(1, 225)';
    -- EXIT WHEN curtid >= tid '(3, 225)';
    -- EXIT WHEN curtid >= tid '(5, 225)';
    -- EXIT WHEN curtid >= tid '(7, 225)';
    num = num + 1;
  END LOOP;
END;
$$;

COPY copy_test_setup TO '/home/john/pgtest/dump.out' WITH csv;


DO $$
BEGIN
RAISE NOTICE 'Emptying tables';
  DELETE FROM copy_test_setup;
  FOR i IN 1..100 LOOP
    EXECUTE FORMAT(
      'DELETE FROM %I WHERE value > 0',
      'copy_test' || i
    );
  END LOOP;
END;
$$;

VACUUM;

\timing on

DO $$
BEGIN
  FOR i IN 1..100 LOOP
    EXECUTE FORMAT(
      'COPY %I FROM ''/home/john/pgtest/dump.out'' WITH csv',
      'copy_test' || i
    );
  END LOOP;
END;
$$;

\timing off