Re: BDR replication and table triggers

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 <
aaguayo(at)opensysperu(dot)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" <jamesadams80(at)hotmail(dot)com>
> To: "PostgreSql-general" <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>