I am using Pg_Cron to run some automated Functions/Procedures using plpgsql. One of my jobs is to do a SELECT... on some tables and gather up some Keys (PK/FK) of records that I need to delete. However, I had some other jobs that are dependent on this job finishing.
Normally you would schedule some trivial function like this on the postgres (where pg_cron lives default) database, e.g.
select cron.schedule ('aReallyCoolJobNameHere',
'* * * * *',
'call mySuperCoolFunction(someArg1, someArg2)'
);
Thus, the above Job would run every 1 minute and call the mySuperCoolFunction
. The way I was doing it currently, was starting up this 1st Job at some time, say every 7pm on Wednesday, and then I would have the other Jobs that are dependent maybe checking every 30min or 1hr to some job_log
table to ensure a record from the 1st Job with a status of COMPLETE
existed. If not, then the other dependent jobs do not actually run (although they do still get triggered in Pg_cron since their doesn't appear a way to chain jobs).
I really don't like the fact though, that I have to set the other dependent Jobs via Cron to basically retry constantly, also it spams of the cron.job_run_details table fast.
I know you might be thinking, "Why don't you just schedule a time like 8pm Wednesday for the other dependent Jobs to run". But the thing is my database is huge and I don't know if the 1st Job will take 30min , 1hr , or 1hr30min etc....
Is there a feature or design pattern for orchestrating the Jobs in pg_cron such that they run in a certain order/chained and get triggered in an efficent way?