Re: Problem with stored procedure and nested transactions

On 10/30/18 7:03 AM, p(dot)pierotti(at)mmbb(dot)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 = 'asd(at)asd(dot)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 <http://www.mmbb.it/>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com