468,117 Members | 1,452 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,117 developers. It's quick & easy.

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

2 2Bits
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 !
Feb 26 '21 #1
2 1842
Niheel
2,418 Expert Mod 2GB
Try moving the commit; outside of the transaction block.

In the block, as you have it:
Expand|Select|Wrap|Line Numbers
  1. begin
  2. -- adding the amount from the recievers's account
  3. update cuentas
  4. set saldo = saldo + cantidad
  5. where id = id_receptor;
  6. -- substracting the amount to the sender's account
  7. update cuentas
  8. set saldo = saldo - cantidad
  9. where id = id_emisor;
  10. commit;
  11. EXCEPTION
  12. WHEN check_violation THEN
  13. RAISE NOTICE 'Violación de saldo.';
  14. rollback;
  15. end;$$
More information here:
https://www.postgresql.org/docs/11/p...nsactions.html
A transaction cannot be ended inside a block with exception handlers.
Feb 26 '21 #2
Gabrach
2 2Bits
@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
Feb 28 '21 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

7 posts views Thread by Dirk Deimeke | last post: by
5 posts views Thread by Karl | last post: by
reply views Thread by mbench | last post: by
2 posts views Thread by meendar | last post: by
13 posts views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.