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-- Setup 6-- 7\c :TEST_DBNAME :ROLE_SUPERUSER 8CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(timeout INT = -1) RETURNS VOID 9AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 10CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_run(timeout INT = -1, mock_start_time INT = 0) RETURNS VOID 11AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 12CREATE OR REPLACE FUNCTION ts_bgw_db_scheduler_test_wait_for_scheduler_finish() RETURNS VOID 13AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 14CREATE OR REPLACE FUNCTION ts_bgw_params_create() RETURNS VOID 15AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 16CREATE OR REPLACE FUNCTION ts_bgw_params_destroy() RETURNS VOID 17AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 18CREATE OR REPLACE FUNCTION ts_bgw_params_reset_time(set_time BIGINT = 0, wait BOOLEAN = false) RETURNS VOID 19AS :MODULE_PATHNAME LANGUAGE C VOLATILE; 20\set WAIT_ON_JOB 0 21\set IMMEDIATELY_SET_UNTIL 1 22\set WAIT_FOR_OTHER_TO_ADVANCE 2 23-- Remove any default jobs, e.g., telemetry 24DELETE FROM _timescaledb_config.bgw_job; 25TRUNCATE _timescaledb_internal.bgw_job_stat; 26\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 27CREATE TABLE public.bgw_log( 28 msg_no INT, 29 mock_time BIGINT, 30 application_name TEXT, 31 msg TEXT 32); 33CREATE VIEW sorted_bgw_log AS 34SELECT msg_no, 35 mock_time, 36 application_name, 37 CASE WHEN length(msg) > 80 THEN 38 substring(msg, 1, 80) || '...' 39 ELSE 40 msg 41 END AS msg 42FROM bgw_log 43ORDER BY mock_time, 44 application_name COLLATE "C", 45 msg_no; 46CREATE TABLE public.bgw_dsm_handle_store( 47 handle BIGINT 48); 49INSERT INTO public.bgw_dsm_handle_store VALUES (0); 50SELECT ts_bgw_params_create(); 51 ts_bgw_params_create 52---------------------- 53 54(1 row) 55 56SELECT * FROM _timescaledb_config.bgw_job; 57 id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | hypertable_id | config 58----+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+---------------+-------- 59(0 rows) 60 61SELECT * FROM timescaledb_information.job_stats; 62 hypertable_schema | hypertable_name | job_id | last_run_started_at | last_successful_finish | last_run_status | job_status | last_run_duration | next_start | total_runs | total_successes | total_failures 63-------------------+-----------------+--------+---------------------+------------------------+-----------------+------------+-------------------+------------+------------+-----------------+---------------- 64(0 rows) 65 66\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 67------------------------------ 68-- test reorder policy runs -- 69------------------------------ 70CREATE TABLE test_reorder_table(time int, chunk_id int); 71SELECT create_hypertable('test_reorder_table', 'time', chunk_time_interval => 1); 72NOTICE: adding not-null constraint to column "time" 73 create_hypertable 74--------------------------------- 75 (1,public,test_reorder_table,t) 76(1 row) 77 78-- These inserts should create 5 different chunks 79INSERT INTO test_reorder_table VALUES (1, 1); 80INSERT INTO test_reorder_table VALUES (2, 2); 81INSERT INTO test_reorder_table VALUES (3, 3); 82INSERT INTO test_reorder_table VALUES (4, 4); 83INSERT INTO test_reorder_table VALUES (5, 5); 84SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_reorder_table'; 85 count 86------- 87 5 88(1 row) 89 90SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies'); 91 json_object_field 92------------------- 93 0 94(1 row) 95 96select add_reorder_policy('test_reorder_table', 'test_reorder_table_time_idx') as reorder_job_id \gset 97SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_reorder_policies'); 98 json_object_field 99------------------- 100 1 101(1 row) 102 103-- job was created 104SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 105 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 106--------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+-------------------------------------------------------------------+------------+-------------------+-------------------- 107 1000 | Reorder Policy [1000] | @ 4 days | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | {"index_name": "test_reorder_table_time_idx", "hypertable_id": 1} | | public | test_reorder_table 108(1 row) 109 110-- no stats 111SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 112 FROM _timescaledb_internal.bgw_job_stat 113 ORDER BY job_id; 114 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 115--------+------------+------------+------------------+------------+-----------------+----------------+--------------- 116(0 rows) 117 118-- nothing clustered 119SELECT indexrelid::regclass, indisclustered 120 FROM pg_index 121 WHERE indisclustered = true ORDER BY 1; 122 indexrelid | indisclustered 123------------+---------------- 124(0 rows) 125 126-- run first time 127SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25); 128 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 129------------------------------------------------------------ 130 131(1 row) 132 133SELECT * FROM sorted_bgw_log; 134 msg_no | mock_time | application_name | msg 135--------+-----------+------------------+-------------------------------------------- 136 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 137 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 138(2 rows) 139 140SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 141 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 142--------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+-------------------------------------------------------------------+------------------------------+-------------------+-------------------- 143 1000 | Reorder Policy [1000] | @ 4 days | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | {"index_name": "test_reorder_table_time_idx", "hypertable_id": 1} | Fri Dec 31 16:00:00 1999 PST | public | test_reorder_table 144(1 row) 145 146-- job ran once, successfully 147SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 148 FROM _timescaledb_internal.bgw_job_stat 149 where job_id=:reorder_job_id; 150 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 151--------+------------------------------+------------------------------+------------------+------------+-----------------+----------------+--------------- 152 1000 | Fri Dec 31 16:00:00 1999 PST | Fri Dec 31 16:00:00 1999 PST | t | 1 | 1 | 0 | 0 153(1 row) 154 155-- first chunk reordered 156SELECT indexrelid::regclass, indisclustered 157 FROM pg_index 158 WHERE indisclustered = true ORDER BY 1; 159 indexrelid | indisclustered 160--------------------------------------------------------------------+---------------- 161 _timescaledb_internal._hyper_1_1_chunk_test_reorder_table_time_idx | t 162(1 row) 163 164-- second call to scheduler should immediately run reorder again, due to catchup 165SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25); 166 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 167------------------------------------------------------------ 168 169(1 row) 170 171SELECT * FROM sorted_bgw_log; 172 msg_no | mock_time | application_name | msg 173--------+-----------+------------------+---------------------------------------------- 174 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 175 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 176 0 | 25000 | DB Scheduler | [TESTING] Registered new background worker 177 1 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 178(4 rows) 179 180SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 181 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 182--------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+-------------------------------------------------------------------+----------------------------------+-------------------+-------------------- 183 1000 | Reorder Policy [1000] | @ 4 days | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | {"index_name": "test_reorder_table_time_idx", "hypertable_id": 1} | Fri Dec 31 16:00:00.025 1999 PST | public | test_reorder_table 184(1 row) 185 186-- two runs 187SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 188 FROM _timescaledb_internal.bgw_job_stat 189 where job_id=:reorder_job_id; 190 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 191--------+----------------------------------+----------------------------------+------------------+------------+-----------------+----------------+--------------- 192 1000 | Fri Dec 31 16:00:00.025 1999 PST | Fri Dec 31 16:00:00.025 1999 PST | t | 2 | 2 | 0 | 0 193(1 row) 194 195-- two chunks clustered 196SELECT indexrelid::regclass, indisclustered 197 FROM pg_index 198 WHERE indisclustered = true ORDER BY 1; 199 indexrelid | indisclustered 200--------------------------------------------------------------------+---------------- 201 _timescaledb_internal._hyper_1_1_chunk_test_reorder_table_time_idx | t 202 _timescaledb_internal._hyper_1_2_chunk_test_reorder_table_time_idx | t 203(2 rows) 204 205-- third call to scheduler should immediately run reorder again, due to catchup 206SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(50); 207 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 208------------------------------------------------------------ 209 210(1 row) 211 212-- job info is gone 213SELECT * FROM sorted_bgw_log; 214 msg_no | mock_time | application_name | msg 215--------+-----------+------------------+----------------------------------------------- 216 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 217 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 218 0 | 25000 | DB Scheduler | [TESTING] Registered new background worker 219 1 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 220 0 | 50000 | DB Scheduler | [TESTING] Registered new background worker 221 1 | 50000 | DB Scheduler | [TESTING] Wait until 100000, started at 50000 222(6 rows) 223 224SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 225 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 226--------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+-------------------------------------------------------------------+---------------------------------+-------------------+-------------------- 227 1000 | Reorder Policy [1000] | @ 4 days | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | {"index_name": "test_reorder_table_time_idx", "hypertable_id": 1} | Tue Jan 04 16:00:00.05 2000 PST | public | test_reorder_table 228(1 row) 229 230SELECT * 231 FROM _timescaledb_internal.bgw_job_stat 232 where job_id=:reorder_job_id; 233 job_id | last_start | last_finish | next_start | last_successful_finish | last_run_success | total_runs | total_duration | total_successes | total_failures | total_crashes | consecutive_failures | consecutive_crashes 234--------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+------------------+------------+----------------+-----------------+----------------+---------------+----------------------+--------------------- 235 1000 | Fri Dec 31 16:00:00.05 1999 PST | Fri Dec 31 16:00:00.05 1999 PST | Tue Jan 04 16:00:00.05 2000 PST | Fri Dec 31 16:00:00.05 1999 PST | t | 3 | @ 0 | 3 | 0 | 0 | 0 | 0 236(1 row) 237 238-- three chunks clustered 239SELECT indexrelid::regclass, indisclustered 240 FROM pg_index 241 WHERE indisclustered = true ORDER BY 1; 242 indexrelid | indisclustered 243--------------------------------------------------------------------+---------------- 244 _timescaledb_internal._hyper_1_1_chunk_test_reorder_table_time_idx | t 245 _timescaledb_internal._hyper_1_2_chunk_test_reorder_table_time_idx | t 246 _timescaledb_internal._hyper_1_3_chunk_test_reorder_table_time_idx | t 247(3 rows) 248 249-- running is a nop 250SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(100); 251 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 252------------------------------------------------------------ 253 254(1 row) 255 256SELECT * FROM sorted_bgw_log; 257 msg_no | mock_time | application_name | msg 258--------+-----------+------------------+------------------------------------------------ 259 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 260 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 261 0 | 25000 | DB Scheduler | [TESTING] Registered new background worker 262 1 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 263 0 | 50000 | DB Scheduler | [TESTING] Registered new background worker 264 1 | 50000 | DB Scheduler | [TESTING] Wait until 100000, started at 50000 265 0 | 100000 | DB Scheduler | [TESTING] Wait until 200000, started at 100000 266(7 rows) 267 268SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 269 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 270--------+-----------------------+-------------------+-------------+-------------+--------------+-----------------------+----------------+-------------------+-----------+-------------------------------------------------------------------+---------------------------------+-------------------+-------------------- 271 1000 | Reorder Policy [1000] | @ 4 days | @ 0 | -1 | @ 5 mins | _timescaledb_internal | policy_reorder | default_perm_user | t | {"index_name": "test_reorder_table_time_idx", "hypertable_id": 1} | Tue Jan 04 16:00:00.05 2000 PST | public | test_reorder_table 272(1 row) 273 274SELECT * 275 FROM _timescaledb_internal.bgw_job_stat 276 where job_id=:reorder_job_id; 277 job_id | last_start | last_finish | next_start | last_successful_finish | last_run_success | total_runs | total_duration | total_successes | total_failures | total_crashes | consecutive_failures | consecutive_crashes 278--------+---------------------------------+---------------------------------+---------------------------------+---------------------------------+------------------+------------+----------------+-----------------+----------------+---------------+----------------------+--------------------- 279 1000 | Fri Dec 31 16:00:00.05 1999 PST | Fri Dec 31 16:00:00.05 1999 PST | Tue Jan 04 16:00:00.05 2000 PST | Fri Dec 31 16:00:00.05 1999 PST | t | 3 | @ 0 | 3 | 0 | 0 | 0 | 0 280(1 row) 281 282-- still have 3 chunks clustered 283SELECT indexrelid::regclass, indisclustered 284 FROM pg_index 285 WHERE indisclustered = true ORDER BY 1; 286 indexrelid | indisclustered 287--------------------------------------------------------------------+---------------- 288 _timescaledb_internal._hyper_1_1_chunk_test_reorder_table_time_idx | t 289 _timescaledb_internal._hyper_1_2_chunk_test_reorder_table_time_idx | t 290 _timescaledb_internal._hyper_1_3_chunk_test_reorder_table_time_idx | t 291(3 rows) 292 293--check that views work correctly 294SELECT * FROM timescaledb_information.job_stats; 295 hypertable_schema | hypertable_name | job_id | last_run_started_at | last_successful_finish | last_run_status | job_status | last_run_duration | next_start | total_runs | total_successes | total_failures 296-------------------+--------------------+--------+---------------------------------+---------------------------------+-----------------+------------+-------------------+---------------------------------+------------+-----------------+---------------- 297 public | test_reorder_table | 1000 | Fri Dec 31 16:00:00.05 1999 PST | Fri Dec 31 16:00:00.05 1999 PST | Success | Scheduled | | Tue Jan 04 16:00:00.05 2000 PST | 3 | 3 | 0 298(1 row) 299 300-- test deleting the policy 301SELECT remove_reorder_policy('test_reorder_table'); 302 remove_reorder_policy 303----------------------- 304 305(1 row) 306 307SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 308 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 309--------+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+--------+------------+-------------------+----------------- 310(0 rows) 311 312SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 313 FROM _timescaledb_internal.bgw_job_stat 314 where job_id=:reorder_job_id; 315 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 316--------+------------+------------+------------------+------------+-----------------+----------------+--------------- 317(0 rows) 318 319SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(125); 320 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 321------------------------------------------------------------ 322 323(1 row) 324 325SELECT * FROM sorted_bgw_log; 326 msg_no | mock_time | application_name | msg 327--------+-----------+------------------+------------------------------------------------ 328 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 329 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 330 0 | 25000 | DB Scheduler | [TESTING] Registered new background worker 331 1 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 332 0 | 50000 | DB Scheduler | [TESTING] Registered new background worker 333 1 | 50000 | DB Scheduler | [TESTING] Wait until 100000, started at 50000 334 0 | 100000 | DB Scheduler | [TESTING] Wait until 200000, started at 100000 335 0 | 200000 | DB Scheduler | [TESTING] Wait until 325000, started at 200000 336(8 rows) 337 338SELECT * FROM timescaledb_information.jobs WHERE job_id=:reorder_job_id; 339 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 340--------+------------------+-------------------+-------------+-------------+--------------+-------------+-----------+-------+-----------+--------+------------+-------------------+----------------- 341(0 rows) 342 343-- still only 3 chunks clustered 344SELECT indexrelid::regclass, indisclustered 345 FROM pg_index 346 WHERE indisclustered = true ORDER BY 1; 347 indexrelid | indisclustered 348--------------------------------------------------------------------+---------------- 349 _timescaledb_internal._hyper_1_1_chunk_test_reorder_table_time_idx | t 350 _timescaledb_internal._hyper_1_2_chunk_test_reorder_table_time_idx | t 351 _timescaledb_internal._hyper_1_3_chunk_test_reorder_table_time_idx | t 352(3 rows) 353 354\c :TEST_DBNAME :ROLE_SUPERUSER 355TRUNCATE bgw_log; 356TRUNCATE _timescaledb_internal.bgw_job_stat; 357DELETE FROM _timescaledb_config.bgw_job; 358SELECT ts_bgw_params_reset_time(); 359 ts_bgw_params_reset_time 360-------------------------- 361 362(1 row) 363 364\c :TEST_DBNAME :ROLE_DEFAULT_PERM_USER 365----------------------------------- 366-- test drop chunnks policy runs -- 367----------------------------------- 368CREATE TABLE test_drop_chunks_table(time timestamptz, drop_order int); 369SELECT create_hypertable('test_drop_chunks_table', 'time', chunk_time_interval => INTERVAL '1 week'); 370NOTICE: adding not-null constraint to column "time" 371 create_hypertable 372------------------------------------- 373 (2,public,test_drop_chunks_table,t) 374(1 row) 375 376-- These inserts should create 5 different chunks 377INSERT INTO test_drop_chunks_table VALUES (now() - INTERVAL '2 month', 4); 378INSERT INTO test_drop_chunks_table VALUES (now(), 5); 379INSERT INTO test_drop_chunks_table VALUES (now() - INTERVAL '6 months', 2); 380INSERT INTO test_drop_chunks_table VALUES (now() - INTERVAL '4 months', 3); 381INSERT INTO test_drop_chunks_table VALUES (now() - INTERVAL '8 months', 1); 382SELECT show_chunks('test_drop_chunks_table'); 383 show_chunks 384----------------------------------------- 385 _timescaledb_internal._hyper_2_6_chunk 386 _timescaledb_internal._hyper_2_7_chunk 387 _timescaledb_internal._hyper_2_8_chunk 388 _timescaledb_internal._hyper_2_9_chunk 389 _timescaledb_internal._hyper_2_10_chunk 390(5 rows) 391 392SELECT COUNT(*) FROM _timescaledb_catalog.chunk as c, _timescaledb_catalog.hypertable as ht where c.hypertable_id = ht.id and ht.table_name='test_drop_chunks_table'; 393 count 394------- 395 5 396(1 row) 397 398SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies'); 399 json_object_field 400------------------- 401 0 402(1 row) 403 404SELECT add_retention_policy('test_drop_chunks_table', INTERVAL '4 months') as drop_chunks_job_id \gset 405SELECT json_object_field(get_telemetry_report(always_display_report := true)::json,'num_retention_policies'); 406 json_object_field 407------------------- 408 1 409(1 row) 410 411SELECT alter_job(:drop_chunks_job_id, schedule_interval => INTERVAL '1 second'); 412 alter_job 413-------------------------------------------------------------------------------------------------------------- 414 (1001,"@ 1 sec","@ 5 mins",-1,"@ 5 mins",t,"{""drop_after"": ""@ 4 mons"", ""hypertable_id"": 2}",-infinity) 415(1 row) 416 417SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id; 418 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 419--------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+------------------------------------------------+------------+-------------------+------------------------ 420 1001 | Retention Policy [1001] | @ 1 sec | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | {"drop_after": "@ 4 mons", "hypertable_id": 2} | | public | test_drop_chunks_table 421(1 row) 422 423-- no stats 424SELECT job_id, next_start, last_finish as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 425 FROM _timescaledb_internal.bgw_job_stat 426 WHERE job_id=:drop_chunks_job_id; 427 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 428--------+------------+------------+------------------+------------+-----------------+----------------+--------------- 429(0 rows) 430 431-- all chunks are there 432SELECT show_chunks('test_drop_chunks_table'); 433 show_chunks 434----------------------------------------- 435 _timescaledb_internal._hyper_2_6_chunk 436 _timescaledb_internal._hyper_2_7_chunk 437 _timescaledb_internal._hyper_2_8_chunk 438 _timescaledb_internal._hyper_2_9_chunk 439 _timescaledb_internal._hyper_2_10_chunk 440(5 rows) 441 442-- run first time 443SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25); 444 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 445------------------------------------------------------------ 446 447(1 row) 448 449SELECT * FROM sorted_bgw_log; 450 msg_no | mock_time | application_name | msg 451--------+-----------+------------------+-------------------------------------------- 452 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 453 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 454(2 rows) 455 456SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id; 457 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 458--------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+------------------------------------------------+------------------------------+-------------------+------------------------ 459 1001 | Retention Policy [1001] | @ 1 sec | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | {"drop_after": "@ 4 mons", "hypertable_id": 2} | Fri Dec 31 16:00:01 1999 PST | public | test_drop_chunks_table 460(1 row) 461 462-- job ran once, successfully 463SELECT job_id, time_bucket('1m',next_start) AS next_start, time_bucket('1m',last_finish) as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 464 FROM _timescaledb_internal.bgw_job_stat 465 where job_id=:drop_chunks_job_id; 466 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 467--------+------------------------------+------------------------------+------------------+------------+-----------------+----------------+--------------- 468 1001 | Fri Dec 31 16:00:00 1999 PST | Fri Dec 31 16:00:00 1999 PST | t | 1 | 1 | 0 | 0 469(1 row) 470 471-- chunks 8 and 10 dropped 472SELECT show_chunks('test_drop_chunks_table'); 473 show_chunks 474---------------------------------------- 475 _timescaledb_internal._hyper_2_6_chunk 476 _timescaledb_internal._hyper_2_7_chunk 477 _timescaledb_internal._hyper_2_9_chunk 478(3 rows) 479 480-- job doesn't run again immediately 481SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(25); 482 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 483------------------------------------------------------------ 484 485(1 row) 486 487SELECT * FROM sorted_bgw_log; 488 msg_no | mock_time | application_name | msg 489--------+-----------+------------------+---------------------------------------------- 490 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 491 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 492 0 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 493(3 rows) 494 495SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id; 496 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 497--------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+------------------------------------------------+------------------------------+-------------------+------------------------ 498 1001 | Retention Policy [1001] | @ 1 sec | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | {"drop_after": "@ 4 mons", "hypertable_id": 2} | Fri Dec 31 16:00:01 1999 PST | public | test_drop_chunks_table 499(1 row) 500 501-- still only 1 run 502SELECT job_id, time_bucket('1m',next_start) AS next_start, time_bucket('1m',last_finish) as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 503 FROM _timescaledb_internal.bgw_job_stat 504 where job_id=:drop_chunks_job_id; 505 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 506--------+------------------------------+------------------------------+------------------+------------+-----------------+----------------+--------------- 507 1001 | Fri Dec 31 16:00:00 1999 PST | Fri Dec 31 16:00:00 1999 PST | t | 1 | 1 | 0 | 0 508(1 row) 509 510-- same chunks 511SELECT show_chunks('test_drop_chunks_table'); 512 show_chunks 513---------------------------------------- 514 _timescaledb_internal._hyper_2_6_chunk 515 _timescaledb_internal._hyper_2_7_chunk 516 _timescaledb_internal._hyper_2_9_chunk 517(3 rows) 518 519-- a new chunk older than the drop date will be dropped 520INSERT INTO test_drop_chunks_table VALUES (now() - INTERVAL '12 months', 0); 521SELECT show_chunks('test_drop_chunks_table'); 522 show_chunks 523----------------------------------------- 524 _timescaledb_internal._hyper_2_6_chunk 525 _timescaledb_internal._hyper_2_7_chunk 526 _timescaledb_internal._hyper_2_9_chunk 527 _timescaledb_internal._hyper_2_11_chunk 528(4 rows) 529 530SELECT ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish(10000); 531 ts_bgw_db_scheduler_test_run_and_wait_for_scheduler_finish 532------------------------------------------------------------ 533 534(1 row) 535 536SELECT * FROM sorted_bgw_log; 537 msg_no | mock_time | application_name | msg 538--------+-----------+------------------+--------------------------------------------------- 539 0 | 0 | DB Scheduler | [TESTING] Registered new background worker 540 1 | 0 | DB Scheduler | [TESTING] Wait until 25000, started at 0 541 0 | 25000 | DB Scheduler | [TESTING] Wait until 50000, started at 25000 542 0 | 50000 | DB Scheduler | [TESTING] Wait until 1000000, started at 50000 543 1 | 1000000 | DB Scheduler | [TESTING] Registered new background worker 544 2 | 1000000 | DB Scheduler | [TESTING] Wait until 10050000, started at 1000000 545(6 rows) 546 547SELECT * FROM timescaledb_information.jobs WHERE job_id=:drop_chunks_job_id; 548 job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | config | next_start | hypertable_schema | hypertable_name 549--------+-------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-------------------+-----------+------------------------------------------------+------------------------------+-------------------+------------------------ 550 1001 | Retention Policy [1001] | @ 1 sec | @ 5 mins | -1 | @ 5 mins | _timescaledb_internal | policy_retention | default_perm_user | t | {"drop_after": "@ 4 mons", "hypertable_id": 2} | Fri Dec 31 16:00:02 1999 PST | public | test_drop_chunks_table 551(1 row) 552 553-- 2 runs 554SELECT job_id, time_bucket('1m',next_start) AS next_start, time_bucket('1m',last_finish) as until_next, last_run_success, total_runs, total_successes, total_failures, total_crashes 555 FROM _timescaledb_internal.bgw_job_stat 556 where job_id=:drop_chunks_job_id; 557 job_id | next_start | until_next | last_run_success | total_runs | total_successes | total_failures | total_crashes 558--------+------------------------------+------------------------------+------------------+------------+-----------------+----------------+--------------- 559 1001 | Fri Dec 31 16:00:00 1999 PST | Fri Dec 31 16:00:00 1999 PST | t | 2 | 2 | 0 | 0 560(1 row) 561 562SELECT show_chunks('test_drop_chunks_table'); 563 show_chunks 564---------------------------------------- 565 _timescaledb_internal._hyper_2_6_chunk 566 _timescaledb_internal._hyper_2_7_chunk 567 _timescaledb_internal._hyper_2_9_chunk 568(3 rows) 569 570--test that views work 571SELECT * FROM timescaledb_information.job_stats; 572 hypertable_schema | hypertable_name | job_id | last_run_started_at | last_successful_finish | last_run_status | job_status | last_run_duration | next_start | total_runs | total_successes | total_failures 573-------------------+------------------------+--------+------------------------------+------------------------------+-----------------+------------+-------------------+------------------------------+------------+-----------------+---------------- 574 public | test_drop_chunks_table | 1001 | Fri Dec 31 16:00:01 1999 PST | Fri Dec 31 16:00:01 1999 PST | Success | Scheduled | | Fri Dec 31 16:00:02 1999 PST | 2 | 2 | 0 575(1 row) 576 577