Postgresql: transaction - exception procedure problem

Question

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

This is the code:

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;
        commit;
end;$$

When I try to execute:

call transferir(1,2,1000);

I get:

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

Error:

Can anybody see what I'm doing wrong?

What is your Posgres version? (select version(); will tell you). Did you turn on autocommit before calling the procedure? — user330315
– user330315, Commented Feb 26, 2021 at 13:28

user1746636 · Accepted Answer · 2021-02-26 20:25:20Z

1

Hi 'a_horse_with_no_name' and forum administrators, Sorry for using this section which I know is not the correct place but I can't answer a_horse_with_no_name nor comment his suggestion.
I'm using Postgresql version 12.4. Also I unselect autocommit. But Postgresql throws the error. Thanks

answered Feb 26, 2021 at 20:25

user1746636

471 gold badge1 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sticky bit Over a year ago

Hmm, you could try to only use one account. If you had used the account you started the question with, you could have simply edited it and add the information you've been asked for...

Collectives™ on Stack Overflow

Postgresql: transaction - exception procedure problem

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related