Re: transactions not working properly ?

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(at)fuhr(dot)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