Triggers and logical replication (10devel)

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
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 10devel 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers