test.sql

text/plain

Download

-- 1. prepare
-- pgbench -i -s --partition=3

-- 2. check functions which has regclass arguments

-- prewarm
CREATE EXTENSION IF NOT EXISTS pg_prewarm;

SELECT pg_prewarm('pgbench_accounts');
SELECT pg_prewarm('pgbench_accounts_1');
SELECT pg_prewarm('pgbench_accounts_pkey');
SELECT pg_prewarm('pgbench_accounts_1_pkey');

-- amcheck
CREATE EXTENSION IF NOT EXISTS amcheck;
SELECT bt_index_check('pgbench_accounts'::regclass, true, true);
SELECT bt_index_check('pgbench_accounts_1'::regclass, true, true);
SELECT bt_index_check('pgbench_accounts_pkey'::regclass, true, true);
SELECT bt_index_check('pgbench_accounts_1_pkey'::regclass, true, true);

SELECT bt_index_parent_check('pgbench_accounts'::regclass, true, true);
SELECT bt_index_parent_check('pgbench_accounts_1'::regclass, true, true);
SELECT bt_index_parent_check('pgbench_accounts_pkey'::regclass, true, true);
SELECT bt_index_parent_check('pgbench_accounts_1_pkey'::regclass, true, true);

SELECT gin_index_check('pgbench_accounts'::regclass);
SELECT gin_index_check('pgbench_accounts_1'::regclass);
SELECT gin_index_check('pgbench_accounts_pkey'::regclass);
SELECT gin_index_check('pgbench_accounts_1_pkey'::regclass);

SELECT verify_heapam('pgbench_accounts'::regclass);
SELECT verify_heapam('pgbench_accounts_1'::regclass);
SELECT verify_heapam('pgbench_accounts_pkey'::regclass);
SELECT verify_heapam('pgbench_accounts_1_pkey'::regclass);

-- brin
SELECT brin_summarize_new_values('pgbench_accounts'::regclass);
SELECT brin_summarize_new_values('pgbench_accounts_1'::regclass);
SELECT brin_summarize_new_values('pgbench_accounts_pkey'::regclass);
SELECT brin_summarize_new_values('pgbench_accounts_1_pkey'::regclass);

SELECT brin_summarize_range('pgbench_accounts'::regclass, 1);
SELECT brin_summarize_range('pgbench_accounts_1'::regclass, 1);
SELECT brin_summarize_range('pgbench_accounts_pkey'::regclass, 1);
SELECT brin_summarize_range('pgbench_accounts_1_pkey'::regclass, 1);

SELECT brin_desummarize_range('pgbench_accounts'::regclass, 1);
SELECT brin_desummarize_range('pgbench_accounts_1'::regclass, 1);
SELECT brin_desummarize_range('pgbench_accounts_pkey'::regclass, 1);
SELECT brin_desummarize_range('pgbench_accounts_1_pkey'::regclass, 1);

-- pageinspect
CREATE EXTENSION IF NOT EXISTS pageinspect;

SELECT tuple_data_split(
  'pgbench_accounts'::regclass,
  t_data, t_infomask, t_infomask2, t_bits
)
FROM heap_page_items(get_raw_page('pgbench_accounts_1', 2)) LIMIT 1;
SELECT tuple_data_split(
  'pgbench_accounts_1'::regclass,
  t_data, t_infomask, t_infomask2, t_bits
)
FROM heap_page_items(get_raw_page('pgbench_accounts_1', 2)) LIMIT 1;
SELECT tuple_data_split(
  'pgbench_accounts_pkey'::regclass,
  t_data, t_infomask, t_infomask2, t_bits
)
FROM heap_page_items(get_raw_page('pgbench_accounts_1', 2)) LIMIT 1;
SELECT tuple_data_split(
  'pgbench_accounts_1_pkey'::regclass,
  t_data, t_infomask, t_infomask2, t_bits
)
FROM heap_page_items(get_raw_page('pgbench_accounts_1', 2)) LIMIT 1;

SELECT * FROM heap_page_item_attrs(
  get_raw_page('pgbench_accounts', 0),
  'pgbench_accounts'::regclass
) LIMIT 1;
SELECT * FROM heap_page_item_attrs(
  get_raw_page('pgbench_accounts_1', 0),
  'pgbench_accounts_1'::regclass
) LIMIT 1;
SELECT * FROM heap_page_item_attrs(
  get_raw_page('pgbench_accounts_pkey', 0),
  'pgbench_accounts_pkey'::regclass
) LIMIT 1;
SELECT * FROM heap_page_item_attrs(
  get_raw_page('pgbench_accounts_1_pkey', 0),
  'pgbench_accounts_1_pkey'::regclass
) LIMIT 1;

