[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
