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

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