R: Problem with stored procedure and nested transactions

[p.pierotti]

Thanks, I had already read it.
What I cannot find is how to get the behavior I described, is there a way?


Paolo Pierotti 


 
Viale Lombardia, 4 Lodi (LO) 
M: +39 328 9035851 
P: +39 075 8556435
W: www.mmbb.it 

-Messaggio originale-
Da: Adrian Klaver  
Inviato: martedì 30 ottobre 2018 15:06
A: p.piero...@mmbb.it; pgsql-gene...@postgresql.org
Oggetto: Re: Problem with stored procedure and nested transactions

On 10/30/18 7:03 AM, p.piero...@mmbb.it wrote:
> Hi everyone,
> 
> I have problems with stored procedures introduced in version 11.
> 
> I do not understand how to create a nested transaction, in this 
> semplified example:
> 
> *create**or**replace**procedure*tst_prc(*inout*p_cod *text*) 
> *language*plpgsql *as**$procedure$*
> 
> *begin*
> 
> p_cod := 'a';
> 
> *begin*
> 
> *update*aziende *set*mail = 'a...@asd.asd'*where*id = 11; --1st update
> 
> *begin*
> 
> *update*aziende *set*telefono = '0123456789'*where*id = 11; --2nd 
> update
> 
> *commit*;
> 
> *raise**notice*'Inner';
> 
> *end*;
> 
> *update*aziende *set*telefono = '089'*where*id = 11; --3rd update
> 
> --commit;
> 
> *rollback*;
> 
> *raise**notice*'Outer';
> 
> *end*;
> 
> p_cod := 'b';
> 
> *end*;
> 
> *$procedure$*
> 
> The third update goes, rightly, in rollback; the problem is that the 
> first 2 are committed.
> 
> I thought that the “BEGIN/END” block was used to create new 
> transactions and that each of them could be managed individually.

https://www.postgresql.org/docs/10/static/plpgsql-structure.html

"It is important not to confuse the use of BEGIN/END for grouping statements
in PL/pgSQL with the similarly-named SQL commands for transaction control.
PL/pgSQL's BEGIN/END are only for grouping; they do not start or end a
transaction. See Section 43.8 for information on managing transactions in
PL/pgSQL. Also, a block containing an EXCEPTION clause effectively forms a
subtransaction that can be rolled back without affecting the outer
transaction. For more about that see "

> 
> What am I doing wrong?
> 
> Best regards.
> 
> **
> 
> *Paolo Pierotti *
> 
> **
> 
> 
> http://www.mmbb.it/wp-content/uploads/2018/02/MMBB-logo.png
> 
> Viale Lombardia, 4 Lodi (LO)
> M: +39 328 9035851
> 
> P: +39 075 8556435
> 
> W: www.mmbb.it 
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

Problem with stored procedure and nested transactions

[p.pierotti]

Hi everyone,

I have problems with stored procedures introduced in version 11.

I do not understand how to create a nested transaction, in this semplified
example:

 

create or replace procedure tst_prc(inout p_cod text) language plpgsql as
$procedure$

begin

p_cod := 'a';

   begin

 update aziende set mail = 'a...@asd.asd' where id = 11; --1st
update

 begin

update aziende set telefono = '0123456789' where id =
11; --2nd update

commit;

raise notice 'Inner';

 end;

 update aziende set telefono = '089' where id = 11; --3rd update

 --commit;

 rollback;

 raise notice 'Outer';

   end;

p_cod := 'b';

end;

 

$procedure$

 

 

The third update goes, rightly, in rollback; the problem is that the first 2
are committed.

 

I thought that the "BEGIN/END" block was used to create new transactions and
that each of them could be managed individually.

 

What am I doing wrong?

 

Best regards.

 

 

Paolo Pierotti 

 


 

Viale Lombardia, 4 Lodi (LO) 
M: +39 328 9035851 

P: +39 075 8556435

W:   www.mmbb.it