Re: transactions not working properly ?

Ali Baba wrote:

>Hi Michael,
>
>i want to support explicit commit/rollback support
>in pl/pgsql instead of using autocommit feature.
>
>
The fine manual is your friend:

http://www.postgresql.org/docs/8.0/static/transaction-iso.html
http://www.postgresql.org/docs/8.0/static/tutorial-transactions.html

Sincerely,

Joshua D. Drake

>
>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
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>