Thread: Transaction Handling in pl/pgsql?

pgsql-general，
    

　　　On 7/12/05, Craig Bryden <postgresql ( at ) bryden ( dot ) co ( dot ) za> wrote:
> Hi
> 
> I am trying to get a better understanding of how transactions work in
> pl/pgsql functions. I found the following text in the help:
> "It is important not to confuse the use of BEGIN/END for grouping statements
> in PL/pgSQL with the database commands for transaction control. PL/pgSQL's
> BEGIN/END are only for grouping; they do not start or end a transaction"
> but I am still a bit confused.
> 
> Suppose I have a function that will be called from an application. Will all
> the statements in the function be rolled back if the last one generates an
> exception? or do I need to add code to a function to make that happen?
> 
> 

>suppose you have: select your_function(); 

>your_function adds some rows but the last one gives an error, because
>all statements that are out of a transaction block are in its own
>transaction the select calling your_function is inside a
>transaction... so, the answer is yes... the statements inside the
>function will be rolled back



How to use rollback  work in pl/pgsql functions.? Give an example 

>>your_function adds some rows but the last one gives an error, because
>>all statements that are out of a transaction block are in its own
>>transaction the select calling your_function is inside a
>>transaction... so, the answer is yes... the statements inside the
>>function will be rolled back
>

Unless you are using savepoints in 8.x, any error you generate will
automatically cause the need to rollback.

If you are using savepoints you can rollback to a specific point of
a parent transaction.



Sincerely,

Joshua D. Drake


>
>
>
> How to use rollback  work in pl/pgsql functions.? Give an example
>
>
> ---------------------------(end of broadcast)---------------------------TIP 5: don't forget to increase your free
spacemap settings 


--
Your PostgreSQL solutions provider, Command Prompt, Inc.
24x7 support - 1.800.492.2240, programming, and consulting
Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit
http://www.commandprompt.com / http://www.postgresql.org

Joshua D. Drake wrote:
> If you are using savepoints you can rollback to a specific point of
> a parent transaction.

Although you can't use savepoints (explicitly) in functions. PL/PgSQL
exceptions (which are actually implemented internally via savepoints)
can be used to achieve a similar effect.

-Neil