Answer a question

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.

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.