[HACKERS] Triggers and logical replication (10devel) - Mailing list pgsql-hackers

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