Transaction commit error - invalid transaction terminations

Question

Can you please guide what is wrong with below code which gives an issue with below error:

ERROR: invalid transaction termination.

CREATE OR REPLACE PROCEDURE "app"."sproc_data_create"
( name varchar(100))
LANGUAGE plpgsql
AS
$$
Declare idIdentity int;
 BEGIN

 INSERT INTO data(Name) VALUES (name) RETURNING Id into idIdentity;
 INSERT INTO relationalData(dataId) VALUES (idIdentity);
  
 COMMIT; 
 END

For below scenarios, this does not work executing commit for one insert statement and expected to rollback second insert statement. This rollback both insert statements.

BEGIN
    
     INSERT INTO data(Name) VALUES (name) RETURNING Id into idIdentity;
     COMMIT;
    INSERT INTO relationalData(dataId) VALUES (idIdentity);
END

Execute below script:

/*
do
begin
    CALL "app"."sproc_data_create"("name field")
    
end
*/    
//invalid transaction termination
    CONTEXT: PL/pgSQL function

Apologies, have not created online sandbox. But this same scenario happen with my procedure and raising "invalid transaction termination" issue. By searching I found, "commit" outside of END statement but still that not worked. In SQL Server, it went so easy and I am new to postgres. — dsi, Apr 13 '22 at 21:28

score 2 · Answer 1 · answered Apr 14 '22 at 00:39

Found an issue, I was executing "call" procedure statement inside DO BEGIN/END statement and that creates parent transaction scope.

As found on below post. (Basically, "call" has parent transaction scope and I use commit/rollback under subTransaction scope within procedure. By removing DO BEGIN/END wrapper from "call" statement, it resolves the issue.

Can a Postgres Commit Exist in Procedure that has an Exception Block?

Thanks

score 0 · Answer 2 · answered Mar 09 '23 at 07:39

This error happened after trying to execute the stored procedure using sqlalchemy with python.

The main reason of this error was I created the stored procedure with including COMMIT; statement after INSERT/UPDATE statement, so replacing the stored procedure without using the COMMIT; statement solved the issue.

CREATE OR REPLACE PROCEDURE insert_files(
    file_id text)
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN 
INSERT INTO files(file_id)
                    VALUES (file_id);
        
-- AVOID USING COMMIT; HERE
    
END
$BODY$;

ALTER PROCEDURE insert_files(text)
    OWNER TO user;

Transaction commit error - invalid transaction terminations

2 Answers2