Jon,
Thanks for the reply.
As i explained earlier, I am using java (via hibernate) to control the
overall transaction.
I cannot have begin/rollback statements for dblink_exec.
Only when the entire method call from java succeeds I want the transaction to
be commited.
I dont want a funtion controlling the transaction partially. I thought there
would be easier way for letting dblink participate in the incoming transaction
(i.e. from java-jdbc method call) and commit only when the main transaction
commits.
I wanted to treat the whole updates inside the function as one, and let java
method call decide whether to commit or rollback.
thanks,
"Roberts, Jon" <[EMAIL PROTECTED]> wrote:
v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);}
I sent this just a few days ago to the list on a similar question. You
will need to move your code from the trigger to a regular function like the one
below. The function should handle the update to the table and also to the
linked database.
create table log (update_datetime timestamp);
create or replace function fn_test (p_fail boolean) returns void as $$ declare
v_sql varchar;
v_int int;
begin
perform dblink_connect('pg', 'dbname=postgres user=scott password=tiger
host=localhost');
v_sql := 'begin;';
perform dblink_exec('pg', v_sql, false);
v_sql := 'insert into log values (now())';
perform dblink_exec('pg', v_sql, false);
if p_fail then
v_int := 1/0;
end if;
v_sql := 'commit;';
perform dblink_exec('pg', v_sql, false);
perform dblink_disconnect('pg');
exception
when others then
v_sql := 'rollback;';
perform dblink_exec('pg', v_sql, false);
perform dblink_disconnect('pg');
raise exception '%', sqlerrm;
end;
$$
language 'plpgsql';
Now that is a basic function that will insert data into the postgres database
via a dblink. If you pass in fail, it will hit "division by zero" and rollback
both the linked transaction and the primary transaction of the function.
select fn_test(false);
select * from log;
--you see a new row
select fn_test(true)
--ERROR: division by zero
select * from log;
--you see that a new row wasn't inserted.
Jon
---------------------------------
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of carty mc
Sent: Tuesday, April 01, 2008 6:56 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] dblink ,dblink_exec not participating in a Transaction??
Just to clarify few things in the above posting.
My Main DB Client is java (using hibernate) which has Main Transaction
Manager.
This one controls the overall transaction
Work flow is as follows:
1)java program updates a table in Database A
2) As a result Trigger procedure defined for this table gets executed which
inturn uses dblink_exec program to update a table in Different database B.
3) Control goes back to java program.
java program will rollback the whole transaction if any errors/exceptions
are found.
or else java program will issue a commit.
Java program will determine whether to commit or rollback. It is the main
transaction manager.
In this case the updates that were made using dblink_exec are not getting
rolled back in Database B.
carty mc <[EMAIL PROTECTED]> wrote:
How I can make dblink to participate in transaction so that remote
changes made by dblink can only be committed if only local transaction succeds.
Here is my current scenario:
I am using two databases A & B. In Database A, I have trigger procedure
written for a Table . In this trigger procedure, I use dblink_exec to update
a table in the other database B.
The problem I am having right now is my trigger procedure currently
participates in a transaction. If Transaction rollsback: whatever trigger
procedure modified in database A is getting rolled back whereas Chages made to
a table in Database B using dblink_exec are not.
Note: Transaction can be rolled back due to excpetions any where from main
db client which updates a table in database A .
So my question is there a way to tell dblink_exec to participate in a
Transaction. This way commits wont go to table in database B until transaction
completed succesfully.
I appreciate any help,
thanks,
---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster
Total Access, No Cost.
---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster
Total Access, No Cost.
---------------------------------
You rock. That's why Blockbuster's offering you one month of Blockbuster Total
Access, No Cost.
