9 ms·

Pg_cron

115 points by awinter-py 5y ago

Angharad 5y ago
That is really cool! No more crontab with the PostgreSQL CLI's execute and username (and potentially password) to do e.g. clean up tasks in the database. Does anyone know which time config this will use? Is it the system time, UTC, or something else?
- OJFord 5y ago
  > Does anyone know which time config this will use? From the readme: > Be aware that pg_cron always uses GMT! (=UTC)
  - polskibus 5y ago
    Too bad, that will make it problematic to use for batch jobs that have to run at particular business hour.
    - vlovich123 5y ago
      Why? Can’t you just convert that business hour to UTC?
      - boondaburrah 5y ago
        ho boy, before I break out "falsehoods programmers believe about time,"[0] nobody wants to manually have to rewrite the UTC time something's supposed to run at when the business hours keep changing relative to UTC for (at the most basic) Daylight Savings reasons. [0] https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time https://infiniteundo.com/post/25326999628/falsehoods-program... (if this is your first time seeing this article, have fun! also check out the one about names.)
        castorp 5y ago
        You don't have to do that "manually". Postgres supports the conversion using the `at time zone` operator.
        mulmen 5y ago
        This extension uses cron syntax and only supports GMT.
    - OJFord 5y ago
      What if your 'business hour' was 0130[^] (business logic for some reason, rather than a working hour, presumably; hence quotes) - what do you want to happen when you go in to Summer time, or come out of it? Does everybody want the same thing? Is it even obvious what it probably does such that nobody's going to have a bad night? [^] in the UK at least, clocks go forward skipping the hour 0100-0200, and repeat it when they go back. If that happens at a different time where you are (this hypothetical feature gets even more complicated! and) then I mean whatever appropriate time for my point to make sense.
      - deleted 5y ago
        [deleted]
      - Xorlev 5y ago
        Not entirely sure why you're being downvoted, but it's true. Sometimes you need something to run at 9am daily, in your local time zone. If your local time zone shifts for daylight savings, you can't define it in UTC without changing it twice a year.
dlojudice 5y ago
cron running on only one server is unreliable and "high availability" cron is complex. So, it's unbelievable how no such solution existed before. It might not be the most elegant way, but having a centralized cron (just like the database is centralized) helps a lot.
- nickjj 5y ago
  > So, it's unbelievable how no such solution existed before I think a lot of folks solved this problem at the application level using background workers or job processing services. For example, Celery with Python or Sidekiq with Ruby has had recurring job support for a really long time. Celery supported this since 2010. You could run distributed jobs (recurring or not) this way without using cron directly or requiring a custom DB extension. IMO it feels much more natural to solve this problem at the app level with a background worker since you can run any task you want, not just DB queries. This also has the added benefit of letting you use your DB library of choice (ORM, etc.) since you can run any code you want within the job.
  - mey 5y ago
    In the Java world the venerable (pre-2006) Quartz library has been around a long time, and is certainly not the only solution. https://www.quartz-scheduler.org/overview/ https://www.quartz-scheduler.org/overview/
  - luhn 5y ago
    Although Celery itself is distributed, the cron manager (celery-beat) is not and will not coordinate with other nodes. If you have two nodes running celery-beat, you'll get two jobs dispatched every time a cron triggers. imo defeats the whole purpose, at that point I might as well use crontab.
- joshribakoff 5y ago
  Perhaps you’re unaware, but kubernetes has cron jobs. Highly available, and a cron only runs on one node at a time (in the happy case)
polskibus 5y ago
Is this like SQL Agent for MS SQL?
kiwicopple 5y ago
You can do some very neat things with this, especially since it’s implemented with Background Workers > Care is taken that these extra processes do not interfere with other postmaster tasks: only one such process is started on each ServerLoop iteration. This means a large number of them could be waiting to be started up and postmaster is still able to quickly service external connection requests. I have had the occasional need to (ab)use it for webhooks https://supabase.com/blog/2021/03/05/postgres-as-a-cron-server https://supabase.com/blog/2021/03/05/postgres-as-a-cron-serv...
bflesch 5y ago
I would've needed this a couple of years ago!
oceanicberries 5y ago
pelagicAustral 5y ago
That's pretty good. I'm finishing a pure SQL merge replication for PSQL. This will definitely wrap up this little project nicely.
pjbe 5y ago
I’ve been using pg_cron to refresh materialized views, which has kept things simple and self contained for our use.

