fsm-copy-test.sql

DROP TABLE copy_test;

CREATE UNLOGGED TABLE copy_test (
    value int
) WITH (fillfactor=10, autovacuum_enabled=false);

-- Fill a few pages
DO $$
DECLARE curtid tid;
BEGIN
  LOOP
    INSERT INTO copy_test VALUES (1) RETURNING ctid INTO curtid;
    EXIT WHEN curtid > tid '(3, 0)';  -- 3,7,11,15
  END LOOP;
END;
$$;

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


DO $$
BEGIN
RAISE NOTICE 'Creating tables';
  FOR i IN 1..100 LOOP
    EXECUTE FORMAT(
      'CREATE UNLOGGED TABLE IF NOT EXISTS %I (LIKE copy_test) WITH (fillfactor=10, autovacuum_enabled=false)',
      'copy_test' || i
    );
  END LOOP;
END;
$$;

DO $$
BEGIN
RAISE NOTICE 'Truncating tables';
  FOR i IN 1..100 LOOP
    EXECUTE FORMAT(
      'TRUNCATE TABLE %I',
      'copy_test' || i
    );
  END LOOP;
END;
$$;


\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