citusdata
Any Reason why there can only be one job table inside schema cron on postgres or configured?
Hello I'm actually quite loving the pg_cron extensions, it allows for small users a really simple mechanism to create High Available Cron Jobs.
However managing these are quite a bit painful. Is there any reason why the metadata tables is expected to be a single one?
Isn't there a way to actually do CREATE EXTENSION pg_cron; and it would be possible to select all the jobs of the current user, schedule/unschedule them?
Currently we use jdbc and if we don't want to open up a second connection to the postgres database, we would need to reconfigure the cron extension everywhere (well sadly we need to configure shared_preload_libraries anyway...).
this looks a little bit of an overhead, especially since we want to still use our flyway migrations to insert new cron jobs.
Is there any "best practice"/no-go on what's the best way to integrate pg_cron inside an application?
(At the moment we actually have a single cron.schedule that calls
SELECT pg_notify('our_channel', '{"type": "nightly"}'). Which we registerted with cron.schedule and then updated the cron.job database column.
If you want to allow a regular user to schedule jobs then you would run the following as superuser after creating the extension:
GRANT USAGE ON SCHEMA cron TO myuser;
If you connect to the database as myuser, you can then run cron.schedule to schedule jobs that are executed as myuser.
To unschedule all jobs belonging to the current user you could do:
SELECT cron.unschedule(jobid) FROM cron.job;
The cron.job table uses row-level security to make sure users only see their own jobs. Only the superuser can see all jobs.
well that's what I did, however I either need to configure all my databases to set cron.database_name. However this is unsuitable if I would use two databases for my application. (I.e. some smaller "not-so-micro"-services.)
So I actually need to keep another connection open to the postgres (or configured) database, this is what I find not really suitable in some cases.
Makes sense. Currently pg_cron can only work with a single database.
You'll be able to set multiple database names in cron.database_name with pg_cron 2.0:
https://github.com/citusdata/pg_cron/tree/beta-2.0
(2.0 is mostly pending a fix for #16)
This is problematic for me as well. I would like to add pg_cron to a template database. Even being able to add multiple database names in cron.database_name wouldn't solve the problem. I would have to change the parameter and restart postgres every time a new database was created.
I've tried adding the name of the template db, but the creation of a new DB fails.
I basically have the same problem. I would like to use pg_cron without having to configure in which DB it can be used. We use DBs quite often on different servers.
Makes sense. Currently pg_cron can only work with a single database.
You'll be able to set multiple database names in
cron.database_namewith pg_cron 2.0: https://github.com/citusdata/pg_cron/tree/beta-2.0(2.0 is mostly pending a fix for #16)
@marcocitus / @marcoslot Would it be possible to land the other changes from the 2.0 branch such as support for multiple databases and scheduling jobs by name? It seems a shame for those features to stagnate for years waiting for a fix for the timezone issue.
