citusdata / citusdata/pg_cron

Document restrictions when using procedures?

Open
#85 0 comments 6 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.