Thread: Command execution

Hi guys,

I have been following the mailing list and reading the source code  
for a little while,  and was wandering if someone could fill in the  
gaps for me.  Does PostgresQL view updates and inserts performed in a  
function as part of the same transaction or are they considered  
separate transactions:

For instance say I had a function name foo and ran the command:

SELECT foo();

Inside foo there was a INSERT and UPDATE, and the INSERT failed but  
the UPDATE succeeded would the UPDATE be rolled back?

am  Thu, dem 03.07.2008, um 23:15:33 +1000 mailte Aaron Spiteri folgendes:
> Hi guys,
> 
> I have been following the mailing list and reading the source code  
> for a little while,  and was wandering if someone could fill in the  
> gaps for me.  Does PostgresQL view updates and inserts performed in a  
> function as part of the same transaction or are they considered  
> separate transactions:
> 
> For instance say I had a function name foo and ran the command:
> 
> SELECT foo();
> 
> Inside foo there was a INSERT and UPDATE, and the INSERT failed but  
> the UPDATE succeeded would the UPDATE be rolled back?

The whole function is an own transaction. Fails the INSERT, the complete
function foo failed. In other words; yes, also the UPDATE rolled back.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Thu, 2008-07-03 at 23:15 +1000, Aaron Spiteri wrote:
> Inside foo there was a INSERT and UPDATE, and the INSERT failed but  
> the UPDATE succeeded would the UPDATE be rolled back?

Just to add to the other answers, if the INSERT is before the UPDATE in
the function, the function execution stops when the INSERT fails, and so
the UPDATE will never be executed in the first place...

Cheers,
Csaba.

Thanks for pointers, that has cleared a few things up for me.

On 03/07/2008, at 11:27 PM, Csaba Nagy wrote:

> On Thu, 2008-07-03 at 23:15 +1000, Aaron Spiteri wrote:
>> Inside foo there was a INSERT and UPDATE, and the INSERT failed but
>> the UPDATE succeeded would the UPDATE be rolled back?
>
> Just to add to the other answers, if the INSERT is before the  
> UPDATE in
> the function, the function execution stops when the INSERT fails,  
> and so
> the UPDATE will never be executed in the first place...
>
> Cheers,
> Csaba.
>
>