Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Invalid transaction termination

Tags:

postgresql

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.
like image 601
Henry Avatar asked Dec 03 '19 14:12

Henry


1 Answers

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.

like image 129
Laurenz Albe Avatar answered Nov 05 '22 19:11

Laurenz Albe