citusdata/pg_cron

Trouble adding more than 5 cron jobs...

ssoper-usgs opened this issue · 2 comments

I have pg_cron installed in a postgres database in an AWS RDS instance. I'm attempting to add more than the default 5 jobs per database to the job table.

Using this documentation: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

I have been modifying the RDS instance parameter group settings:

  • cron.max_running_jobs from the default (5) to higher numbers, 10 and 20, but not higher than max_worker_processes
  • postgres' own max_worker_processes from the default to a higher number (20, 32, 64, etc)

The behavior I notice is that the first 5 jobs I add in my script do appear in the job table, and those beyond 5 fail to make it onto the table. I have rearranged the job order and notice the same behavior - the first 5 jobs appear in the table and the remainder fail to make it onto the table. I'm curious if other folks have run into this issue and how they resolved it if they were able to add more than 5 jobs to the job table.

Thanks for any input!

Tl;DR: Make sure your jobnames are 64 characters or less and unique, and that you have cron.max_running_jobs set appropriately in your RDS' parameter group settings.

Note, this issue is now resolved. The reason I could not add more than 5 jobs to the cron.job table was that our job names (that fill in the jobname column) were not unique within the first 64 characters. There is a 64 character limit in the jobname column, after which extra characters are not added. If you have two jobs that have the same string within those first 64 characters, pg_cron seems to evaluate that as the same jobname (our job names were in several cases 70+ characters long, but were ultimately unique), and will replace an existing job with a new job of the "same" name without throwing an error.

It might be worth either expanding the character limit for unique validation or making it more obvious (in the cron.job table ddl for instance).

Closing this issue.