Re: Problem with stored procedure and nested transactions

2018-11-03 Thread 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: R: Problem with stored procedure and nested transactions

2018-10-30 Thread Adrian Klaver 

On 10/30/18 7:19 AM, p.piero...@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 
Inviato: martedì 30 ottobre 2018 15:06
A: p.piero...@mmbb.it; pgsql-gene...@postgresql.org
Oggetto: Re: Problem with stored procedure and nested transactions

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 <http://www.mmbb.it/>




--
Adrian Klaver
adrian.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@aklaver.com

R: Problem with stored procedure and nested transactions

2018-10-30 Thread p.pierotti 
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  
Inviato: martedì 30 ottobre 2018 15:06
A: p.piero...@mmbb.it; pgsql-gene...@postgresql.org
Oggetto: Re: Problem with stored procedure and nested transactions

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 <http://www.mmbb.it/>
> 


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Problem with stored procedure and nested transactions

2018-10-30 Thread 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

Problem with stored procedure and nested transactions

2018-10-30 Thread p.pierotti 
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.

 

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