From: | jamesadams89 <jamesadams80(at)hotmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | BDR replication and table triggers |
Date: | 2017-04-26 12:48:03 |
Message-ID: | 1493210883978-5958463.post@n3.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2017-04-26 12:59:38 | Re: GENERAL - [How to check if the array contains the element.] |
Previous Message | Andreas Kretschmer | 2017-04-26 11:33:03 | Re: GENERAL - [How to check if the array contains the element.] |