I have a use case where I want to delete expired entries, but since they are heavily connected each might take some time and DELETE CASCADE many records with it. Therefore, to avoid long running transactions, I wanted to commit after each deletion.
I found that it is not possible just using SQL statements, since pg_cron is already running everything inside a transaction. But by testing, I found that stored procedures called from the cron allowed to do it.
I tried verifying with pgadmin and it does look like indeed the stored procedure is commiting the transactions successfully in between the deletions, but I wanted to verify that there were no unintended side effects since this seems to be an "undocumented" area. I suppose a similar case to #85.
Can anybody chime in? I hope this issue also serves as documentation at least, since I searched a lot in the internet and didn't find info about this topic. Thanks!
I have a use case where I want to delete expired entries, but since they are heavily connected each might take some time and DELETE CASCADE many records with it. Therefore, to avoid long running transactions, I wanted to commit after each deletion.
I found that it is not possible just using SQL statements, since pg_cron is already running everything inside a transaction. But by testing, I found that stored procedures called from the cron allowed to do it.
I tried verifying with pgadmin and it does look like indeed the stored procedure is commiting the transactions successfully in between the deletions, but I wanted to verify that there were no unintended side effects since this seems to be an "undocumented" area. I suppose a similar case to #85.
Can anybody chime in? I hope this issue also serves as documentation at least, since I searched a lot in the internet and didn't find info about this topic. Thanks!