115 points by awinter-py 5y ago

That is really cool! No more crontab with the PostgreSQL CLI's execute and username (and potentially password) to do e.g. clean up tasks in the database. Does anyone know which time config this will use? Is it the system time, UTC, or something else?

OJFord 5y ago
> Does anyone know which time config this will use? From the readme: > Be aware that pg_cron always uses GMT! (=UTC)
- polskibus 5y ago
  Too bad, that will make it problematic to use for batch jobs that have to run at particular business hour.
  - vlovich123 5y ago
    Why? Can’t you just convert that business hour to UTC?
    - boondaburrah 5y ago
      ho boy, before I break out "falsehoods programmers believe about time,"[0] nobody wants to manually have to rewrite the UTC time something's supposed to run at when the business hours keep changing relative to UTC for (at the most basic) Daylight Savings reasons. [0] https://infiniteundo.com/post/25326999628/falsehoods-programmers-believe-about-time https://infiniteundo.com/post/25326999628/falsehoods-program... (if this is your first time seeing this article, have fun! also check out the one about names.)
      - castorp 5y ago
        You don't have to do that "manually". Postgres supports the conversion using the `at time zone` operator.
        mulmen 5y ago
        This extension uses cron syntax and only supports GMT.
  - OJFord 5y ago
    What if your 'business hour' was 0130[^] (business logic for some reason, rather than a working hour, presumably; hence quotes) - what do you want to happen when you go in to Summer time, or come out of it? Does everybody want the same thing? Is it even obvious what it probably does such that nobody's going to have a bad night? [^] in the UK at least, clocks go forward skipping the hour 0100-0200, and repeat it when they go back. If that happens at a different time where you are (this hypothetical feature gets even more complicated! and) then I mean whatever appropriate time for my point to make sense.
    - deleted 5y ago
      [deleted]
    - Xorlev 5y ago
      Not entirely sure why you're being downvoted, but it's true. Sometimes you need something to run at 9am daily, in your local time zone. If your local time zone shifts for daylight savings, you can't define it in UTC without changing it twice a year.

dlojudice 5y ago

cron running on only one server is unreliable and "high availability" cron is complex. So, it's unbelievable how no such solution existed before. It might not be the most elegant way, but having a centralized cron (just like the database is centralized) helps a lot.

nickjj 5y ago
> So, it's unbelievable how no such solution existed before I think a lot of folks solved this problem at the application level using background workers or job processing services. For example, Celery with Python or Sidekiq with Ruby has had recurring job support for a really long time. Celery supported this since 2010. You could run distributed jobs (recurring or not) this way without using cron directly or requiring a custom DB extension. IMO it feels much more natural to solve this problem at the app level with a background worker since you can run any task you want, not just DB queries. This also has the added benefit of letting you use your DB library of choice (ORM, etc.) since you can run any code you want within the job.
- mey 5y ago
  In the Java world the venerable (pre-2006) Quartz library has been around a long time, and is certainly not the only solution. https://www.quartz-scheduler.org/overview/ https://www.quartz-scheduler.org/overview/
- luhn 5y ago
  Although Celery itself is distributed, the cron manager (celery-beat) is not and will not coordinate with other nodes. If you have two nodes running celery-beat, you'll get two jobs dispatched every time a cron triggers. imo defeats the whole purpose, at that point I might as well use crontab.
joshribakoff 5y ago
Perhaps you’re unaware, but kubernetes has cron jobs. Highly available, and a cron only runs on one node at a time (in the happy case)

polskibus 5y ago

Is this like SQL Agent for MS SQL?

kiwicopple 5y ago

You can do some very neat things with this, especially since it’s implemented with Background Workers > Care is taken that these extra processes do not interfere with other postmaster tasks: only one such process is started on each ServerLoop iteration. This means a large number of them could be waiting to be started up and postmaster is still able to quickly service external connection requests. I have had the occasional need to (ab)use it for webhooks https://supabase.com/blog/2021/03/05/postgres-as-a-cron-server https://supabase.com/blog/2021/03/05/postgres-as-a-cron-serv...

bflesch 5y ago

I would've needed this a couple of years ago!

oceanicberries 5y ago

pelagicAustral 5y ago

That's pretty good. I'm finishing a pure SQL merge replication for PSQL. This will definitely wrap up this little project nicely.

pjbe 5y ago

I’ve been using pg_cron to refresh materialized views, which has kept things simple and self contained for our use.