Thread: Transaction Handling in pl/pgsql

Transaction Handling in pl/pgsql

From
"Craig Bryden"
Date:
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?



Thanks
Craig


Re: Transaction Handling in pl/pgsql

From
Jaime Casanova
Date:
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 ;)

Re: Transaction Handling in pl/pgsql

From
Douglas McNaught
Date:
"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

Re: Transaction Handling in pl/pgsql

From
"Craig Bryden"
Date:
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
>
> For previous versions (7.X), the whole thing will be rolled back if
> anything in the function throws an exception.
>
> -Doug
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>


Re: Transaction Handling in pl/pgsql

From
"Craig Bryden"
Date:
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 ;)



Re: Transaction Handling in pl/pgsql

From
Christopher Browne
Date:
> 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/>

Re: Transaction Handling in pl/pgsql

From
Douglas McNaught
Date:
"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

Re: Transaction Handling in pl/pgsql

From
"Craig Bryden"
Date:
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
> > if an error occurs?
>
> Yes, if you don't use EXCEPTION clauses the behavior is the same as
> previous versions.
>
> -Doug
>
>