From: | Egor Rogov <e(dot)rogov(at)postgrespro(dot)ru> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Triggers and logical replication (10devel) |
Date: | 2017-04-21 10:08:19 |
Message-ID: | 8b6ecb08-136d-48b9-20b4-39d8d758ffac@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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(at)publisher=# CREATE TABLE t(n integer PRIMARY KEY);
postgres(at)publisher=# CREATE PUBLICATION testpub FOR TABLE t;
#
# subscriber: the same table, triggers to write some information into
log table, and subscription
#
postgres(at)subscriber=# CREATE TABLE t(n integer PRIMARY KEY);
postgres(at)subscriber=# CREATE TABLE log(tg_table_name text, tg_when text,
tg_op text, tg_level text, tg_str text);
postgres(at)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(at)subscriber=# CREATE TRIGGER t_before_row BEFORE INSERT OR
UPDATE OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
postgres(at)subscriber=# CREATE TRIGGER t_after_row AFTER INSERT OR UPDATE
OR DELETE ON t FOR EACH ROW EXECUTE PROCEDURE describe();
postgres(at)subscriber=# CREATE TRIGGER t_before_stmt BEFORE INSERT OR
UPDATE OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
postgres(at)subscriber=# CREATE TRIGGER t_after_stmt AFTER INSERT OR UPDATE
OR DELETE ON t FOR EACH STATEMENT EXECUTE PROCEDURE describe();
postgres(at)subscriber=# CREATE SUBSCRIPTION testsub CONNECTION
'host=localhost port=5432 user=postgres dbname=postgres' PUBLICATION
testpub;
#
# publisher
#
postgres(at)publisher=# INSERT INTO t VALUES (1);
INSERT 0 1
#
# subscriber
#
postgres(at)subscriber=# SELECT * FROM t;
n
---
1
(1 row)
postgres(at)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(at)subscriber=# INSERT INTO t VALUES (100);
INSERT 0 1
postgres(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2017-04-21 10:10:33 | Why is get_cheapest_parallel_safe_total_inner() in pathkeys.c? |
Previous Message | Heikki Linnakangas | 2017-04-21 09:20:18 | On-disk format of SCRAM verifiers |