I am new to Postgresql and I try to do something so simple with Oracle.

I created a procedure with a simple code inside :

CREATE OR REPLACE PROCEDURE user.test_proc(IN param1 character varying)
LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
   i text;
BEGIN

   if param1 = '1' then
     insert into amian.test values ('1', '1');
     rollback;
   else
      insert into amian.test values('2','2');
      commit;
   end if;
end;
$BODY$;

From unix environement I use psql :

psql –p port–d base–U user
\set AUTOCOMMIT off
call user.test_proc('2');

I have this error when the programm meets the COMMIT :

invalid transaction termination 

What is wrong ? what should I do (I want to be with autocommit off) ?

Thank you

CodePudding user response：

You can only use COMMIT and ROLLBACK in a procedure if the procedure is running in its own transaction (autocommit mode). See this quote from the documentation:

If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction.

Turning off autocommit in psql causes the client to send a BEGIN before the CALL statement, so you are in an explicitly started transaction. That causes the problem.

This restriction may be lifted at some point in the future, but currently that's the way it is.

Don't disable autocommit. You are risking long transactions, which can impair the health of your database.