transactions not working properly ?

Ali Baba <idofyear@yahoo.com> writes:

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

You can't COMMIT inside a function.

-Doug

[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

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

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

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