Re: dblink ,dblink_exec not participating in a Transaction??

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: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of carty mc
Sent: Tuesday, April 01, 2008 6:56 PM
To: pgsql-general(at)postgresql(dot)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 <cartymc3(at)yahoo(dot)com> 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
<http://us.rd.yahoo.com/evt=47523/*http:/tc.deals.yahoo.com/tc/blockbust
er/text5.com> , No Cost.

________________________________

You rock. That's why Blockbuster's offering you one month of
Blockbuster Total Access
<http://us.rd.yahoo.com/evt=47523/*http:/tc.deals.yahoo.com/tc/blockbust
er/text5.com> , No Cost.