Обсуждение: [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.
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, aswell as changes made by triggers and extensions. As the changes are applied directly from the WAL, no trigger is re-runon 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 asa problem, I could also consider it as a chance to make the application more self-conscious of the distributed environmentit 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 theyhappen 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
Why not using the logical decoding feature:
https://www.postgresql.org/docs/9.4/static/logicaldecoding-example.html
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.
2017-05-02 18:18 GMT+02:00 Alvaro Aguayo Garcia-Rada <aaguayo@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" <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
> 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? Yes. > 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? Yes. > Is there any mechanism > that exists that could provide notifications to a listening application when > BDR makes changes to the underlying database? You could listen to an underlying logical decoding stream, but it might be a bit fiddly and complex for your needs. Ideally we'd be able to fire triggers in BDR, but that's not implemented or on the current roadmap and there's no funded work on it at this point. There's some work to support it in pglogical though. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services