Problem with a Postgresql's procedure to deal with exception in transaction

[Gabrach]

Hello,

I'm trying to control a transaction with a procedure but I'm getting an error I cannot resolve.

This is the code:

SQL

drop table if exists cuentas;

create table cuentas (
id serial,
nombre varchar(100) not null,
saldo dec(15,2) not null,
primary key(id),
constraint saldo CHECK(saldo > 5000)
);


insert into cuentas(nombre, saldo)
values('Carlos',10000);

insert into cuentas(nombre, saldo)
values('Cesar',10000);

SELECT * FROM cuentas;

drop procedure if exists transferir;

create or replace procedure transferir(id_emisor int,id_receptor int,cantidad dec)
language plpgsql
as $$
begin
-- adding the amount from the recievers's account
update cuentas
set saldo = saldo + cantidad
where id = id_receptor;
-- substracting the amount to the sender's account
update cuentas
set saldo = saldo - cantidad
where id = id_emisor;
commit;
EXCEPTION
WHEN check_violation THEN
RAISE NOTICE 'Violación de saldo.';
rollback;
end;$$


When I try to execute:
SQL

call transferir(1,2,1000);

I get:
SQL

ERROR: no se puede comprometer mientras hay una subtransacción activa
CONTEXT: función PL/pgSQL transferir(integer,integer,numeric) en la línea 13 en COMMIT
Estado SQL: 2D000

Can anybody see what I'm doing wrong?

Thank you

Have a nice day !

[Niheel]

Try moving the commit; outside of the transaction block.

In the block, as you have it:

begin
-- adding the amount from the recievers's account
update cuentas
set saldo = saldo + cantidad
where id = id_receptor;
-- substracting the amount to the sender's account
update cuentas
set saldo = saldo - cantidad
where id = id_emisor;
commit;
EXCEPTION
WHEN check_violation THEN
RAISE NOTICE 'Violación de saldo.';
rollback;
end;$$

More information here:
https://www.postgresql.org/docs/11/p...nsactions.html

A transaction cannot be ended inside a block with exception handlers.

[Gabrach]

@Niheel
I appreciate your comment "A transaction cannot be ended inside a block with exception handlers."

If I've understood well that means that if I use an exception handler I will not be able to finish the transaction.

It surprises me a lot that Postgresql is not capable to work with an error in the transaction so you can rollback. Other languages allow you to catch the error and do a rollback.
It should be a way.

Thank you