1-- This file and its contents are licensed under the Timescale License. 2-- Please see the included NOTICE for copyright information and 3-- LICENSE-TIMESCALE for a copy of the license. 4 5\c :TEST_DBNAME :ROLE_SUPERUSER 6SET timescaledb.license_key='CommunityLicense'; 7 8CREATE OR REPLACE FUNCTION ts_test_chunk_stats_insert(job_id INTEGER, chunk_id INTEGER, num_times_run INTEGER, last_time_run TIMESTAMPTZ = NULL) RETURNS VOID 9AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE; 10 11\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 12 13SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 14 15CREATE TABLE test_table(time timestamptz, junk int); 16CREATE TABLE test_table_int(time bigint, junk int); 17 18SELECT create_hypertable('test_table', 'time'); 19SELECT create_hypertable('test_table_int', 'time', chunk_time_interval => 1); 20 21CREATE INDEX second_index on test_table (time); 22CREATE INDEX third_index on test_table (time); 23 24select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 25-- Noop for duplicate policy 26select add_reorder_policy('test_table', 'test_table_time_idx', true); 27select add_reorder_policy('test_table', 'second_index', true); 28select add_reorder_policy('test_table', 'third_index', true); 29 30\set ON_ERROR_STOP 0 31-- Error whenever incorrect arguments are applied (must have table and index) 32select add_reorder_policy('test_table', 'bad_index'); 33select add_reorder_policy('test_table', ''); 34select add_reorder_policy('test_table'); 35 36select add_reorder_policy('test_table', 'second_index'); 37select add_reorder_policy('test_table', 'third_index'); 38select add_reorder_policy(NULL, 'third_index'); 39select add_reorder_policy(2, 'third_index'); 40\set ON_ERROR_STOP 1 41 42select * from _timescaledb_config.bgw_job where id=:job_id; 43 44-- Now check that default scheduling interval for reorder policy is calculated correctly 45-- Should be 1/2 default chunk interval length 46CREATE TABLE test_table2(time timestamptz, junk int); 47SELECT create_hypertable('test_table2', 'time', chunk_time_interval=>INTERVAL '1 day'); 48select add_reorder_policy('test_table2', 'test_table2_time_idx'); 49 50SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 51 52DROP TABLE test_table2; 53-- Make sure that test_table2 reorder policy gets dropped 54SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 55 56-- Error whenever incorrect arguments are applied (must have table and interval) 57\set ON_ERROR_STOP 0 58select add_retention_policy(); 59select add_retention_policy('test_table'); 60select add_retention_policy(INTERVAL '3 hours'); 61select add_retention_policy('test_table', INTERVAL 'haha'); 62select add_retention_policy('test_table', 'haha'); 63select add_retention_policy('test_table', 42); 64select add_retention_policy('fake_table', INTERVAL '3 month'); 65\set ON_ERROR_STOP 1 66 67select add_retention_policy('test_table', INTERVAL '3 month', true); 68-- Should not add new policy with different parameters 69select add_retention_policy('test_table', INTERVAL '3 month', true); 70select add_retention_policy('test_table', INTERVAL '1 year', if_not_exists => true); 71select add_retention_policy('test_table', INTERVAL '3 days', if_not_exists => true); 72select add_retention_policy('test_table', INTERVAL '3 days', if_not_exists => true); 73 74SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 75 76\set ON_ERROR_STOP 0 77select add_retention_policy('test_table', INTERVAL '1 year'); 78select add_retention_policy('test_table', INTERVAL '3 days'); 79\set ON_ERROR_STOP 1 80 81SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 82 83select remove_retention_policy('test_table'); 84 85-- Test set_integer_now_func and add_retention_policy with 86-- hypertables that have integer time dimension 87 88select * from _timescaledb_catalog.dimension; 89\c :TEST_DBNAME :ROLE_SUPERUSER 90 91CREATE SCHEMA IF NOT EXISTS my_new_schema; 92create or replace function my_new_schema.dummy_now2() returns BIGINT LANGUAGE SQL IMMUTABLE as 'SELECT 1::BIGINT'; 93grant execute on ALL FUNCTIONS IN SCHEMA my_new_schema to public; 94select set_integer_now_func('test_table_int', 'my_new_schema.dummy_now2'); 95 96\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 97select * from _timescaledb_catalog.dimension; 98 99SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 100select remove_reorder_policy('test_table'); 101 102SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 103 104select add_retention_policy('test_table', INTERVAL '3 month'); 105SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 106select remove_retention_policy('test_table'); 107SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 108 109select add_retention_policy('test_table_int', 1); 110SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 111-- Should not add new policy with different parameters 112select add_retention_policy('test_table_int', 2, true); 113 114select remove_retention_policy('test_table_int'); 115SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 116 117-- Make sure remove works when there's nothing to remove 118select remove_retention_policy('test_table', true); 119select remove_reorder_policy('test_table', true); 120 121\set ON_ERROR_STOP 0 122select remove_retention_policy(); 123select remove_retention_policy('fake_table'); 124select remove_reorder_policy(); 125select remove_reorder_policy('fake_table'); 126\set ON_ERROR_STOP 1 127 128\set ON_ERROR_STOP 0 129-- This should be noop 130select remove_reorder_policy(2, true); 131-- Fail with error message 132select remove_reorder_policy(2); 133\set ON_ERROR_STOP 1 134 135-- Now make sure policy args have correct job deletion dependency 136SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 137 138select add_retention_policy('test_table', INTERVAL '2 month') as job_id \gset 139select add_reorder_policy('test_table', 'third_index') as reorder_job_id \gset 140 141select count(*) from _timescaledb_config.bgw_job where id=:job_id; 142select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 143 144select delete_job(:job_id); 145 146select count(*) from _timescaledb_config.bgw_job where id=:job_id; 147-- Job config should still be there 148select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 149 150select delete_job(:reorder_job_id); 151select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 152 153-- Now make sure policy args have correct job deletion dependency 154select add_retention_policy('test_table', INTERVAL '2 month') as job_id \gset 155select add_reorder_policy('test_table', 'third_index') as reorder_job_id \gset 156 157select count(*) from _timescaledb_config.bgw_job where id=:job_id; 158select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 159SELECT * FROM _timescaledb_config.bgw_job ORDER BY id; 160 161DROP TABLE test_table; 162 163select count(*) from _timescaledb_config.bgw_job where id=:job_id; 164select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 165 166-- Check that we can't add policies on non-hypertables 167CREATE TABLE non_hypertable(junk int, more_junk int); 168CREATE INDEX non_ht_index on non_hypertable(junk); 169 170\set ON_ERROR_STOP 0 171select add_retention_policy('non_hypertable', INTERVAL '2 month'); 172select add_reorder_policy('non_hypertable', 'non_ht_index'); 173\set ON_ERROR_STOP 1 174 175-- Now make sure things work with multiple policies on multiple hypertables 176CREATE TABLE test_table(time timestamptz, junk int); 177SELECT create_hypertable('test_table', 'time'); 178CREATE INDEX second_index on test_table (time); 179 180CREATE TABLE test_table2(time timestamptz, junk int); 181SELECT create_hypertable('test_table2', 'time'); 182CREATE INDEX junk_index on test_table2 (junk); 183 184select add_retention_policy('test_table', INTERVAL '2 days'); 185select add_retention_policy('test_table2', INTERVAL '1 days'); 186 187SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 188 189DROP TABLE test_table; 190DROP TABLE test_table_int; 191 192SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 193 194DROP TABLE test_table2; 195 196SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 197 198-- Now test chunk_stat insertion 199select ts_test_chunk_stats_insert(123, 123, 45); 200select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 201 202\c :TEST_DBNAME :ROLE_SUPERUSER 203TRUNCATE _timescaledb_internal.bgw_policy_chunk_stats; 204\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 205 206-- Now test chunk_stat deletion is correct 207select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 208 209CREATE TABLE test_table(time timestamptz, junk int); 210SELECT create_hypertable('test_table', 'time'); 211CREATE INDEX second_index on test_table (time); 212 213insert into test_table values (now(), 1); 214insert into test_table values (now() - INTERVAL '5 weeks', 123); 215 216select c.id from _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as h where c.hypertable_id=h.id and h.table_name='test_table' ORDER BY c.id; 217 218select c.id as chunk_id from _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as h where c.hypertable_id=h.id and h.table_name='test_table' ORDER BY c.id LIMIT 1 \gset 219 220select add_reorder_policy('test_table', 'second_index') as job_id \gset 221-- Simulate reorder job running and setting this stat row 222select ts_test_chunk_stats_insert(:job_id, :chunk_id, 1); 223select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 224SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 225 226-- Deleting a chunk that has nothing to do with the job should do nothing 227select c.table_name as other_chunk_name,c.schema_name as other_chunk_schema from _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as h where c.id != :chunk_id \gset 228select concat(:'other_chunk_schema','.',:'other_chunk_name') as other_chunk \gset 229 230DROP TABLE :other_chunk; 231 232select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 233SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 234 235-- Dropping the hypertable should drop the chunk, which should drop the reorder policy 236DROP TABLE test_table; 237select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 238SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 239 240-- Now check dropping a job will drop the chunk_stat row 241CREATE TABLE test_table(time timestamptz, junk int); 242SELECT create_hypertable('test_table', 'time'); 243 244select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 245select add_retention_policy('test_table', INTERVAL '2 days', true); 246 247select ts_test_chunk_stats_insert(:job_id, 123, 1); 248select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 249SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 250 251-- Dropping the drop_chunks job should not affect the chunk_stats row 252select remove_retention_policy('test_table'); 253select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 254SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 255 256select remove_reorder_policy('test_table'); 257-- Row should be gone 258select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 259SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 260 261-- Now test if alter_job works 262select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 263 select * from _timescaledb_config.bgw_job where id=:job_id; 264-- No change 265select * from alter_job(:job_id); 266-- Changes expected 267select * from alter_job(:job_id, INTERVAL '3 years', INTERVAL '5 min', 5, INTERVAL '123 sec'); 268select * from alter_job(:job_id, INTERVAL '123 years'); 269select * from alter_job(:job_id, retry_period => INTERVAL '33 hours'); 270select * from alter_job(:job_id, max_runtime => INTERVAL '456 sec'); 271select * from alter_job(:job_id, max_retries => 0); 272select * from alter_job(:job_id, max_retries => -1); 273select * from alter_job(:job_id, max_retries => 20); 274 275-- No change 276select * from alter_job(:job_id, max_runtime => NULL); 277select * from alter_job(:job_id, max_retries => NULL); 278 279--change schedule_interval when bgw_job_stat does not exist 280select * from alter_job(:job_id, schedule_interval=>'1 min'); 281select count(*) = 0 from _timescaledb_internal.bgw_job_stat where job_id = :job_id; 282--set next_start when bgw_job_stat does not exist 283select * from alter_job(:job_id, next_start=>'2001-01-01 01:01:01'); 284--change schedule_interval when no last_finish set 285select * from alter_job(:job_id, schedule_interval=>'10 min'); 286--next_start overrides any schedule_interval changes 287select * from alter_job(:job_id, schedule_interval=>'20 min', next_start=>'2002-01-01 01:01:01'); 288 289--set the last_finish manually 290\c :TEST_DBNAME :ROLE_SUPERUSER 291UPDATE _timescaledb_internal.bgw_job_stat SET last_finish = '2003-01-01:01:01:01' WHERE job_id = :job_id; 292\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 293 294--not changing the interval doesn't change the next_start 295select * from alter_job(:job_id, schedule_interval=>'20 min'); 296--changing the interval changes next_start 297select * from alter_job(:job_id, schedule_interval=>'30 min'); 298--explicit next start overrides. 299select * from alter_job(:job_id, schedule_interval=>'40 min', next_start=>'2004-01-01 01:01:01'); 300--test pausing 301select * from alter_job(:job_id, next_start=>'infinity'); 302--test that you can use now() to unpause 303select next_start = now() from alter_job(:job_id, next_start=>now()); 304--test pausing/resuming via scheduled parameter 305select job_id from alter_job(:job_id, scheduled=>false); 306select job_status, next_start from timescaledb_information.job_stats where job_id = :job_id; 307select job_id from alter_job(:job_id, scheduled=>true); 308select job_status from timescaledb_information.job_stats where job_id = :job_id; 309 310\set ON_ERROR_STOP 0 311-- negative infinity disallowed (used as special value) 312select * from alter_job(:job_id, next_start=>'-infinity'); 313-- index should exist 314select * from alter_job(:job_id, 315 config => '{"index_name": "non-existent", "hypertable_id": 7}'); 316-- index should be an index on the hypertable 317select * from alter_job(:job_id, 318 config => '{"index_name": "non_ht_index", "hypertable_id": 7}'); 319-- hypertable should exist 320select * from alter_job(:job_id, 321 config => '{"index_name": "test_table_time_idx", "hypertable_id": 47}'); 322\set ON_ERROR_STOP 1 323 324-- Check if_exists boolean works correctly 325select * from alter_job(1234, if_exists => TRUE); 326 327\set ON_ERROR_STOP 0 328select * from alter_job(1234); 329\set ON_ERROR_STOP 1 330 331select remove_reorder_policy('test_table'); 332 333\c :TEST_DBNAME :ROLE_SUPERUSER 334set session timescaledb.license_key='Community'; 335 336-- Test for failure cases 337\set ON_ERROR_STOP 0 338select alter_job(12345); 339\set ON_ERROR_STOP 1 340 341select add_reorder_policy('test_table', 'test_table_time_idx') as reorder_job_id \gset 342select add_retention_policy('test_table', INTERVAL '4 months', true) as drop_chunks_job_id \gset 343 344\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2 345\set ON_ERROR_STOP 0 346select from alter_job(:reorder_job_id, max_runtime => NULL); 347select from alter_job(:drop_chunks_job_id, max_runtime => NULL); 348\set ON_ERROR_STOP 1 349