transactions not working properly ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

transactions not working properly ?

Ali Baba
Hi,
can any one describe how the transaction are being
handled in postgres.
i.e.
function given below should actually insert the desire
values in test table but it do not save them.
START TRANSACTION;
create or replace function testFunc() returns int as
$$
declare
x integer;
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;
$$ language plpgsql;

__________________________________________________
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
Reply | Threaded
Open this post in threaded view
|

Re: transactions not working properly ?

Douglas McNaught
Ali Baba <[hidden email]> writes:

> exception
> when others then
>     raise info 'error generated ';
>     commit;
>     RETURN 0;
> end;

You can't COMMIT inside a function.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: transactions not working properly ?

Michael Fuhr
In reply to this post by Ali Baba
[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
Reply | Threaded
Open this post in threaded view
|

Re: transactions not working properly ?

Jeroen Vermeulen-3
In reply to this post by Ali Baba
Ali Baba wrote:

> can any one describe how the transaction are being
> handled in postgres.

Pretty much the same as in any other SQL implementation, and you'd have
the same problem in any database.  Is this a homework assignment?


Jeroen



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: transactions not working properly ?

Ali Baba
In reply to this post by Ali Baba
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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: transactions not working properly ?

Joshua D. Drake
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 <[hidden email]> 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
>  
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend