Re: Problem with stored procedure and nested transactions

[Peter Eisentraut]

On 30/10/2018 15:03, p.piero...@mmbb.it wrote:
> I thought that the “BEGIN/END” block was used to create new transactions
> and that each of them could be managed individually.

In PL/pgSQL, BEGIN/END just create syntactic blocks, they don't manage
transactions.

COMMIT and ROLLBACK manage top-level transactions, but those cannot be
nested (since they are top-level).

In order to create a nested transaction structure, you need to use
subtransactions.  In PL/pgSQL, you can use BEGIN/END blocks with an
exception clause to create subtransactions.

I'm not sure what your code is actually trying to do, but you might need
to reorganize it a bit.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Problem with stored procedure and nested transactions

[Adrian Klaver]

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