Thread: transaction in plpgsql

how can i implement transactions inside my plpgsql functions 

Joe,

> how can i implement transactions inside my plpgsql functions 

You can't.

Each PL/pgSQL function is automatically treated as a single transaction
by Postgres.   As such, the entire function either succeeds or fails.

To manage a transaction *inside* the PL/pgSQL function, Postgres would
have to support nested transactions ... which we currently don't.Nested transactions and MVCC are somewhat of a tangle
toreconcile,
 
and we don't expect a solution until Postgres 8.0 (if then).

-Josh Berkus

> how can i implement transactions inside my plpgsql functions 

I would guess that this is not possible since postgres don't support
nested transactions(?).


-- 
/Nils Höglund, Naqua KB

E-mail:         nils@naqua.se
Web:            http://www.naqua.se/
Home Phone:     +46 (0)18  30 09 19
Cellular Phone: +46 (0)709 91 97 65
Address:        Nya Valsätrav. 26 B               SE-756 46               Uppsala, Sweden

Joseph,

> so does that mean that if i call function 2 from function 1 and
> function
> 2 fails than function 1 and function 2 rolls back
> 
> hopefully the answer is yes

That's correct.  To diagram:

Function 1 calls|
V
Function 2 calls|
V
Function 3, which fails|
V
Function 3 is rolled back|
V
Function 2 is rolled back|
V
Function 1 is rolled back


While this automatic tranactioning and "All succeed or fail together"
functionality is sometimes useful, its often also a major annoyance.   Changing it is on the "todo" list, but poses
somemajor technical
 
challenges.

-Josh Berkus