Thread: transactions not working properly ?
Hi, can any one describe how the transaction are being handled in postgres. i.e. function given below should actually insert the desire values in test table but it do not save them. START TRANSACTION; create or replace function testFunc() returns int as $$ declare x integer; begin x := 1; insert into test values (210,20); x := x/0; RETURN 0; exception when others then raise info 'error generated '; commit; RETURN 0; end; $$ language plpgsql; __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Ali Baba <idofyear@yahoo.com> writes: > exception > when others then > raise info 'error generated '; > commit; > RETURN 0; > end; You can't COMMIT inside a function. -Doug
[This question would probably be more appropriate in pgsql-general than in pgsql-hackers.] On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba wrote: > can any one describe how the transaction are being > handled in postgres. I think you're talking about how PL/pgSQL exception handlers work with transactions. See the documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > function given below should actually insert the desire > values in test table but it do not save them. A complete test case would make it easier help. All we see in the example is the start of a transaction and the creation of a function -- we don't see how you're actually using it nor what output (e.g., error messages) it produces. > begin > x := 1; > insert into test values (210,20); > x := x/0; > > RETURN 0; > > exception > when others then > raise info 'error generated '; > commit; > RETURN 0; > end; The "Trapping Errors" documentation states: When an error is caught by an EXCEPTION clause, the local variables of the PL/pgSQL function remain as they were when theerror occurred, but all changes to persistent database state within the block are rolled back. Since the divide-by-zero error is in the same block as the INSERT, the INSERT is rolled back. Also, you can't issue COMMIT inside a function -- see the "Structure of PL/pgSQL" documentation: http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html Functions and trigger procedures are always executed within a transaction established by an outer query they cannot startor commit that transaction, since there would be no context for them to execute in. However, a block containing anEXCEPTION clause effectively forms a subtransaction that can be rolled back without affecting the outer transaction. -- Michael Fuhr
Ali Baba wrote: > can any one describe how the transaction are being > handled in postgres. Pretty much the same as in any other SQL implementation, and you'd have the same problem in any database. Is this a homework assignment? Jeroen
Hi Michael, i want to support explicit commit/rollback support in pl/pgsql instead of using autocommit feature. my requirement is to know how transactions work in postgres generally and how to support transaction managment in pl/pgsql thanks for your help. -- Asif Ali. > --- Michael Fuhr <mike@fuhr.org> wrote: > > > [This question would probably be more appropriate > in > > pgsql-general > > than in pgsql-hackers.] > > > > On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba > > wrote: > > > can any one describe how the transaction are > being > > > handled in postgres. > > > > I think you're talking about how PL/pgSQL > exception > > handlers work > > with transactions. See the documentation: > > > > > http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > > > > function given below should actually insert the > > desire > > > values in test table but it do not save them. > > > > A complete test case would make it easier help. > All > > we see in the > > example is the start of a transaction and the > > creation of a function -- > > we don't see how you're actually using it nor what > > output (e.g., error > > messages) it produces. > > > > > begin > > > x := 1; > > > insert into test values (210,20); > > > x := x/0; > > > > > > RETURN 0; > > > > > > exception > > > when others then > > > raise info 'error generated '; > > > commit; > > > RETURN 0; > > > end; > > > > The "Trapping Errors" documentation states: > > > > When an error is caught by an EXCEPTION clause, > > the local variables > > of the PL/pgSQL function remain as they were > when > > the error occurred, > > but all changes to persistent database state > > within the block are > > rolled back. > > > > Since the divide-by-zero error is in the same > block > > as the INSERT, > > the INSERT is rolled back. Also, you can't issue > > COMMIT inside a > > function -- see the "Structure of PL/pgSQL" > > documentation: > > > > > http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html > > > > Functions and trigger procedures are always > > executed within a > > transaction established by an outer query they > > cannot start or > > commit that transaction, since there would be no > > context for them > > to execute in. However, a block containing an > > EXCEPTION clause > > effectively forms a subtransaction that can be > > rolled back without > > affecting the outer transaction. > > > > -- > > Michael Fuhr > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Ali Baba wrote: >Hi Michael, > >i want to support explicit commit/rollback support >in pl/pgsql instead of using autocommit feature. > > The fine manual is your friend: http://www.postgresql.org/docs/8.0/static/transaction-iso.html http://www.postgresql.org/docs/8.0/static/tutorial-transactions.html Sincerely, Joshua D. Drake > >my requirement is to know how transactions work in >postgres generally and how to support transaction >managment in pl/pgsql > >thanks for your help. > >-- >Asif Ali. > > > > >>--- Michael Fuhr <mike@fuhr.org> wrote: >> >> >> >>>[This question would probably be more appropriate >>> >>> >>in >> >> >>>pgsql-general >>>than in pgsql-hackers.] >>> >>>On Wed, Aug 17, 2005 at 05:53:14AM -0700, Ali Baba >>>wrote: >>> >>> >>>>can any one describe how the transaction are >>>> >>>> >>being >> >> >>>>handled in postgres. >>>> >>>> >>>I think you're talking about how PL/pgSQL >>> >>> >>exception >> >> >>>handlers work >>>with transactions. See the documentation: >>> >>> >>> >>> >http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING > > >>>>function given below should actually insert the >>>> >>>> >>>desire >>> >>> >>>>values in test table but it do not save them. >>>> >>>> >>>A complete test case would make it easier help. >>> >>> >>All >> >> >>>we see in the >>>example is the start of a transaction and the >>>creation of a function -- >>>we don't see how you're actually using it nor what >>>output (e.g., error >>>messages) it produces. >>> >>> >>> >>>>begin >>>>x := 1; >>>>insert into test values (210,20); >>>>x := x/0; >>>> >>>>RETURN 0; >>>> >>>>exception >>>>when others then >>>> raise info 'error generated '; >>>> commit; >>>> RETURN 0; >>>>end; >>>> >>>> >>>The "Trapping Errors" documentation states: >>> >>> When an error is caught by an EXCEPTION clause, >>>the local variables >>> of the PL/pgSQL function remain as they were >>> >>> >>when >> >> >>>the error occurred, >>> but all changes to persistent database state >>>within the block are >>> rolled back. >>> >>>Since the divide-by-zero error is in the same >>> >>> >>block >> >> >>>as the INSERT, >>>the INSERT is rolled back. Also, you can't issue >>>COMMIT inside a >>>function -- see the "Structure of PL/pgSQL" >>>documentation: >>> >>> >>> >>> >http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html > > >>> Functions and trigger procedures are always >>>executed within a >>> transaction established by an outer query they >>>cannot start or >>> commit that transaction, since there would be no >>>context for them >>> to execute in. However, a block containing an >>>EXCEPTION clause >>> effectively forms a subtransaction that can be >>>rolled back without >>> affecting the outer transaction. >>> >>>-- >>>Michael Fuhr >>> >>>---------------------------(end of >>>broadcast)--------------------------- >>>TIP 4: Have you searched our list archives? >>> >>> http://archives.postgresql.org >>> >>> >>> >>__________________________________________________ >>Do You Yahoo!? >>Tired of spam? Yahoo! Mail has the best spam >>protection around >>http://mail.yahoo.com >> >> >> > > >__________________________________________________ >Do You Yahoo!? >Tired of spam? Yahoo! Mail has the best spam protection around >http://mail.yahoo.com > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > >