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 <[EMAIL PROTECTED]> 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
