fsm-copy-test-v3.sql

-- 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 with a fillfactor of 20 (30) can fit 44 (67) of these records; leave room for "0" tuple
    -- EXIT WHEN curtid >= tid '(3, 43)';
    -- EXIT WHEN curtid >= tid '(7, 43)';
    -- EXIT WHEN curtid >= tid '(3, 66)';
    EXIT WHEN curtid >= tid '(7, 66)';
    num = num + 1;
  END LOOP;
END;
$$;

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

DO $$
BEGIN
--RAISE NOTICE 'Delete all but one tuple';
  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