CODEWITHSUNDEEP
postgresqltransactionsplpgsqlcommitpostgresql-11
Jorge Campins
Jorge Campins

Reputation: 495

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

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.

Upvotes: 5

Views: 23798

Answers (5)

Alexi Theodore
Alexi Theodore

Reputation: 1677

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
;

Upvotes: 0

chillwalker
chillwalker

Reputation: 98

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.

Upvotes: 1

MC Hammerabi
MC Hammerabi

Reputation: 471

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!

Upvotes: 1

johnbinsc
johnbinsc

Reputation: 41

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.

Upvotes: 4

klin
klin

Reputation: 121604

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

Upvotes: 6

Related Questions