4 ms·

The time keepers: pg_cron and pg_timetable

123 points by radimm 2y ago

lucianbr 2y ago
Postgres is shaping up to be a quite nice operating system. I wonder if there is an extension for text editing.
- blitzar 2y ago
  Doom on Postgres yet?
  - deleted 2y ago
    [deleted]
  - throw101010 2y ago
    I haven't tested it... but https://github.com/DreamNik/pg_doom https://github.com/DreamNik/pg_doom
    - lucianbr 2y ago
      Awesome stuff.
- RedShift1 2y ago
  Are the built in string functions (https://www.postgresql.org/docs/current/functions-string.html https://www.postgresql.org/docs/current/functions-string.htm...) not enough for you? Snuffs nose in disapproving way.
  - ZiiS 2y ago
    I assume they prefer Emacs style.
    - RedShift1 2y ago
      It's that or the butterflies.
- digitalsankhara 2y ago
  I have been waiting for someone to re-invent PickOS [1] but with a PG heart and a multiuser shell with PL/pgSQL as first class CLI language or some modern form of Pick/BASIC :-) [1] https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system
cuu508 2y ago
As I just learned, apparently you can make HTTP requests from postgres like so: copy (select 'hello world') to program 'curl -m 10 --data-binary @- https://some-url-here'; There's also a postgres extension for making HTTP requests but this seems to work out of the box (if curl is installed). The "-m 10" parameter is 10 second timeout, to reduce the risk of this command hanging. I did not test what happens if curl returns non-zero exit code, this would also need to be tested and handled. One could use this to monitor pg_cron tasks with external cron monitoring services. I'm not sure if this would be overall good idea, but one could :-)
- radimm 2y ago
  Autor here. Generally I feel much more confident about external services 'pulling' the status. But I have something cooking up to reduce the friction of external calls and possible process blocking...
  - cuu508 2y ago
    Browsing StackOverflow I saw a suggestion to use NOTIFY/LISTEN for stuff like this (it was a question about sending emails from postgres) – an external process listens for notifications and runs the external commands. No blocking risk, and the external process can run on a different host, but you have one extra moving part to look after.
- mattashii 2y ago
  > As I just learned, apparently you can make HTTP requests from postgres like so: Well, it's more "you can execute arbitrary commands on the host node, which happens to include curl if it's installed". Which is also why this is limited to admins, as it is often seen as a security problem when users can execute arbitrary commands on the host server. So IMV there's nothing PostgreSQL-specific to that curl command and HTTP request, other than that it happened to be part of the execution stack.
- RedShift1 2y ago
  This only works as admin, use https://github.com/pramsey/pgsql-http https://github.com/pramsey/pgsql-http instead. Whether or not you should, is a different question, there is a certain "oh no" factor to this.
mslot 2y ago
Nice comparison! Worth noting that pg_cron is available on almost all managed PostgreSQL services. Also, many thanks to Devrim Gunduz and Christoph Berg for providing community packages. I wrote pg_cron with the intention of keeping it as simple, reliable, and low maintenance as possible, so it's likely to remain that way. It is possible to implement a more advanced job schedulers on top of pg_cron if needed. For instance, you can set up a few parallel jobs that run every N seconds and take an item from a job queue table.
- radimm 2y ago
  Author here, I hope I made it clear pg_cron is available everywhere. Thank you for pg_cron though!
9dev 2y ago
I was just yesterday wondering about the following scenario: I would have entries that have an expiration date and need to regularly purge all required rows (think access tokens, WebAuthn challenges, etc). The service creating those rows is deployed serverless, so only invoked on incoming requests. Now the only viable options I know are a) having a lottery and run the delete query as part of the normal request handling with p=0.01, b) have a secondary scheduler system that performs housekeeping tasks, or c) using pg_cron to do so in the database. Are there any other solutions to this? Scheduling jobs on the database system works, but I’m always wondering how others solve this.
deleted 2y ago
[deleted]

