Re: [GENERAL] BDR replication and table triggers

Hi.

It's not like BDR is unable to replicate triggers across the cluster: BDR is not intended to do so.

BDR replicates everything that happens inside a transaction; that includes both SQL run directly from the application, as well as changes made by triggers and extensions. As the changes are applied directly from the WAL, no trigger is re-run on the other nodes. If the trigger is re-run, that would lead to problems, such as duplicated rows.

The only "problem", if it really is, is that BDR does not copy notifications across the databases. As this may be seen as a problem, I could also consider it as a chance to make the application more self-conscious of the distributed environment it is running in. So I would try one out of two alternatives:

1. Make the application listen to notifications on both databases, so it will get notified of changes no matter where they happen

2. Instead of using notify, create a notification table, which your app should scan periodically and act accordingly.

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-1) 3377813 | RPM: #034252 / (+51) 995540103  | RPC: (+51) 954183248
Website: www.ocs.pe

----- Original Message -----
From: "jamesadams89" <jamesadams80@hotmail.com>
To: "PostgreSql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, 26 April, 2017 07:48:03
Subject: [GENERAL] BDR replication and table triggers

Hi,

I have some questions regarding how BDR interacts with triggers.

I have two databases that are both joined to the same BDR group and
correctly replicating between one another sharing a table created as:

create table testtable(
        key varchar(16) NOT NULL PRIMARY KEY,
        data jsonb
);

With the following trigger defined:

CREATE OR REPLACE FUNCTION test_table_notify()
RETURNS TRIGGER AS
$$
BEGIN
        IF TG_OP='INSERT' OR TG_OP='UPDATE' THEN
                PERFORM pg_notify( 'TestTable', TG_OP || ' ' || NEW.key );
        ELSE
                PERFORM pg_notify( 'TestTable', TG_OP || ' ' || OLD.key );
        END IF;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER TestTableTrigger
AFTER INSERT OR UPDATE OR DELETE
on testtable
FOR EACH ROW
EXECUTE PROCEDURE test_table_notify();

I then have a client application listening on the 'TestTable' Notify on one
of the Databases:

Client
 ___
|     |
| A  |
|___|
  /\
   |
 _|_         ___
|      |     |       |
|DB1|-----|DB2|
|_ __|     |____|

If I perform any INSERT, UPDATE or DELETE operations directly on DB1 I see
the trigger on the table being fired as expected and Client Application 'A'
recieves the notify.  I also see the changes propagate to DB2 via BDR as
expected.  However if I perform any INSERT, UPDATE or DELETE operations on
DB2 and these changes propagate over to DB1 via BDR I do not see DB1 firing
any triggers. Is this intended behavior?  My current understanding is that
BDR is unable to invoke Postgres triggers as it operates on the rows
directly, a layer below Postgres. Is this Correct? Is there any mechanism
that exists that could provide notifications to a listening application when
BDR makes changes to the underlying database?

Apologies if this is all a bit elementary, this is my first foray into BDR
and I was unable to find anything in the documentation that mentioned
triggers.

Thanks for any input



--
View this message in context: http://www.postgresql-archive.org/BDR-replication-and-table-triggers-tp5958463.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general