I am trying to create and update a cron job on a Azure PostgreSQL Flex server. I have pg_cron installed and I can create the job. But I am unable to update it.

select cron.schedule('0 9 * * 7', 'refresh materialized view sample_mv;');

The above works perfectly, I can see the job scheduled in the job table. But when I try to run the below update statement, it fails with a "ERROR: permission denied for table job".

update cron.job set database = 'newdb' where jobid='3';

Any advice or guidance would be greatly appreciated!

CodePudding user response：

In Azure Database for PostgreSQL Flexible Server, if you try to update the cron.job you will get an error as it requires superuser privileges. However, you can try to use a workaround to make pg_cron jobs work on other databases and manage to do it this way:

select cron.alter_job(job_id:=<your_job_id>,database:='<your_db_name>');