Re: dblink ,dblink_exec not participating in a Transaction?? - Mailing list pgsql-general

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