4 ms·

The ability to run a cron every few seconds is pretty cool and avoids those OS cron scripts that loop and sleep, so I'm glad that was added. Only downside to cr

by film42 3y ago

The ability to run a cron every few seconds is pretty cool and avoids those OS cron scripts that loop and sleep, so I'm glad that was added. Only downside to cron jobs running inside postgres is PL/pgSQL. It's never my first choice for scripting sql operations. I'd much rather use python, ruby, etc. I'm glad it exists when there's no other option, but it's kind of its own beast. That said, I'm very happy to see more and more stuff coming from Citus. We're looking to split a few growing databases and a bunch of folks are excited to try Citus again now that the enterprise features have been open sourced.

danr4 3y ago
plv8 can let your run JavaScript
qaq 3y ago
You can use PL/Python no?
- code_biologist 3y ago
  I dunno about other hosted Postgres providers, but not on AWS RDS no, as the Python extension is untrusted. Looks like you can use plv8 via pg_tle (Trusted Language Extensions) [1] though. [1] https://github.com/aws/pg_tle https://github.com/aws/pg_tle
- BiteCode_dev 3y ago
  Good luck using a venv, loading your libs, upgrading the extensions when needed, etc.
  - killingtime74 3y ago
    Cant you just use it to call a webhook?
    - BiteCode_dev 3y ago
      You need an extension, and making the HTTP call reliable with retry and jitter is going to be hard in pl.
throwaway44444d 3y ago
At the risk of sounding like a Golang/inteface{} "you're using it wrong" defender, most of the objection to PL/pgSQL is because people don't know what they are doing. They look at assignment, looping and manipulation and think this would be much easier in javascript or python. However they've usually embarked on the wrong route at this point, and if they knew how to do join on UPDATE, or use CTEs, or windowing they wouldn't need a big ugly block of imperative code, 90% you see a FOR ... the whole block can be collapsed to a single more performant update/upsert statement. That said I still have 1000+ line pl/pgsql functions and the language constructs could definitely be improved, just a little sugar please...
- maxbond 3y ago
  As a smell test, when writing a FOR loop in pgSQL, you might ask yourself, "does the order of these rows matter?" And "do I need to see all of them?" (rather than say, just the first or last). If the answer to either of these questions is no, you can probably rewrite it declaratively. You might be able to turn the entire function to PL/SQL rather than PL/pgSQL, at which point you might ask yourself if it's better as a view instead of a function. (Similarly, if the answer to both of those questions is yes, you definitely cannot rewrite it declaratively, unless you figure out a different algorithm.) If all of them matter but the order doesn't, you might be able to use aggregations like sum. If the order matters but you don't need to see all of them, you might use aggregations like min and max. You can refactor some of your logic into an aggregation function if your use cases isn't in the standard library. Often helpful if you have some core business logic that contains lots of if statements, and so is easier to write imperatively. But I also wish the language were more ergonomic.
  - nonethewiser 3y ago
    Can someone explain the difference between declarative and imperative in this context? My understanding is SQL is declarative. So is declarative something like “writing high level statements that postgres turns into more specific (imperative?) instructions”? And imperative being writing those specific “do this, then do this” yourself (although probably differently than how postgres does it under the hood).
simonw 3y ago
PL/pgSQL is a great example of the kind of DSL that I would have avoided in the past but I'm actually much happier to use today because GPT-4 can explain code to me and even write basic things that work which I can then improve on.
- BiteCode_dev 3y ago
  Yeah but it can't __easily__ manipulate the file system, talk to the cache store, run bash commands, make an HTTP requests and so on. Cron job are not just about db updates, and once you have one cron job in Python, having them scattered among several language is not ideal.
  - 3c6bYDXLMj 3y ago
    Yes, but there are advantages to doing some things inside your DBMS. Things have tradeoffs as always.
Alifatisk 3y ago
> I'd much rather use python, ruby, etc. I think I’m with you on this one
deleted 3y ago
[deleted]
rubenfiszel 3y ago
One way to use raw python/javascript/bash on top of a postgres scheduler is to run a single container of https://github.com/windmill-labs/windmill https://github.com/windmill-labs/windmill. It only requires a PG connection and does not require a pg extension. It implements a scheduler that is implemented on top of PG and can run any of the aforementioned language in a very practical and observable interface.
mslot 3y ago
An obvious answer would be PL/Python, though is not available on most managed services because they prefer to prevent file system access. An exception is https://docs.crunchybridge.com/extensions-and-languages/python https://docs.crunchybridge.com/extensions-and-languages/pyth... On the other hand, it's quite useful to have a pure SQL cron that cannot do everything, but can run safely in managed services or under a restricted database user. That's probably one of the reasons why practically every managed PostgreSQL service offers pg_cron. Doing more things, like running shell scripts, might limit its usefulness.

