0

I'm running pg_cron jobs that of course runs from the default postgres db.

I have some functions/procedures that I created in another database called test, and have ran assigned these pg_cron jobs to the correct new database via (example):

select cron.schedule ('some_random_function',
                      '* * * * *',
                      'call test.my_random_funct()'
           );

UPDATE cron.job SET database = 'test';

I know this works because when I had the function/procedure in the public schema of the test database, everything worked. However, I have a another schema in the test database I want to use, called poop, but when I schedule the pg_cron job on this database and schema, all I get are error messages that the function/procedure doesn't exist (even though it does exist).

Do I have to grant some permissions or something or some schema? I'm running under a sysadmin account so it should have all needed privileges...

sample error message that shows up in cron.job_run_details:

ERROR: procedure my_rand_funct() does not exist
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
1
  • Does the \df command in psql show more than one definition? Also, the function name seems to keep changing in your question (test.my_random_funct() becomes my_rand_funct() in the error message), make sure you are calling the correct one with the correct arguments.
    – dwhitemv
    Oct 5, 2021 at 2:58

1 Answer 1

0

The following worked for me. You must specify the jobId.

You can find the jobIds of all cron jobs via select * from cron.job. Also, the jobId is getting returned when you schedule a job.

select cron.schedule ('some_random_function',
                      '* * * * *',
                      'select * from table'
           );

When you have the jobId, add it. In this example the id is 1

UPDATE cron.job SET database = 'test' WHERE jobid = 1;

Here is where I found all this info: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.