citusdata / citusdata/pg_cron

Is it possible to do internal transaction control using a stored procedure scheduled with pg_cron?

Open
#407 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

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!

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.