by film42 3y ago

danr4 3y ago

plv8 can let your run JavaScript

qaq 3y ago

You can use PL/Python no?

code_biologist 3y ago
I dunno about other hosted Postgres providers, but not on AWS RDS no, as the Python extension is untrusted. Looks like you can use plv8 via pg_tle (Trusted Language Extensions) [1] though. [1] https://github.com/aws/pg_tle https://github.com/aws/pg_tle
BiteCode_dev 3y ago
Good luck using a venv, loading your libs, upgrading the extensions when needed, etc.
- killingtime74 3y ago
  Cant you just use it to call a webhook?
  - BiteCode_dev 3y ago
    You need an extension, and making the HTTP call reliable with retry and jitter is going to be hard in pl.

throwaway44444d 3y ago

At the risk of sounding like a Golang/inteface{} "you're using it wrong" defender, most of the objection to PL/pgSQL is because people don't know what they are doing. They look at assignment, looping and manipulation and think this would be much easier in javascript or python. However they've usually embarked on the wrong route at this point, and if they knew how to do join on UPDATE, or use CTEs, or windowing they wouldn't need a big ugly block of imperative code, 90% you see a FOR ... the whole block can be collapsed to a single more performant update/upsert statement. That said I still have 1000+ line pl/pgsql functions and the language constructs could definitely be improved, just a little sugar please...

maxbond 3y ago
As a smell test, when writing a FOR loop in pgSQL, you might ask yourself, "does the order of these rows matter?" And "do I need to see all of them?" (rather than say, just the first or last). If the answer to either of these questions is no, you can probably rewrite it declaratively. You might be able to turn the entire function to PL/SQL rather than PL/pgSQL, at which point you might ask yourself if it's better as a view instead of a function. (Similarly, if the answer to both of those questions is yes, you definitely cannot rewrite it declaratively, unless you figure out a different algorithm.) If all of them matter but the order doesn't, you might be able to use aggregations like sum. If the order matters but you don't need to see all of them, you might use aggregations like min and max. You can refactor some of your logic into an aggregation function if your use cases isn't in the standard library. Often helpful if you have some core business logic that contains lots of if statements, and so is easier to write imperatively. But I also wish the language were more ergonomic.
- nonethewiser 3y ago
  Can someone explain the difference between declarative and imperative in this context? My understanding is SQL is declarative. So is declarative something like “writing high level statements that postgres turns into more specific (imperative?) instructions”? And imperative being writing those specific “do this, then do this” yourself (although probably differently than how postgres does it under the hood).

simonw 3y ago

PL/pgSQL is a great example of the kind of DSL that I would have avoided in the past but I'm actually much happier to use today because GPT-4 can explain code to me and even write basic things that work which I can then improve on.

BiteCode_dev 3y ago
Yeah but it can't __easily__ manipulate the file system, talk to the cache store, run bash commands, make an HTTP requests and so on. Cron job are not just about db updates, and once you have one cron job in Python, having them scattered among several language is not ideal.
- 3c6bYDXLMj 3y ago
  Yes, but there are advantages to doing some things inside your DBMS. Things have tradeoffs as always.

Alifatisk 3y ago

> I'd much rather use python, ruby, etc. I think I’m with you on this one

deleted 3y ago

[deleted]

rubenfiszel 3y ago

One way to use raw python/javascript/bash on top of a postgres scheduler is to run a single container of https://github.com/windmill-labs/windmill https://github.com/windmill-labs/windmill. It only requires a PG connection and does not require a pg extension. It implements a scheduler that is implemented on top of PG and can run any of the aforementioned language in a very practical and observable interface.

mslot 3y ago

An obvious answer would be PL/Python, though is not available on most managed services because they prefer to prevent file system access. An exception is https://docs.crunchybridge.com/extensions-and-languages/python https://docs.crunchybridge.com/extensions-and-languages/pyth... On the other hand, it's quite useful to have a pure SQL cron that cannot do everything, but can run safely in managed services or under a restricted database user. That's probably one of the reasons why practically every managed PostgreSQL service offers pg_cron. Doing more things, like running shell scripts, might limit its usefulness.