Обсуждение: Problem with stored procedure and nested transactions
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@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
Вложения
On 10/30/18 7:03 AM, p.pierotti@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 = 'asd@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 <http://www.mmbb.it/> > -- Adrian Klaver adrian.klaver@aklaver.com
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 <adrian.klaver@aklaver.com> Inviato: martedì 30 ottobre 2018 15:06 A: p.pierotti@mmbb.it; pgsql-general@postgresql.org Oggetto: Re: Problem with stored procedure and nested transactions On 10/30/18 7:03 AM, p.pierotti@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 = 'asd@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 <http://www.mmbb.it/> > -- Adrian Klaver adrian.klaver@aklaver.com
On 10/30/18 7:19 AM, p.pierotti@mmbb.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.klaver@aklaver.com> > Inviato: martedì 30 ottobre 2018 15:06 > A: p.pierotti@mmbb.it; pgsql-general@postgresql.org > Oggetto: Re: Problem with stored procedure and nested transactions > > On 10/30/18 7:03 AM, p.pierotti@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 = 'asd@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 <http://www.mmbb.it/> >> > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > -- Adrian Klaver adrian.klaver@aklaver.com
On 30/10/2018 15:03, p.pierotti@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