Configuration check function for custom actions

[mkindahl]

Background

a custom action is added using add_job or altered using alter_job and there is a problem with the configuration, it will not generate an error until the job executes. This applies both to custom action types and to native action types.

For example, prior to #2689, the following would not generate an error despite the fact that the configuration is missing a mat_hypertable_id field.

SELECT add_continuous_aggregate_policy('mat_m1', NULL, 2::integer, '12 h'::interval) AS job_id \gset
SELECT alter_job(:job_id, config => '{"end_offset": "1 week", "start_offset": "2 weeks"}');

The same applies if you use a bad type for one of the fields in the configuration:

SELECT alter_job(:job_id,
       config => '{"mat_hypertable_id": "2", "end_offset": "chicken", "start_offset": "1 week"}');

This was fixed in #2689 by adding hard-coded checks to the native actions, but there is no similar checking available for custom actions. One option is to add custom check functions similar to how it was done in #2681, but it might be necessary to add other extensions to the custom action API to better support integrity of the custom actions.

Specification

A check function is added to the jobs that accepts a JSONB structure being the configuration. The function is expected to raise an error for any issues in the configuration, and do nothing if the configuration is good.

Function add_job

Extend the add_job function with one extra parameter check_config being the procedure to execute for checking the configuration.

Parameters

Parameter	Type	Default
proc	REGPROC
schedule_interval	INTERVAL
config	JSONB	NULL
initial_start	TIMESTAMPTZ	NULL
scheduled	BOOL	true
scheduled	BOOL	true
check_config	REGPROC	NULL

Return Value

The job id as an integer

Function alter_job

Extend the alter_job function with a new parameter check_config. If defined, the check function will be executed after the job configuration has been updated.

Parameters

Parameter	Type	Default
job_id	INTEGER
schedule_interval	INTERVAL	NULL
max_runtime	INTERVAL	NULL
max_retries	INTEGER	NULL
retry_period	INTERVAL	NULL
scheduled	BOOL	NULL
config	JSONB	NULL
next_start	TIMESTAMPTZ	NULL
if_exists	BOOL	FALSE
check_config	REGPROC	NULL

Return value

A tuple with the columns below.

Name	Type
job_id	INTEGER
schedule_interval	INTERVAL
max_runtime	INTERVAL
max_retries	INTEGER
retry_period	INTERVAL
scheduled	BOOL
config	JSONB
next_start	TIMESTAMPTZ
check_config	TEXT

Public view timescaledb_information.jobs

Two new columns are added last, which are read from the internal table bgw_job

Name	Type	Default
check_schema	NAME	NULL
check_table	NAME	NULL

Internal table bgw_job

Two new colums are added (last) for each job

Name	Type	Default
check_schema	NAME	NULL
check_table	NAME	NULL

Internal check functions for existing policies

The following functions are added for the current policies.

• _timescaledb_internal.policy_retention_check
• _timescaledb_internal.policy_reorder_check
• _timescaledb_internal.policy_compression_check
• _timescaledb_internal.policy_refresh_continuous_aggregate_check