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@xxxxxxxxxxx> To: "PostgreSql-general" <pgsql-general@xxxxxxxxxxxxxx> Sent: Wednesday, 26 April, 2017 07:48:03 Subject: 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
