Why does my scheduled job not work?

TimescaleDB and PostgreSQL
1

Hi, I have some trouble creating a scheduled job. I have the following stored procedure:

CREATE OR REPLACE PROCEDURE delete_events()
LANGUAGE plpgsql
AS
$$
DECLARE
  l_rec record;
  l_sql text;
BEGIN
  FOR l_rec IN SELECT table_schema, TABLE_NAME
               FROM information_schema.tables
               WHERE TABLE_NAME = 'log_win'
                 AND table_schema LIKE '%org_%'
  LOOP 
    l_sql := format('WITH summary AS (
    SELECT time, device_id, ROW_NUMBER() OVER (PARTITION BY device_id
										    ORDER BY time DESC) AS rank
    FROM %I.log_win
    JOIN %I.device USING (device_id)
)
DELETE FROM %I.log_win 
USING summary
WHERE summary.rank = 1000 AND log_win.time < summary.time AND summary.device_id = 
log_win.device_id', l_rec.table_schema, l_rec.table_schema, l_rec.table_schema);
  EXECUTE l_sql;
  END LOOP;
END
$$;

What the procedure does is simply iterate through all schemas in my TimescaleDB and trimm the data. I can envoke the procedure by using CALL delete_events() and it works exactly as I want it to.

Now the issue I have is that I want my TimescaleDB to envoke this every 5 minues as a scheduled job. According to the documentation, this can be done in the following way:

SELECT add_job('delete_events','5m');

When I do this it says that the query is run successfully and it even returns an add_job id. However my data is never trimmed, so the stored procedure is not getting invoked by this job.

So I tried to run the job manually: CALL run_job(1108) , it gives the following error: ERROR: function public.delete_events(integer, jsonb) does not exist .

I don’t understand what the problem is and what I need to do to resolve it?

2

Hi @YBenarab welcome to the forum.

A quick question and I may be wildly wrong here, but is there a ; missing from the last but one line?
i.e. should be END; rather than END ?

After note: just found this PostgreSQL: Documentation: 16: 43.2. Structure of PL/pgSQL

however the final END that concludes a function body does not require a semicolon.

3

Hi @LorraineP and thank you.

The END does not need a semicolon, as there is a semicolon after the $$ that comes right after.

The procedure works fine when I call it directly CALL delete_events().

1 Like
4

@YBenarab,

TimescaleDB User-Defined Actions require that the signature of the stored procedure match what is expected. Specifically, you must have the two required inputs, even if your procedure code doesn’t use them. The runtime will always query for a procedure with your specified name and the two parameters.

To fix it in your current setup, you’ll need to delete the stored procedure you currently have (to avoid confusion mostly) and then recreate it using the following opening signature.

(Note: I haven’t gone line-by-line through your code so I’ll assume it does work, it’s just not getting called because of the definition.)

  1. Remove the scheduled job: SELECT delete_job(1008);
  2. DROP the current procedure: DROP PROCEDURE delete_events();
  3. Create the new procedure with the correct parameter signature:

CREATE OR REPLACE PROCEDURE delete_events(job_id int, config jsonb)...

  1. Create the new job: SELECT add_job('delete_events','5m');
  2. Manually run the job to verify: CALL run_job(####, NULL);

If that last job produces an error, let us know. Also, you can use something like RAISE INFO in the stored procedure code while you’re debugging. When you manually call the UDA with CALL run_job... any RAISE statements should output to help you better understand where things might be failing.

HTH!

2 Likes
5

I should add that the documentation for UDAs has a few examples of how you can use the config JSONB input with these stored procedures and your setup of the automated jobs if you find it helpful to pass in configuration.

Here’s one example: https://docs.timescale.com/timescaledb/latest/how-to-guides/user-defined-actions/example-tiered-storage/#using-a-user-defined-action-to-implement-automatic-data-tiering

1 Like
7

This question was also posed on StackOverflow where we were also able to help out. Here’s the reply in case it helps supplement the above advice from Ryan.

A user defined action must have a specific set of arguments that it takes, see: https://docs.timescale.com/timescaledb/latest/overview/core-concepts/user-defined-actions/#examples

Namely, it must take a (job_id int, config jsonb) if you don’t use those arguments that’s fine, you don’t have to use them, but I believe the procedure needs to take them, even if it never uses them.

The other thing I would note – though somewhat unrelated – is that your deletes here aren’t actually going to reclaim space, you might need to run a reorder policy or a vacuum full or a compression job in order to actually reclaim database space. Deletes in Postgres simply place a gravestone on a row, so unless you have lots more updates or inserts into older chunks you may see significant bloat with a job like this. Running reorder_chunk or a vacuum full or compressing the chunk will actually reclaim the space from deleted rows.