Runnig pg_cron jobs on different database and non-public schema says "No procedure matches the given name and argument types"

Question

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.

Answer 1

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