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
