Re: transactions not working properly ? - Mailing list pgsql-hackers

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