123 points by radimm 2y ago

lucianbr 2y ago

Postgres is shaping up to be a quite nice operating system. I wonder if there is an extension for text editing.

blitzar 2y ago
Doom on Postgres yet?
- deleted 2y ago
  [deleted]
- throw101010 2y ago
  I haven't tested it... but https://github.com/DreamNik/pg_doom https://github.com/DreamNik/pg_doom
  - lucianbr 2y ago
    Awesome stuff.
RedShift1 2y ago
Are the built in string functions (https://www.postgresql.org/docs/current/functions-string.html https://www.postgresql.org/docs/current/functions-string.htm...) not enough for you? Snuffs nose in disapproving way.
- ZiiS 2y ago
  I assume they prefer Emacs style.
  - RedShift1 2y ago
    It's that or the butterflies.
digitalsankhara 2y ago
I have been waiting for someone to re-invent PickOS [1] but with a PG heart and a multiuser shell with PL/pgSQL as first class CLI language or some modern form of Pick/BASIC :-) [1] https://en.wikipedia.org/wiki/Pick_operating_system https://en.wikipedia.org/wiki/Pick_operating_system

cuu508 2y ago

As I just learned, apparently you can make HTTP requests from postgres like so: copy (select 'hello world') to program 'curl -m 10 --data-binary @- https://some-url-here'; There's also a postgres extension for making HTTP requests but this seems to work out of the box (if curl is installed). The "-m 10" parameter is 10 second timeout, to reduce the risk of this command hanging. I did not test what happens if curl returns non-zero exit code, this would also need to be tested and handled. One could use this to monitor pg_cron tasks with external cron monitoring services. I'm not sure if this would be overall good idea, but one could :-)

radimm 2y ago
Autor here. Generally I feel much more confident about external services 'pulling' the status. But I have something cooking up to reduce the friction of external calls and possible process blocking...
- cuu508 2y ago
  Browsing StackOverflow I saw a suggestion to use NOTIFY/LISTEN for stuff like this (it was a question about sending emails from postgres) – an external process listens for notifications and runs the external commands. No blocking risk, and the external process can run on a different host, but you have one extra moving part to look after.
mattashii 2y ago
> As I just learned, apparently you can make HTTP requests from postgres like so: Well, it's more "you can execute arbitrary commands on the host node, which happens to include curl if it's installed". Which is also why this is limited to admins, as it is often seen as a security problem when users can execute arbitrary commands on the host server. So IMV there's nothing PostgreSQL-specific to that curl command and HTTP request, other than that it happened to be part of the execution stack.
RedShift1 2y ago
This only works as admin, use https://github.com/pramsey/pgsql-http https://github.com/pramsey/pgsql-http instead. Whether or not you should, is a different question, there is a certain "oh no" factor to this.

mslot 2y ago

Nice comparison! Worth noting that pg_cron is available on almost all managed PostgreSQL services. Also, many thanks to Devrim Gunduz and Christoph Berg for providing community packages. I wrote pg_cron with the intention of keeping it as simple, reliable, and low maintenance as possible, so it's likely to remain that way. It is possible to implement a more advanced job schedulers on top of pg_cron if needed. For instance, you can set up a few parallel jobs that run every N seconds and take an item from a job queue table.

radimm 2y ago
Author here, I hope I made it clear pg_cron is available everywhere. Thank you for pg_cron though!

9dev 2y ago

I was just yesterday wondering about the following scenario: I would have entries that have an expiration date and need to regularly purge all required rows (think access tokens, WebAuthn challenges, etc). The service creating those rows is deployed serverless, so only invoked on incoming requests. Now the only viable options I know are a) having a lottery and run the delete query as part of the normal request handling with p=0.01, b) have a secondary scheduler system that performs housekeeping tasks, or c) using pg_cron to do so in the database. Are there any other solutions to this? Scheduling jobs on the database system works, but I’m always wondering how others solve this.

deleted 2y ago

[deleted]