Re: R: Problem with stored procedure and nested transactions

On 10/30/18 7:19 AM, p(dot)pierotti(at)mmbb(dot)it wrote:
> Thanks, I had already read it.
> What I cannot find is how to get the behavior I described, is there a way?

Yes, follow the link to Section 43.8:

https://www.postgresql.org/docs/11/static/plpgsql-transactions.html

>
>
> Paolo Pierotti
>
>
>
> Viale Lombardia, 4 Lodi (LO)
> M: +39 328 9035851
> P: +39 075 8556435
> W: www.mmbb.it
>
> -----Messaggio originale-----
> Da: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> Inviato: martedì 30 ottobre 2018 15:06
> A: p(dot)pierotti(at)mmbb(dot)it; pgsql-general(at)postgresql(dot)org
> Oggetto: 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
>
>

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