User-Define-Action - Job are not executed

1

Hello,

I’m currently using user define action, following the documentation and jobs are not automatically executed.
call run_job(job_id) is working but then no next_start value is calculated.

I have also tried
SELECT _timescaledb_functions.start_background_workers();
as suggested in documentation but it returns “false” and I don’t see any info on what could be checked.
Any suggestion is welcome

2

Hi @Marco90 , there’s a timescaledb_information.job_stats and you can also use job_errors view to check what’s going on.

Have you called the add_job function?

3

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.

4

It says scheduled as true. So, in theory it should continue. I remember I fall into an error trying to make a job that recurrent schedules itself and that doesn’t work.

Any change of job metadata inside the job function worth remembering that will be replaced by the default function that changes the job after run.

As a next step, Can you fill an issue on the official timescaledb GitHub with minimal reproducible steps so the core team can take a look on it?

5

Thanks,

This will be a bit complicated amongst my other tasks. I will work on the subject next week.

1 Like
6

If you need some help to bootstrap it, use GitHub - timescale/templates: Templates to get started with Timescale on Finance or Sensors (IoT).