Re: transactions not working properly ?

[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