1

I have a query as a transaction, using BEGIN and COMMIT. When I run this as a query it succeeds, however when I run it through pg_cron I receive the following error:

FATAL: EndTransactionBlock: unexpected state BEGIN

BEGIN;TRUNCATE app_top50;INSERT INTO xxx...;COMMIT;

Googling the error returns very few results.

dojogeorge
  • 1,674
  • 3
  • 25
  • 35
  • Is this wrapped in a function or a procedure or just passed to pg_cron as a naked string? Please show us. – jjanes Feb 23 '23 at 15:26

1 Answers1

2

pg_cron source code has a comment about it:

/*
 * We don't allow transaction-control commands like COMMIT and ABORT
 * here.  The entire SQL statement is executed as a single transaction
 * which commits if no errors are encountered.
*/

So you don't need to add BEGIN; ... COMMIT; block. Just add queries and they will be executed as single transaction.

tdy
  • 36,675
  • 19
  • 86
  • 83
Angry Hamster
  • 21
  • 1
  • 2