Hello,
Thank’s for helping me.
Here is the user function I try to schedule
create function user_job_remove_monthly_partition(IN job_id integer, IN config jsonb)
returns integer as rc
declare
rc integer;
BEGIN
rc := 0;
RAISE NOTICE ‘Executing action % with config %’, job_id, config;
PERFORM wrk.remove_monthly_partitions();
RETURN rc;
END;
rc LANGUAGE plpgsql;
It’s purely inspired by the documentation.
Then the stored procedure call by the perfom is doing so stuff with partitions.
It’s working fine and cannot failed due to usage of an exception block
then I’ve used
SELECT add_job(‘user_job_remove_monthly_partition’,‘1h’, config => ‘{“table_add_partition”:“dp_val15”}’ );
I use the following query to see the scheduling
SELECT *
FROM timescaledb_information.jobs j
, timescaledb_information.job_stats js
WHERE j.job_id=js.job_id;
and the result is
1007,User-Defined Action [1007],0 years 0 mons 0 days 1 hours 0 mins 0.0 secs,0 years 0 mons 0 days 0 hours 0 mins 0.0 secs,-1,0 years 0 mons 0 days 0 hours 5 mins 0.0 secs,public,user_job_create_monthly_partition,admindba,true,true,“{”“table_add_partition”": ““dp_val15"”}”,2024-11-12 15:16:51.020836 +00:00,2024-11-12 15:16:51.020836 +00:00,null,null,null,null,null,null,1007,-infinity,-infinity,null,Scheduled,null,2024-11-12 15:16:51.020836 +00:00,0,0,0
I can use run_job
creos_tsdb.public> CALL run_job(1007)
Executing user_job_create_monthly_partition with parameters {“table_add_partition”: “dp_val15”}
Executing action 1007 with config {“table_add_partition”: “dp_val15”}
EventTriggerInvoke 16958
EventTriggerInvoke 16958
CREATE TABLE / PRIMARY KEY will create implicit index “dp_val15_futur_pkey1” for table “dp_val15_futur”
building index “dp_val15_futur_pkey1” on table “dp_val15_futur” serially
index “dp_val15_futur_pkey1” can safely use deduplication
building index “dp_val15_futur_valueday_idx1” on table “dp_val15_futur” serially
index “dp_val15_futur_valueday_idx1” can safely use deduplication
building index “dp_val15_futur_profile_idx1” on table “dp_val15_futur” serially
index “dp_val15_futur_profile_idx1” can safely use deduplication
EventTriggerInvoke 16958
EventTriggerInvoke 16958
EventTriggerInvoke 16958
[2024-11-14 15:35:32] completed in 95 ms
SELECT * FROM timescaledb_information.job_errors
is empty
and the next_start is not scheduled.
Hope all this, could help to understand.