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
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