Consider using pg_cron for Cron-style repeating/recurring jobs

[ollym]

https://github.com/citusdata/pg_cron

Is a supported extension on a lot of managed PG servers, including most recently AWS Aurora Postgres. This just feels more resilient than running the cron in ruby, and means you wouldn't have to worry about running it only on one node.

What do you think?

I imagine keeping the interface you have right now, and adding a new configuration:

config.good_job.cron_adapter = :pg_cron vs
config.good_job.cron_adapter = :good_job

And then a rake task like:

rails good_job:pg_cron_migrate

Which would automatically generate the correct cron.schedule and cron.unschedule commands in a new migration depending on what you have configured in config.good_job.cron

[bensheldon]

@ollym whoah, I wasn't aware of this plugin. This looks really nice, and this chart of cloud providers is exactly what I was wanting to know: https://github.com/citusdata/pg_cron#managed-services (Bummer that Heroku and Google are not yet part of the list).

I have some reluctance about building it into GoodJob because the mechanism is so different: making a change would require a database migration, whereas the ruby configuration can be changed in code.

I do think this might be a good target for a gem along the lines of scenic or fx gems.

This is the INSERT SQL for a job if you wanted to make a go at an application-level migration (it's a bit messy, and the active_job_id is referenced both as a column value, and in the JSONB):

  INSERT INTO "good_jobs" ("active_job_id", "concurrency_key", "cron_key", "created_at", "updated_at", "scheduled_at", "queue_name", "priority", "serialized_params")
  SELECT "31f0ac5d-185a-4cbb-a22b-64c9b9839617", NULL, NULL, NOW(), NOW(), NOW(), 'default', 0, '{\"job_class\":\"ExampleJob\",\"job_id\":\"31f0ac5d-185a-4cbb-a22b-64c9b9839617\",\"provider_job_id\":null,\"queue_name\":\"default\",\"priority\":0,\"arguments\":[],\"executions\":0,\"exception_executions\":{},\"locale\":\"en\",\"timezone\":\"UTC\",\"enqueued_at\":\"2020-09-21T14:16:16Z\"}'

TIL $$dollar quoted string constants$$ too.

[ollym]

Update: Google are now part of the list, only Heroku is not now

[ollym]

For others struggling with this, a much easier SQL to use for programatically enqueuing jobs from either pg_cron, procedure/database trigger etc. we've settled on something like this:

WITH params AS (
  SELECT
    'ExampleJob'        AS job_class,
    gen_random_uuid()   AS job_id,
    NULL                AS provider_job_id,
    'default'           AS queue_name,
    0                   AS priority,
    json_build_array()  AS arguments,
    0                   AS executions,
    json_build_object() AS exception_executions,
    'en'                AS locale,
    'UTC'               AS timezone,
    now()               AS enqueued_at
)
INSERT INTO good_jobs (active_job_id, concurrency_key, cron_key, created_at, updated_at, scheduled_at, queue_name, priority, serialized_params)
SELECT params.job_id, NULL, NULL, params.enqueued_at, params.enqueued_at, params.enqueued_at, params.queue_name, params.priority, row_to_json(params) FROM params

Just replace the CTE params with the values you want.