Transaction Handling in pl/pgsql

On 7/12/05, Craig Bryden <postgresql@bryden.co.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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

"Craig Bryden" <postgresql@bryden.co.za> writes:

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?

Read up on how in-function exception handling and savepoints interact
for pl/pgsql in 8.0:

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

For previous versions (7.X), the whole thing will be rolled back if
anything in the function throws an exception.

-Doug

OK. I have read that. The part that sticks out is "A block containing an
EXCEPTION clause is significantly more expensive to enter and exit than a
block without one. Therefore, don't use EXCEPTION without need. ".
Performance is paramount to me.

If I ommit the EXCEPTION clause will all the statements still be rolled back
if an error occurs?

Thanks
Craig

----- Original Message -----
From: "Douglas McNaught" <doug@mcnaught.org>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, July 12, 2005 7:43 PM
Subject: Re: [GENERAL] Transaction Handling in pl/pgsql

"Craig Bryden" <postgresql@bryden.co.za> writes:

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?

Read up on how in-function exception handling and savepoints interact
for pl/pgsql in 8.0:

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

What if the select calling my function is not in it's own explicit
transaction block?

Thanks
Craig

----- Original Message -----
From: "Jaime Casanova" <systemguards@gmail.com>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, July 12, 2005 7:37 PM
Subject: Re: [GENERAL] Transaction Handling in pl/pgsql

On 7/12/05, Craig Bryden <postgresql@bryden.co.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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

"Craig Bryden" <postgresql@bryden.co.za> writes:

OK. I have read that. The part that sticks out is "A block containing an
EXCEPTION clause is significantly more expensive to enter and exit than a
block without one. Therefore, don't use EXCEPTION without need. ".
Performance is paramount to me.

If I ommit the EXCEPTION clause will all the statements still be rolled back
if an error occurs?

Yes, if you don't use EXCEPTION clauses the behavior is the same as
previous versions.

-Doug

Thanks a stack. That has answered by question.

Craig

----- Original Message -----
From: "Douglas McNaught" <doug@mcnaught.org>
To: "Craig Bryden" <postgresql@bryden.co.za>
Cc: "pgsql" <pgsql-general@postgresql.org>
Sent: Tuesday, July 12, 2005 8:46 PM
Subject: Re: [GENERAL] Transaction Handling in pl/pgsql

"Craig Bryden" <postgresql@bryden.co.za> writes:

OK. I have read that. The part that sticks out is "A block containing an
EXCEPTION clause is significantly more expensive to enter and exit than

a

block without one. Therefore, don't use EXCEPTION without need. ".
Performance is paramount to me.

If I ommit the EXCEPTION clause will all the statements still be rolled

back

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?

pl/pgsql code always runs *inside a transaction*.

As a result, if you roll something back, all of the effects of code
inside the transaction will be rolled back.

So you don't need to do anything special for things to roll back.
--
let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/rdbms.html
Rules of the Evil Overlord #153. "My Legions of Terror will be an
equal-opportunity employer. Conversely, when it is prophesied that no
man can defeat me, I will keep in mind the increasing number of
non-traditional gender roles." <http://www.eviloverlord.com/&gt;