SELECT * FROM gist_page_items(
  get_raw_page('pgbench_accounts', 1),
  'pgbench_accounts'::regclass
);
SELECT * FROM gist_page_items(
  get_raw_page('pgbench_accounts_1', 1),
  'pgbench_accounts_1'::regclass
);
SELECT * FROM gist_page_items(
  get_raw_page('pgbench_accounts_pkey', 1),
  'pgbench_accounts_pkey'::regclass
);
SELECT * FROM gist_page_items(
  get_raw_page('pgbench_accounts_1_pkey', 1),
  'pgbench_accounts_1_pkey'::regclass
);

-- pgstattuple
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('pgbench_accounts');
SELECT * FROM pgstattuple('pgbench_accounts_1');
SELECT * FROM pgstattuple('pgbench_accounts_pkey');
SELECT * FROM pgstattuple('pgbench_accounts_1_pkey');

SELECT * FROM pgstatindex('pgbench_accounts_pkey');
SELECT * FROM pgstatindex('pgbench_accounts_1_pkey');
SELECT * FROM pgstatindex('pgbench_accounts');
SELECT * FROM pgstatindex('pgbench_accounts_1');

SELECT * FROM pgstatginindex('pgbench_accounts_pkey');
SELECT * FROM pgstatginindex('pgbench_accounts_1_pkey');
SELECT * FROM pgstatginindex('pgbench_accounts');
SELECT * FROM pgstatginindex('pgbench_accounts_1');

SELECT * FROM pgstathashindex('pgbench_accounts_pkey');
SELECT * FROM pgstathashindex('pgbench_accounts_1_pkey');
SELECT * FROM pgstathashindex('pgbench_accounts');
SELECT * FROM pgstathashindex('pgbench_accounts_1');

SELECT pg_relpages('pgbench_accounts');
SELECT pg_relpages('pgbench_accounts_1');
SELECT pg_relpages('pgbench_accounts_pkey');
SELECT pg_relpages('pgbench_accounts_1_pkey');

SELECT * FROM pgstattuple_approx('pgbench_accounts');
SELECT * FROM pgstattuple_approx('pgbench_accounts_1');
SELECT * FROM pgstattuple_approx('pgbench_accounts_pkey');
SELECT * FROM pgstattuple_approx('pgbench_accounts_1_pkey');

-- pg_surgery
CREATE EXTENSION IF NOT EXISTS pg_surgery;
SELECT heap_force_kill('pgbench_accounts', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_kill('pgbench_accounts_1', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_kill('pgbench_accounts_pkey', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_kill('pgbench_accounts_1_pkey', ARRAY['(0, 1)']::tid[]);

SELECT heap_force_freeze('pgbench_accounts', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_freeze('pgbench_accounts_1', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_freeze('pgbench_accounts_pkey', ARRAY['(0, 1)']::tid[]);
SELECT heap_force_freeze('pgbench_accounts_1_pkey', ARRAY['(0, 1)']::tid[]);

-- pg_visibility
CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT * FROM pg_visibility_map('pgbench_accounts') LIMIT 1;
SELECT * FROM pg_visibility_map('pgbench_accounts_1') LIMIT 1;
SELECT * FROM pg_visibility_map('pgbench_accounts_pkey') LIMIT 1;
SELECT * FROM pg_visibility_map('pgbench_accounts_1_pkey') LIMIT 1;

SELECT * FROM pg_visibility('pgbench_accounts') LIMIT 1;
SELECT * FROM pg_visibility('pgbench_accounts_1') LIMIT 1;
SELECT * FROM pg_visibility('pgbench_accounts_pkey') LIMIT 1;
SELECT * FROM pg_visibility('pgbench_accounts_1_pkey') LIMIT 1;

SELECT * FROM pg_visibility_map_summary('pgbench_accounts');
SELECT * FROM pg_visibility_map_summary('pgbench_accounts_1');
SELECT * FROM pg_visibility_map_summary('pgbench_accounts_pkey');
SELECT * FROM pg_visibility_map_summary('pgbench_accounts_1_pkey');

SELECT * FROM pg_check_frozen('pgbench_accounts');
SELECT * FROM pg_check_frozen('pgbench_accounts_1');
SELECT * FROM pg_check_frozen('pgbench_accounts_pkey');
SELECT * FROM pg_check_frozen('pgbench_accounts_1_pkey');

SELECT * FROM pg_check_visible('pgbench_accounts');
SELECT * FROM pg_check_visible('pgbench_accounts_1');
SELECT * FROM pg_check_visible('pgbench_accounts_pkey');
SELECT * FROM pg_check_visible('pgbench_accounts_1_pkey');

SELECT pg_truncate_visibility_map('pgbench_accounts');
SELECT pg_truncate_visibility_map('pgbench_accounts_1');
SELECT pg_truncate_visibility_map('pgbench_accounts_pkey');
SELECT pg_truncate_visibility_map('pgbench_accounts_1_pkey');