"ERROR: invalid transaction termination" when trying to execute a procedure with nested transaction control

Question

According to the documentation (https://www.postgresql.org/docs/current/app-psql.html), even with AUTOCOMMIT set to off, PSQL issues an implicit BEGIN just before any command that is not already in a transaction block and is not itself a BEGIN or other transaction-control command, nor a command that cannot be executed inside a transaction block such as VACUUM. (Unfortunately CALL is not treated in the same way as VACCUM). And, according to Shaun Thomas (https://blog.2ndquadrant.com/pg-phriday-stored-procedures-postgres-11/), the invalid transaction termination error happens because it is not possible to close the current transaction (in this case the one initiated by PSQL) from within the procedure. I have tried with all the PSQL settings related to transaction control, but the invalid transaction termination error occurs with all of them; even if the commands file processed by PSQL contains only the CALL statement.

This is the procedure I'm calling:

create or replace procedure producto$cargar_imagenes(_super$ bigint, _archivo$ character varying) as $$
declare
    _msg character varying;
    _log rastro_proceso%ROWTYPE;
begin
    perform rastro_proceso_temporal$insert(_super$);
    perform producto$cargar_imagenes$biz(_super$, _archivo$);
    if (_super$ is not null and _super$ > 0) then
        perform producto$cargar_imagenes$log(_super$, _archivo$);
    else
        perform tarea_usuario$private$update(6519204281880642486, null);
    end if;
    commit;
end;
$$ language plpgsql set search_path = public;

It fails at the commit statement; it works if I comment it out.

Quartis

Please show your code. – Epochmaking

Sorry, I forgot to mention: please edit the question and add the code there. – Epochmaking

"psql" is the PostgreSQL interactive terminal and not an acceptable abbreviation for Postgres. "Postgres" is. Or "pg" if you need it super short. – Choir

When I wrote PSQL I meant PSQL, I didn't mean Postgres. – Quartis

Answer 1

Remove the SET clause. Per the documentation:

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).

Ferryboat

Thanks a lot! Removing the SET clause solves the problem. – Quartis

@JorgeCampins when you remove SET, will it update the table? – Lailalain

Answer 2

It appears that in pg11 (tested in version 11.6) you have the same problem if you include the "SECURITY DEFINER" clause in the procedure definition. So I guess SECURITY DEFINER qualifies as a "SET" clause.

When I remove SECURITY DEFINER I can include a COMMIT statement within the procedure definition without getting the ERROR:invalid transaction termination on the COMMIT statement.

It is also unfortunate that this is a run time error, and NOT a compilation error.

Absurdity

To confirm your suspicion: "A SECURITY DEFINER procedure cannot execute transaction control statements" - postgresql.org/docs/current/sql-createprocedure.html – Mongolic

Answer 3

I had a similar issue where procedure would process an update inside a plpgsql loop and then after update at commit would throw the error: "ERROR: invalid transaction termination" ... Finally tried turning on AutoCommit in PG Admin and procedure worked...for what its worth!

Juback

same here. Note you need to restart pgAdmin entirely after changing this preference. Just reconnecting to server does not help – Zoologist

Answer 4

Not an answer to your question, but i stumbled over this thread via google, because you used the word 'execute' in yout title. I ran into this issue because I called the procedure dynamically via my_var = 'CALL prc_xyz(); and then EXECUTE my_var INTO result_var;. This aproach via EXECUTE cannot handle nested transaction control inside prc_xyz(). I hope this helps someone else.

Marden

Answer 5

To clarify one of the previous answers... the SET...; command CAN be used... it just cannot be done in the procedure's parameters section.

Example:

CREATE OR REPLACE PROCEDURE example()
AS
$$
DECLARE
BEGIN

    BEGIN
        SET your_value = ...; -- << YES
    
        UPDATE something...;
        
        COMMIT;
    
    END;


END
$$
LANGUAGE plpgsql
SET your_value = ...; -- << NO
;

Lasala