CODEWITHSUNDEEP
postgresql
Henry
Henry

Reputation: 825

Invalid transaction termination

I have some procedure which when i execute in dbeaver works fine without issue, however when i call it from outside program i am getting error below. I do not want to copy/paste full procedure here because it's pretty big and it works in db tool. I just copy/paste top and bottom. What could be the cause of it?

Procedure:

CREATE OR REPLACE PROCEDURE MyProcedure(lot of args..)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
.....
.....
COMMIT;
END;
$procedure$
;

Error:

ERROR: invalid transaction termination
  Where: PL/pgSQL function MyFunction line 185 at COMMIT  Call getNextException to see other errors in the batch. Line 185 at COMMIT  Call getNextException to see other errors in the batch.

Upvotes: 13

Views: 36402

Answers (2)

Pyrocks
Pyrocks

Reputation: 481

In my case (v12) the problem was a SET time zone UTC in the procedure declaration.

Once I removed it the error was gone.

It's actually documented:

If a SET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

Upvotes: 5

Laurenz Albe
Laurenz Albe

Reputation: 246433

The documentation says:

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There are some other, undocumented, restrictions:

  • You cannot start a transaction explicitly with BEGIN and commit it inside a transaction. So the following will fail:

    START TRANSACTION;
CALL procedure_with_commit();

    This may be improved in future releases.

  • All procedures in the call stack must be written in PL/pgSQL:

    CREATE PROCEDURE b() LANGUAGE plpgsql
   AS 'BEGIN PERFORM 42; COMMIT; END;';

CREATE PROCEDURE a() LANGUAGE sql
   AS 'CALL b()';

CALL a();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function b() line 1 at COMMIT
SQL function "a" statement 1

As it is, transaction control inside PostgreSQL procedures is somewhat limited.

If you violate any of these rules, you will get the error message you describe in your question. You will probably have to handle transactions in the application rather than in the procedure — perhaps splitting the procedure into smaller parts makes this possible.

Upvotes: 27

Related Questions