0

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?

ennth
  • 1,698
  • 5
  • 31
  • 63
  • Have a look at pg_timetable which supports chains of jobs. – Laurenz Albe Oct 18 '21 at 06:15
  • @LaurenzAlbe Wow, looks awesome. Can this be used in corporate environments and safe to add exe to existing Postgres DBs on, say for example, AWS RDS? – ennth Oct 18 '21 at 20:44
  • You won't be able to install it on RDS, unless it is among the extensions they support. But you can schedule jobs on an RDS database. I'd say it is safe to use. Disclaimer: it was written by a co-worker of mine. – Laurenz Albe Oct 19 '21 at 02:46

0 Answers0