Quantcast

Triggers and logical replication (10devel)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Triggers and logical replication (10devel)

Egor Rogov
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Triggers and logical replication (10devel)

Merlin Moncure-2
On Fri, Apr 21, 2017 at 5:08 AM, Egor Rogov <[hidden email]> wrote:
> Hello,
> It seams that tiggers don't fire on subscriber's tables during logical
> replication. Is it a bug?

Reading the documentation (which is TBH a bit hard to follow) it
appears that it is expected behavior.

https://www.postgresql.org/docs/devel/static/logical-replication-architecture.html#logical-replication-snapshot
states:

"The apply process on the subscriber database always runs with
session_replication_role set to replica, which produces the usual
effects on triggers and constraints."

https://www.postgresql.org/docs/devel/static/sql-altertable.html states:

"The trigger firing mechanism is also affected by the configuration
variable session_replication_role. Simply enabled triggers will fire
when the replication role is “origin” (the default) or “local”."

merlin


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Triggers and logical replication (10devel)

Egor Rogov
On 21.04.2017 16:29, Merlin Moncure wrote:

> On Fri, Apr 21, 2017 at 5:08 AM, Egor Rogov <[hidden email]> wrote:
>> Hello,
>> It seams that tiggers don't fire on subscriber's tables during logical
>> replication. Is it a bug?
> Reading the documentation (which is TBH a bit hard to follow) it
> appears that it is expected behavior.
>
> https://www.postgresql.org/docs/devel/static/sql-altertable.html states:
>
> "The trigger firing mechanism is also affected by the configuration
> variable session_replication_role. Simply enabled triggers will fire
> when the replication role is “origin” (the default) or “local”."
Ah, ALTER TABLE ENABLE REPLICA TRIGGER is what I missed.
Thanks!

Regards,
Egor Rogov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Previous Thread Next Thread
Loading...