Thread: Transaction Handling in pl/pgsql
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
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 > > 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 > >
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 ;)
> 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/>
"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 > > if an error occurs? > > Yes, if you don't use EXCEPTION clauses the behavior is the same as > previous versions. > > -Doug > >