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 space map 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