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\c :TEST_DBNAME :ROLE_SUPERUSER 5SET timescaledb.license_key='CommunityLicense'; 6CREATE 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 7AS :TSL_MODULE_PATHNAME LANGUAGE C VOLATILE; 8\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 9SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 10 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 11----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 12(0 rows) 13 14CREATE TABLE test_table(time timestamptz, junk int); 15CREATE TABLE test_table_int(time bigint, junk int); 16SELECT create_hypertable('test_table', 'time'); 17NOTICE: adding not-null constraint to column "time" 18 create_hypertable 19------------------------- 20 (1,public,test_table,t) 21(1 row) 22 23SELECT create_hypertable('test_table_int', 'time', chunk_time_interval => 1); 24NOTICE: adding not-null constraint to column "time" 25 create_hypertable 26----------------------------- 27 (2,public,test_table_int,t) 28(1 row) 29 30CREATE INDEX second_index on test_table (time); 31CREATE INDEX third_index on test_table (time); 32select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 33-- Noop for duplicate policy 34select add_reorder_policy('test_table', 'test_table_time_idx', true); 35NOTICE: reorder policy already exists on hypertable "test_table", skipping 36 add_reorder_policy 37-------------------- 38 -1 39(1 row) 40 41select add_reorder_policy('test_table', 'second_index', true); 42WARNING: reorder policy already exists for hypertable "test_table" 43 add_reorder_policy 44-------------------- 45 -1 46(1 row) 47 48select add_reorder_policy('test_table', 'third_index', true); 49WARNING: reorder policy already exists for hypertable "test_table" 50 add_reorder_policy 51-------------------- 52 -1 53(1 row) 54 55\set ON_ERROR_STOP 0 56-- Error whenever incorrect arguments are applied (must have table and index) 57select add_reorder_policy('test_table', 'bad_index'); 58ERROR: invalid reorder index 59select add_reorder_policy('test_table', ''); 60ERROR: invalid reorder index 61select add_reorder_policy('test_table'); 62ERROR: function add_reorder_policy(unknown) does not exist at character 8 63select add_reorder_policy('test_table', 'second_index'); 64ERROR: reorder policy already exists for hypertable "test_table" 65select add_reorder_policy('test_table', 'third_index'); 66ERROR: reorder policy already exists for hypertable "test_table" 67select add_reorder_policy(NULL, 'third_index'); 68 add_reorder_policy 69-------------------- 70 71(1 row) 72 73select add_reorder_policy(2, 'third_index'); 74ERROR: OID 2 does not refer to a table 75\set ON_ERROR_STOP 1 76select * from _timescaledb_config.bgw_job where id=:job_id; 77 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 78------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+----------------------------------------------------------- 79 1000 | Reorder Policy [1000] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 1 | {"index_name": "test_table_time_idx", "hypertable_id": 1} 80(1 row) 81 82-- Now check that default scheduling interval for reorder policy is calculated correctly 83-- Should be 1/2 default chunk interval length 84CREATE TABLE test_table2(time timestamptz, junk int); 85SELECT create_hypertable('test_table2', 'time', chunk_time_interval=>INTERVAL '1 day'); 86NOTICE: adding not-null constraint to column "time" 87 create_hypertable 88-------------------------- 89 (3,public,test_table2,t) 90(1 row) 91 92select add_reorder_policy('test_table2', 'test_table2_time_idx'); 93 add_reorder_policy 94-------------------- 95 1001 96(1 row) 97 98SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 99 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 100------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+------------------------------------------------------------ 101 1000 | Reorder Policy [1000] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 1 | {"index_name": "test_table_time_idx", "hypertable_id": 1} 102 1001 | Reorder Policy [1001] | @ 12 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 3 | {"index_name": "test_table2_time_idx", "hypertable_id": 3} 103(2 rows) 104 105DROP TABLE test_table2; 106-- Make sure that test_table2 reorder policy gets dropped 107SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 108 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 109------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+----------------------------------------------------------- 110 1000 | Reorder Policy [1000] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 1 | {"index_name": "test_table_time_idx", "hypertable_id": 1} 111(1 row) 112 113-- Error whenever incorrect arguments are applied (must have table and interval) 114\set ON_ERROR_STOP 0 115select add_retention_policy(); 116ERROR: function add_retention_policy() does not exist at character 8 117select add_retention_policy('test_table'); 118ERROR: function add_retention_policy(unknown) does not exist at character 8 119select add_retention_policy(INTERVAL '3 hours'); 120ERROR: function add_retention_policy(interval) does not exist at character 8 121select add_retention_policy('test_table', INTERVAL 'haha'); 122ERROR: invalid input syntax for type interval: "haha" at character 52 123select add_retention_policy('test_table', 'haha'); 124ERROR: invalid value for parameter drop_after 125select add_retention_policy('test_table', 42); 126ERROR: invalid value for parameter drop_after 127select add_retention_policy('fake_table', INTERVAL '3 month'); 128ERROR: relation "fake_table" does not exist at character 29 129\set ON_ERROR_STOP 1 130select add_retention_policy('test_table', INTERVAL '3 month', true); 131 add_retention_policy 132---------------------- 133 1002 134(1 row) 135 136-- Should not add new policy with different parameters 137select add_retention_policy('test_table', INTERVAL '3 month', true); 138NOTICE: retention policy already exists for hypertable "test_table", skipping 139 add_retention_policy 140---------------------- 141 -1 142(1 row) 143 144select add_retention_policy('test_table', INTERVAL '1 year', if_not_exists => true); 145WARNING: retention policy already exists for hypertable "test_table" 146 add_retention_policy 147---------------------- 148 -1 149(1 row) 150 151select add_retention_policy('test_table', INTERVAL '3 days', if_not_exists => true); 152WARNING: retention policy already exists for hypertable "test_table" 153 add_retention_policy 154---------------------- 155 -1 156(1 row) 157 158select add_retention_policy('test_table', INTERVAL '3 days', if_not_exists => true); 159WARNING: retention policy already exists for hypertable "test_table" 160 add_retention_policy 161---------------------- 162 -1 163(1 row) 164 165SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 166 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 167------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+------------------------------------------------ 168 1002 | Retention Policy [1002] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 1 | {"drop_after": "@ 3 mons", "hypertable_id": 1} 169(1 row) 170 171\set ON_ERROR_STOP 0 172select add_retention_policy('test_table', INTERVAL '1 year'); 173ERROR: retention policy already exists for hypertable "test_table" 174select add_retention_policy('test_table', INTERVAL '3 days'); 175ERROR: retention policy already exists for hypertable "test_table" 176\set ON_ERROR_STOP 1 177SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 178 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 179------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+------------------------------------------------ 180 1002 | Retention Policy [1002] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 1 | {"drop_after": "@ 3 mons", "hypertable_id": 1} 181(1 row) 182 183select remove_retention_policy('test_table'); 184 remove_retention_policy 185------------------------- 186 187(1 row) 188 189-- Test set_integer_now_func and add_retention_policy with 190-- hypertables that have integer time dimension 191select * from _timescaledb_catalog.dimension; 192 id | hypertable_id | column_name | column_type | aligned | num_slices | partitioning_func_schema | partitioning_func | interval_length | integer_now_func_schema | integer_now_func 193----+---------------+-------------+--------------------------+---------+------------+--------------------------+-------------------+-----------------+-------------------------+------------------ 194 1 | 1 | time | timestamp with time zone | t | | | | 604800000000 | | 195 2 | 2 | time | bigint | t | | | | 1 | | 196(2 rows) 197 198\c :TEST_DBNAME :ROLE_SUPERUSER 199CREATE SCHEMA IF NOT EXISTS my_new_schema; 200create or replace function my_new_schema.dummy_now2() returns BIGINT LANGUAGE SQL IMMUTABLE as 'SELECT 1::BIGINT'; 201grant execute on ALL FUNCTIONS IN SCHEMA my_new_schema to public; 202select set_integer_now_func('test_table_int', 'my_new_schema.dummy_now2'); 203 set_integer_now_func 204---------------------- 205 206(1 row) 207 208\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 209select * from _timescaledb_catalog.dimension; 210 id | hypertable_id | column_name | column_type | aligned | num_slices | partitioning_func_schema | partitioning_func | interval_length | integer_now_func_schema | integer_now_func 211----+---------------+-------------+--------------------------+---------+------------+--------------------------+-------------------+-----------------+-------------------------+------------------ 212 1 | 1 | time | timestamp with time zone | t | | | | 604800000000 | | 213 2 | 2 | time | bigint | t | | | | 1 | my_new_schema | dummy_now2 214(2 rows) 215 216SELECT * FROM _timescaledb_config.bgw_job WHERE proc_name = 'policy_retention' ORDER BY id; 217 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 218----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 219(0 rows) 220 221select remove_reorder_policy('test_table'); 222 remove_reorder_policy 223----------------------- 224 225(1 row) 226 227SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 228 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 229----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 230(0 rows) 231 232select add_retention_policy('test_table', INTERVAL '3 month'); 233 add_retention_policy 234---------------------- 235 1003 236(1 row) 237 238SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 239 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 240------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+------------------------------------------------ 241 1003 | Retention Policy [1003] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 1 | {"drop_after": "@ 3 mons", "hypertable_id": 1} 242(1 row) 243 244select remove_retention_policy('test_table'); 245 remove_retention_policy 246------------------------- 247 248(1 row) 249 250SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 251 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 252----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 253(0 rows) 254 255select add_retention_policy('test_table_int', 1); 256 add_retention_policy 257---------------------- 258 1004 259(1 row) 260 261SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 262 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 263------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+--------------------------------------- 264 1004 | Retention Policy [1004] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 2 | {"drop_after": 1, "hypertable_id": 2} 265(1 row) 266 267-- Should not add new policy with different parameters 268select add_retention_policy('test_table_int', 2, true); 269WARNING: retention policy already exists for hypertable "test_table_int" 270 add_retention_policy 271---------------------- 272 -1 273(1 row) 274 275select remove_retention_policy('test_table_int'); 276 remove_retention_policy 277------------------------- 278 279(1 row) 280 281SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 282 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 283----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 284(0 rows) 285 286-- Make sure remove works when there's nothing to remove 287select remove_retention_policy('test_table', true); 288NOTICE: retention policy not found for hypertable "test_table", skipping 289 remove_retention_policy 290------------------------- 291 292(1 row) 293 294select remove_reorder_policy('test_table', true); 295NOTICE: reorder policy not found for hypertable "test_table", skipping 296 remove_reorder_policy 297----------------------- 298 299(1 row) 300 301\set ON_ERROR_STOP 0 302select remove_retention_policy(); 303ERROR: function remove_retention_policy() does not exist at character 8 304select remove_retention_policy('fake_table'); 305ERROR: relation "fake_table" does not exist at character 32 306select remove_reorder_policy(); 307ERROR: function remove_reorder_policy() does not exist at character 8 308select remove_reorder_policy('fake_table'); 309ERROR: relation "fake_table" does not exist at character 30 310\set ON_ERROR_STOP 1 311\set ON_ERROR_STOP 0 312-- This should be noop 313select remove_reorder_policy(2, true); 314ERROR: OID 2 does not refer to a table 315-- Fail with error message 316select remove_reorder_policy(2); 317ERROR: OID 2 does not refer to a table 318\set ON_ERROR_STOP 1 319-- Now make sure policy args have correct job deletion dependency 320SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 321 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 322----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 323(0 rows) 324 325select add_retention_policy('test_table', INTERVAL '2 month') as job_id \gset 326select add_reorder_policy('test_table', 'third_index') as reorder_job_id \gset 327select count(*) from _timescaledb_config.bgw_job where id=:job_id; 328 count 329------- 330 1 331(1 row) 332 333select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 334 count 335------- 336 1 337(1 row) 338 339select delete_job(:job_id); 340 delete_job 341------------ 342 343(1 row) 344 345select count(*) from _timescaledb_config.bgw_job where id=:job_id; 346 count 347------- 348 0 349(1 row) 350 351-- Job config should still be there 352select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 353 count 354------- 355 1 356(1 row) 357 358select delete_job(:reorder_job_id); 359 delete_job 360------------ 361 362(1 row) 363 364select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 365 count 366------- 367 0 368(1 row) 369 370-- Now make sure policy args have correct job deletion dependency 371select add_retention_policy('test_table', INTERVAL '2 month') as job_id \gset 372select add_reorder_policy('test_table', 'third_index') as reorder_job_id \gset 373select count(*) from _timescaledb_config.bgw_job where id=:job_id; 374 count 375------- 376 1 377(1 row) 378 379select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 380 count 381------- 382 1 383(1 row) 384 385SELECT * FROM _timescaledb_config.bgw_job ORDER BY id; 386 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 387------+-------------------------+-------------------+-----------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+--------------------------------------------------- 388 1 | Telemetry Reporter [1] | @ 24 hours | @ 1 min 40 secs | -1 | @ 1 hour | _timescaledb_internal | policy_telemetry | super_user | t | | 389 1007 | Retention Policy [1007] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 1 | {"drop_after": "@ 2 mons", "hypertable_id": 1} 390 1008 | Reorder Policy [1008] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 1 | {"index_name": "third_index", "hypertable_id": 1} 391(3 rows) 392 393DROP TABLE test_table; 394select count(*) from _timescaledb_config.bgw_job where id=:job_id; 395 count 396------- 397 0 398(1 row) 399 400select count(*) from _timescaledb_config.bgw_job where id=:reorder_job_id; 401 count 402------- 403 0 404(1 row) 405 406-- Check that we can't add policies on non-hypertables 407CREATE TABLE non_hypertable(junk int, more_junk int); 408CREATE INDEX non_ht_index on non_hypertable(junk); 409\set ON_ERROR_STOP 0 410select add_retention_policy('non_hypertable', INTERVAL '2 month'); 411ERROR: "non_hypertable" is not a hypertable or a continuous aggregate 412select add_reorder_policy('non_hypertable', 'non_ht_index'); 413ERROR: table "non_hypertable" is not a hypertable 414\set ON_ERROR_STOP 1 415-- Now make sure things work with multiple policies on multiple hypertables 416CREATE TABLE test_table(time timestamptz, junk int); 417SELECT create_hypertable('test_table', 'time'); 418NOTICE: adding not-null constraint to column "time" 419 create_hypertable 420------------------------- 421 (4,public,test_table,t) 422(1 row) 423 424CREATE INDEX second_index on test_table (time); 425CREATE TABLE test_table2(time timestamptz, junk int); 426SELECT create_hypertable('test_table2', 'time'); 427NOTICE: adding not-null constraint to column "time" 428 create_hypertable 429-------------------------- 430 (5,public,test_table2,t) 431(1 row) 432 433CREATE INDEX junk_index on test_table2 (junk); 434select add_retention_policy('test_table', INTERVAL '2 days'); 435 add_retention_policy 436---------------------- 437 1009 438(1 row) 439 440select add_retention_policy('test_table2', INTERVAL '1 days'); 441 add_retention_policy 442---------------------- 443 1010 444(1 row) 445 446SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 447 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 448------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+------------------------------------------------ 449 1009 | Retention Policy [1009] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 4 | {"drop_after": "@ 2 days", "hypertable_id": 4} 450 1010 | Retention Policy [1010] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 5 | {"drop_after": "@ 1 day", "hypertable_id": 5} 451(2 rows) 452 453DROP TABLE test_table; 454DROP TABLE test_table_int; 455SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 456 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 457------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+----------------------------------------------- 458 1010 | Retention Policy [1010] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 5 | {"drop_after": "@ 1 day", "hypertable_id": 5} 459(1 row) 460 461DROP TABLE test_table2; 462SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 463 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 464----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 465(0 rows) 466 467-- Now test chunk_stat insertion 468select ts_test_chunk_stats_insert(123, 123, 45); 469 ts_test_chunk_stats_insert 470---------------------------- 471 472(1 row) 473 474select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 475 job_id | chunk_id | num_times_job_run 476--------+----------+------------------- 477 123 | 123 | 45 478(1 row) 479 480\c :TEST_DBNAME :ROLE_SUPERUSER 481TRUNCATE _timescaledb_internal.bgw_policy_chunk_stats; 482\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 483-- Now test chunk_stat deletion is correct 484select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 485 job_id | chunk_id | num_times_job_run 486--------+----------+------------------- 487(0 rows) 488 489CREATE TABLE test_table(time timestamptz, junk int); 490SELECT create_hypertable('test_table', 'time'); 491NOTICE: adding not-null constraint to column "time" 492 create_hypertable 493------------------------- 494 (6,public,test_table,t) 495(1 row) 496 497CREATE INDEX second_index on test_table (time); 498insert into test_table values (now(), 1); 499insert into test_table values (now() - INTERVAL '5 weeks', 123); 500select 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; 501 id 502---- 503 1 504 2 505(2 rows) 506 507select 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 508select add_reorder_policy('test_table', 'second_index') as job_id \gset 509-- Simulate reorder job running and setting this stat row 510select ts_test_chunk_stats_insert(:job_id, :chunk_id, 1); 511 ts_test_chunk_stats_insert 512---------------------------- 513 514(1 row) 515 516select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 517 job_id | chunk_id | num_times_job_run 518--------+----------+------------------- 519 1011 | 1 | 1 520(1 row) 521 522SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 523 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 524------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+---------------------------------------------------- 525 1011 | Reorder Policy [1011] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 6 | {"index_name": "second_index", "hypertable_id": 6} 526(1 row) 527 528-- Deleting a chunk that has nothing to do with the job should do nothing 529select 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 530select concat(:'other_chunk_schema','.',:'other_chunk_name') as other_chunk \gset 531DROP TABLE :other_chunk; 532select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 533 job_id | chunk_id | num_times_job_run 534--------+----------+------------------- 535 1011 | 1 | 1 536(1 row) 537 538SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 539 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 540------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+---------------------------------------------------- 541 1011 | Reorder Policy [1011] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 6 | {"index_name": "second_index", "hypertable_id": 6} 542(1 row) 543 544-- Dropping the hypertable should drop the chunk, which should drop the reorder policy 545DROP TABLE test_table; 546select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 547 job_id | chunk_id | num_times_job_run 548--------+----------+------------------- 549(0 rows) 550 551SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 552 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 553----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 554(0 rows) 555 556-- Now check dropping a job will drop the chunk_stat row 557CREATE TABLE test_table(time timestamptz, junk int); 558SELECT create_hypertable('test_table', 'time'); 559NOTICE: adding not-null constraint to column "time" 560 create_hypertable 561------------------------- 562 (7,public,test_table,t) 563(1 row) 564 565select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 566select add_retention_policy('test_table', INTERVAL '2 days', true); 567 add_retention_policy 568---------------------- 569 1013 570(1 row) 571 572select ts_test_chunk_stats_insert(:job_id, 123, 1); 573 ts_test_chunk_stats_insert 574---------------------------- 575 576(1 row) 577 578select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 579 job_id | chunk_id | num_times_job_run 580--------+----------+------------------- 581 1012 | 123 | 1 582(1 row) 583 584SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 585 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 586------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+---------------+----------------------------------------------------------- 587 1012 | Reorder Policy [1012] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 7 | {"index_name": "test_table_time_idx", "hypertable_id": 7} 588 1013 | Retention Policy [1013] | @ 1 day | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | 7 | {"drop_after": "@ 2 days", "hypertable_id": 7} 589(2 rows) 590 591-- Dropping the drop_chunks job should not affect the chunk_stats row 592select remove_retention_policy('test_table'); 593 remove_retention_policy 594------------------------- 595 596(1 row) 597 598select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 599 job_id | chunk_id | num_times_job_run 600--------+----------+------------------- 601 1012 | 123 | 1 602(1 row) 603 604SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 605 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 606------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+----------------------------------------------------------- 607 1012 | Reorder Policy [1012] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 7 | {"index_name": "test_table_time_idx", "hypertable_id": 7} 608(1 row) 609 610select remove_reorder_policy('test_table'); 611 remove_reorder_policy 612----------------------- 613 614(1 row) 615 616-- Row should be gone 617select job_id,chunk_id,num_times_job_run from _timescaledb_internal.bgw_policy_chunk_stats; 618 job_id | chunk_id | num_times_job_run 619--------+----------+------------------- 620(0 rows) 621 622SELECT * FROM _timescaledb_config.bgw_job WHERE id >= 1000 ORDER BY id; 623 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 624----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 625(0 rows) 626 627-- Now test if alter_job works 628select add_reorder_policy('test_table', 'test_table_time_idx') as job_id \gset 629 select * from _timescaledb_config.bgw_job where id=:job_id; 630 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 631------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+---------------+----------------------------------------------------------- 632 1014 | Reorder Policy [1014] | @ 84 hours | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | 7 | {"index_name": "test_table_time_idx", "hypertable_id": 7} 633(1 row) 634 635-- No change 636select * from alter_job(:job_id); 637 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 638--------+-------------------+-------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 639 1014 | @ 84 hours | @ 0 | -1 | @ 5 mins | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 640(1 row) 641 642-- Changes expected 643select * from alter_job(:job_id, INTERVAL '3 years', INTERVAL '5 min', 5, INTERVAL '123 sec'); 644 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 645--------+-------------------+-------------+-------------+-----------------+-----------+-----------------------------------------------------------+------------ 646 1014 | @ 3 years | @ 5 mins | 5 | @ 2 mins 3 secs | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 647(1 row) 648 649select * from alter_job(:job_id, INTERVAL '123 years'); 650 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 651--------+-------------------+-------------+-------------+-----------------+-----------+-----------------------------------------------------------+------------ 652 1014 | @ 123 years | @ 5 mins | 5 | @ 2 mins 3 secs | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 653(1 row) 654 655select * from alter_job(:job_id, retry_period => INTERVAL '33 hours'); 656 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 657--------+-------------------+-------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 658 1014 | @ 123 years | @ 5 mins | 5 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 659(1 row) 660 661select * from alter_job(:job_id, max_runtime => INTERVAL '456 sec'); 662 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 663--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 664 1014 | @ 123 years | @ 7 mins 36 secs | 5 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 665(1 row) 666 667select * from alter_job(:job_id, max_retries => 0); 668 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 669--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 670 1014 | @ 123 years | @ 7 mins 36 secs | 0 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 671(1 row) 672 673select * from alter_job(:job_id, max_retries => -1); 674 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 675--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 676 1014 | @ 123 years | @ 7 mins 36 secs | -1 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 677(1 row) 678 679select * from alter_job(:job_id, max_retries => 20); 680 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 681--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 682 1014 | @ 123 years | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 683(1 row) 684 685-- No change 686select * from alter_job(:job_id, max_runtime => NULL); 687 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 688--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 689 1014 | @ 123 years | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 690(1 row) 691 692select * from alter_job(:job_id, max_retries => NULL); 693 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 694--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 695 1014 | @ 123 years | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 696(1 row) 697 698--change schedule_interval when bgw_job_stat does not exist 699select * from alter_job(:job_id, schedule_interval=>'1 min'); 700 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 701--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 702 1014 | @ 1 min | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 703(1 row) 704 705select count(*) = 0 from _timescaledb_internal.bgw_job_stat where job_id = :job_id; 706 ?column? 707---------- 708 t 709(1 row) 710 711--set next_start when bgw_job_stat does not exist 712select * from alter_job(:job_id, next_start=>'2001-01-01 01:01:01'); 713 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 714--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------------------------ 715 1014 | @ 1 min | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | Mon Jan 01 01:01:01 2001 PST 716(1 row) 717 718--change schedule_interval when no last_finish set 719select * from alter_job(:job_id, schedule_interval=>'10 min'); 720 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 721--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 722 1014 | @ 10 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | -infinity 723(1 row) 724 725--next_start overrides any schedule_interval changes 726select * from alter_job(:job_id, schedule_interval=>'20 min', next_start=>'2002-01-01 01:01:01'); 727 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 728--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------------------------ 729 1014 | @ 20 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | Tue Jan 01 01:01:01 2002 PST 730(1 row) 731 732--set the last_finish manually 733\c :TEST_DBNAME :ROLE_SUPERUSER 734UPDATE _timescaledb_internal.bgw_job_stat SET last_finish = '2003-01-01:01:01:01' WHERE job_id = :job_id; 735\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 736--not changing the interval doesn't change the next_start 737select * from alter_job(:job_id, schedule_interval=>'20 min'); 738 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 739--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------------------------ 740 1014 | @ 20 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | Tue Jan 01 01:01:01 2002 PST 741(1 row) 742 743--changing the interval changes next_start 744select * from alter_job(:job_id, schedule_interval=>'30 min'); 745 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 746--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------------------------ 747 1014 | @ 30 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | Wed Jan 01 01:31:01 2003 PST 748(1 row) 749 750--explicit next start overrides. 751select * from alter_job(:job_id, schedule_interval=>'40 min', next_start=>'2004-01-01 01:01:01'); 752 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 753--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------------------------ 754 1014 | @ 40 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | Thu Jan 01 01:01:01 2004 PST 755(1 row) 756 757--test pausing 758select * from alter_job(:job_id, next_start=>'infinity'); 759 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 760--------+-------------------+------------------+-------------+--------------+-----------+-----------------------------------------------------------+------------ 761 1014 | @ 40 mins | @ 7 mins 36 secs | 20 | @ 33 hours | t | {"index_name": "test_table_time_idx", "hypertable_id": 7} | infinity 762(1 row) 763 764--test that you can use now() to unpause 765select next_start = now() from alter_job(:job_id, next_start=>now()); 766 ?column? 767---------- 768 t 769(1 row) 770 771--test pausing/resuming via scheduled parameter 772select job_id from alter_job(:job_id, scheduled=>false); 773 job_id 774-------- 775 1014 776(1 row) 777 778select job_status, next_start from timescaledb_information.job_stats where job_id = :job_id; 779 job_status | next_start 780------------+------------ 781 Paused | 782(1 row) 783 784select job_id from alter_job(:job_id, scheduled=>true); 785 job_id 786-------- 787 1014 788(1 row) 789 790select job_status from timescaledb_information.job_stats where job_id = :job_id; 791 job_status 792------------ 793 Scheduled 794(1 row) 795 796\set ON_ERROR_STOP 0 797-- negative infinity disallowed (used as special value) 798select * from alter_job(:job_id, next_start=>'-infinity'); 799ERROR: cannot set next start to -infinity 800-- index should exist 801select * from alter_job(:job_id, 802 config => '{"index_name": "non-existent", "hypertable_id": 7}'); 803ERROR: reorder index not found 804-- index should be an index on the hypertable 805select * from alter_job(:job_id, 806 config => '{"index_name": "non_ht_index", "hypertable_id": 7}'); 807ERROR: invalid reorder index 808-- hypertable should exist 809select * from alter_job(:job_id, 810 config => '{"index_name": "test_table_time_idx", "hypertable_id": 47}'); 811ERROR: configuration hypertable id 47 not found 812\set ON_ERROR_STOP 1 813-- Check if_exists boolean works correctly 814select * from alter_job(1234, if_exists => TRUE); 815NOTICE: job 1234 not found, skipping 816 job_id | schedule_interval | max_runtime | max_retries | retry_period | scheduled | config | next_start 817--------+-------------------+-------------+-------------+--------------+-----------+--------+------------ 818 | | | | | | | 819(1 row) 820 821\set ON_ERROR_STOP 0 822select * from alter_job(1234); 823ERROR: job 1234 not found 824\set ON_ERROR_STOP 1 825select remove_reorder_policy('test_table'); 826 remove_reorder_policy 827----------------------- 828 829(1 row) 830 831\c :TEST_DBNAME :ROLE_SUPERUSER 832set session timescaledb.license_key='Community'; 833-- Test for failure cases 834\set ON_ERROR_STOP 0 835select alter_job(12345); 836ERROR: job 12345 not found 837\set ON_ERROR_STOP 1 838select add_reorder_policy('test_table', 'test_table_time_idx') as reorder_job_id \gset 839select add_retention_policy('test_table', INTERVAL '4 months', true) as drop_chunks_job_id \gset 840\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER_2 841\set ON_ERROR_STOP 0 842select from alter_job(:reorder_job_id, max_runtime => NULL); 843ERROR: insufficient permissions to alter job 1015 844select from alter_job(:drop_chunks_job_id, max_runtime => NULL); 845ERROR: insufficient permissions to alter job 1016 846\set ON_ERROR_STOP 1 847