Why not using the logical decoding feature: https://www.postgresql.org/docs/9.4/static/logicaldecoding-example.html
On both sides, you would have a process that regularly decodes the stream and emits notifications for event in tables you are insterested in. Sylvain 2017-05-02 18:18 GMT+02:00 Alvaro Aguayo Garcia-Rada < aagu...@opensysperu.com>: > 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" <jamesadam...@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 >
