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