[Feature request] execute sql statement with cron

[Bessonov]

Following use cases:

• Clean up history, data etc. See also https://github.com/hasura/graphql-engine/issues/5053 and related documentation.
• Move data from hot to cold tables, daily table partitioning etc.
• Periodic data transformations, denormalization etc.
• Refreshing materialized views
• Execute notifiers

The nearest FP I can see is https://github.com/hasura/graphql-engine/issues/5050 . If it's implemented, then, probably, it can be used for that: call a mutation that calls a postgres function. But, well, it's very hacky and error prone.

[tirumaraiselvan]

You can do this in your webhook using the run_sql API: https://hasura.io/docs/latest/graphql/core/api-reference/schema-metadata-api/run-sql.html#api-run-sql

[Bessonov]

Thanks @tirumaraiselvan ! I wasn't aware of this API. But it's still very similar to https://github.com/hasura/graphql-engine/commit/fb902d420929bff56f4308b1d040c7145508c899 and suffers from the same issues. Also I think this is very dangerous.

[patrickdevivo]

I'd like to voice support for this feature, as it's something that would be very useful generally, and also for a specific use case I have in mind (regularly executing a data sync process via a pg function).

It seems like it may eventually be possible with Schedule Trigger Transformations: https://github.com/hasura/graphql-engine/discussions/7917, but as @Bessonov mentions it's a bit hacky. This would cut out the "middle-man" of setting up a REST endpoint and HTTP transformation.

[ash0080]

Why not simply support pg_cron #8008

[Bessonov]

@ash0080 this is a very good question. You mentioned already one of drawbacks, but additionally because pg_cron isn't portable between db vendors or even postgres offerings. Furthermore, I would like more declarative approach instead of imperative to configure cron.

[ash0080]

@ash0080 this is a very good question. You mentioned already one of drawbacks, but additionally because pg_cron isn't portable between db vendors or even postgres offerings. Furthermore, I would like more declarative approach instead of imperative to configure cron.

Understand. Declarative is easier to integrate into dashboard. This is indeed a better solution, as there is no need for two sets of cron implementation.

Then I hope to support it soon, hasura layer is obviously supported cron, then it seems to add a trigger, allowing the execution of local functions, is not too big challenge.

[GMkonan]

Are there any news on this one? My goal was to have pg_cron as a migration but this is not viable either. What is the best way to do it for now? The only way I can think is just using pg_cron directly in postgres but this would make db and hasura to not be "connected" so I would need to document what is different between hasura and postgres.

[Yann-P]

Upvoting this one! Use case: periodically refreshing a materialized view in a way that is logged in Hasura.