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: <http://www.mmbb.it/> www.mmbb.it
Attachments:
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 9035851P: +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 9035851P: +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 transactionsOn 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 9035851P: +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