1-- ########## TIME DAILY TESTS NATIVE PROCEDURE ########## 2-- Other tests: native, default out of bounds data, partition and undo with procedures instead of functions 3 4\set ON_ERROR_STOP true 5 6SELECT set_config('search_path','partman, public',false); 7 8SELECT plan(16); 9CREATE SCHEMA partman_test; 10 11CREATE TABLE partman_test.time_taptest_table_source ( 12 col1 bigserial 13 , col2 text 14 , col3 timestamptz DEFAULT now() NOT NULL); 15 16INSERT INTO partman_test.time_taptest_table_source (col3) 17VALUES (generate_series(CURRENT_TIMESTAMP - '9 days'::interval, CURRENT_TIMESTAMP, '1 day'::interval)); 18 19CREATE TABLE partman_test.time_taptest_table_target ( 20 col1 bigserial 21 , col2 text 22 , col3 timestamptz DEFAULT now() NOT NULL); 23 24 25CREATE TABLE partman_test.time_taptest_table ( 26 col1 bigserial 27 , col2 text 28 , col3 timestamptz DEFAULT now() NOT NULL 29) PARTITION BY RANGE (col3); 30CREATE INDEX ON partman_test.time_taptest_table(col3); 31 32CREATE TABLE partman_test.template_time_taptest_table (LIKE partman_test.time_taptest_table INCLUDING ALL); 33 34SELECT has_table('partman_test', 'template_time_taptest_table', 'Check that template table was made'); 35 36 37SELECT create_parent('partman_test.time_taptest_table', 'col3', 'native', 'daily', p_template_table := 'partman_test.template_time_taptest_table'); 38 39SELECT has_table('partman_test', 'time_taptest_table_default', 'Check time_taptest_table_default exists'); 40SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD'), 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP, 'YYYY_MM_DD')||' exists'); 41SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM_DD'), 42 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM_DD')||' exists'); 43SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM_DD'), 44 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'2 days'::interval, 'YYYY_MM_DD')||' exists'); 45SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD'), 46 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'3 days'::interval, 'YYYY_MM_DD')||' exists'); 47SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'4 days'::interval, 'YYYY_MM_DD'), 48 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'4 days'::interval, 'YYYY_MM_DD')||' exists'); 49SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'5 days'::interval, 'YYYY_MM_DD'), 50 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'5 days'::interval, 'YYYY_MM_DD')||' does not exist'); 51SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM_DD'), 52 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'1 day'::interval, 'YYYY_MM_DD')||' exists'); 53SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM_DD'), 54 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'2 days'::interval, 'YYYY_MM_DD')||' exists'); 55SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM_DD'), 56 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'3 days'::interval, 'YYYY_MM_DD')||' exists'); 57SELECT has_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'4 days'::interval, 'YYYY_MM_DD'), 58 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'4 days'::interval, 'YYYY_MM_DD')||' exists'); 59SELECT hasnt_table('partman_test', 'time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'5 days'::interval, 'YYYY_MM_DD'), 60 'Check time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'5 days'::interval, 'YYYY_MM_DD')||' does not exist'); 61 62-- Check for duped indexes since it was created on both the parent and the template 63SELECT is_empty($$SELECT key 64 FROM (SELECT indexrelid::regclass AS idx 65 , (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) AS KEY FROM pg_index 66 WHERE indrelid = format('partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'1 day'::interval, 'YYYY_MM_DD'))::regclass) sub 67 GROUP BY key 68 HAVING count(*)>1$$ 69 , 'Check that table does not have duped index'); 70 71SELECT is_empty($$SELECT key 72 FROM (SELECT indexrelid::regclass AS idx 73 , (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) AS KEY FROM pg_index 74 WHERE indrelid = format('partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP+'4 day'::interval, 'YYYY_MM_DD'))::regclass) sub 75 GROUP BY key 76 HAVING count(*)>1$$ 77 , 'Check that table does not have duped index'); 78 79 80SELECT is_empty($$SELECT key 81 FROM (SELECT indexrelid::regclass AS idx 82 , (indrelid::text ||E'\n'|| indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'')||E'\n' || coalesce(indpred::text,'')) AS KEY FROM pg_index 83 WHERE indrelid = format('partman_test.time_taptest_table_p'||to_char(CURRENT_TIMESTAMP-'4 day'::interval, 'YYYY_MM_DD'))::regclass) sub 84 GROUP BY key 85 HAVING count(*)>1$$ 86 , 'Check that table does not have duped index'); 87 88 89SELECT diag('!!! In separate psql terminal, please run the following (adjusting schema if needed): "CALL partman.partition_data_proc(''partman_test.time_taptest_table'', p_wait := 0, p_source_table := ''partman_test.time_taptest_table_source'');".'); 90SELECT diag('!!! After that, run part2 of this script to check result !!!'); 91 92SELECT * FROM finish(); 93