[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
