Hello,
It seams that tiggers don't fire on subscriber's tables during logical
replication. Is it a bug?
#
# publisher: simple table and publication
#
postgres@publisher=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL10devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)
postgres@publisher=# CREATE TABLE t(n integer PRIMARY KEY);
postgres@publisher=# CREATE PUBLICATION testpub FOR TABLE t;
#
# subscriber: the same table, triggers to write some information into
log table, and subscription
#
postgres@subscriber=# CREATE TABLE t(n integer PRIMARY KEY);
postgres@subscriber=# CREATE TABLE log(tg_table_name text, tg_when text,
tg_op text, tg_level text, tg_str text);
postgres@subscriber=# CREATE OR REPLACE FUNCTION public.describe() RETURNS trigger LANGUAGE plpgsql
AS $function$
DECLARE rec record; str text := '';
BEGIN IF TG_LEVEL = 'ROW' THEN CASE TG_OP WHEN 'DELETE' THEN rec := OLD; str := OLD::text;
WHEN 'UPDATE' THEN rec := NEW; str := OLD || ' -> ' || NEW; WHEN 'INSERT' THEN rec := NEW; str :=
NEW::text; END CASE; END IF; INSERT INTO log(tg_table_name, tg_when, tg_op, tg_level, tg_str)
VALUES (TG_TABLE_NAME, TG_WHEN, TG_OP, TG_LEVEL, str); RETURN rec;
END;
$function$;
postgres@subscriber=# CREATE TRIGGER t_before_row BEFORE INSERT OR
UPDATE OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
postgres@subscriber=# CREATE TRIGGER t_after_row AFTER INSERT OR UPDATE
OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
postgres@subscriber=# CREATE TRIGGER t_before_stmt BEFORE INSERT OR
UPDATE OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
postgres@subscriber=# CREATE TRIGGER t_after_stmt AFTER INSERT OR UPDATE
OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
postgres@subscriber=# CREATE SUBSCRIPTION testsub CONNECTION
'host=localhost port=5432 user=postgres dbname=postgres' PUBLICATION
testpub;
#
# publisher
#
postgres@publisher=# INSERT INTO t VALUES (1);
INSERT 0 1
#
# subscriber
#
postgres@subscriber=# SELECT * FROM t; n
--- 1
(1 row)
postgres@subscriber=# SELECT * FROM log; tg_table_name | tg_when | tg_op | tg_level | tg_str
---------------+---------+-------+----------+--------
(0 rows)
#
# so replication works, but triggers don't.
# now check that triggers work alright locally:
#
postgres@subscriber=# INSERT INTO t VALUES (100);
INSERT 0 1
postgres@subscriber=# SELECT * FROM log; tg_table_name | tg_when | tg_op | tg_level | tg_str
---------------+---------+--------+-----------+-------- t | BEFORE | INSERT | STATEMENT | t |
BEFORE | INSERT | ROW | (100) t | AFTER | INSERT | ROW | (100) t | AFTER |
INSERT| STATEMENT |
(4 rows)
Regards,
Egor Rogov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company