Document restrictions when using procedures?
- Dominant language
- C
- Stars
- 3.9k
- Forks
- 260
- Avg merge
- 5d 5h
- Merged PRs (30d)
- 1
Description
So I just ran into an issue where I was calling a procedure that made use of transaction control internally. Here is a simple example:
```sql
create procedure t() language plpgsql as $$ begin perform 1; commit; perform 1; end; $$;
select cron.schedule('* * * * *', 'call t(); select 1;');
```
This blew up with the following in the logs:
```
2019-10-07 15:04:00.000 UTC [11839] LOG: cron job 4 starting: call t(); select 1;
2019-10-07 15:04:00.002 UTC [45157] [unknown]@[unknown] LOG: connection received: host=::1 port=40800
2019-10-07 15:04:00.003 UTC [45157] postgres@postgres LOG: connection authorized: user=postgres database=postgres
2019-10-07 15:04:00.007 UTC [45157] postgres@postgres ERROR: invalid transaction termination
2019-10-07 15:04:00.007 UTC [45157] postgres@postgres CONTEXT: PL/pgSQL function t() line 1 at COMMIT
2019-10-07 15:04:00.007 UTC [45157] postgres@postgres STATEMENT: call t(); select 1;
2019-10-07 15:04:00.007 UTC [11839] LOG: cron job 4 ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function t() line 1 at COMMIT
2019-10-07 15:04:00.007 UTC [45157] postgres@postgres LOG: disconnection: session time: 0:00:00.005 user=postgres database=postgres host=::1 port=40800
```
If I only run the procedure and not another statement:
```sql
select cron.unschedule(1);
select cron.schedule('* * * * *', 'call t();');
```
Then things work as expected:
```
2019-10-07 15:06:00.000 UTC [11839] LOG: cron job 5 starting: call t();
2019-10-07 15:06:00.002 UTC [45176] [unknown]@[unknown] LOG: connection received: host=::1 port=40806
2019-10-07 15:06:00.003 UTC [45176] postgres@postgres LOG: connection authorized: user=postgres database=postgres
2019-10-07 15:06:00.007 UTC [11839] LOG: cron job 5 completed: CALL
2019-10-07 15:06:00.007 UTC [45176] postgres@postgres LOG: disconnection: session time: 0:00:00.004 user=postgres database=postgres host=::1 port=40806
```
(And cleanup...)
```sql
select cron.unschedule(2);
drop procedure t();
```
I presume that when there are multiple statements they are wrapped with an implicit transaction by pg_cron. It might be worth documenting some of the caveats around transactions when using pg_cron...
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.