PostgreSQL trigger/rule based replication configure, DISABLE/ENABLE [ REPLICA ALWAYS ] TRIGGER RULE
背景
当我们使用PostgreSQL做基于规则或触发器的逻辑复制时，如果是双向复制，那么在两个节点的表上都需要创建规则或者触发器，那么就需要注意死循环，例如一条记录在本地插入后，通过触发器发送到远端执行，在远端如果触发器又被触发，就会进入死循环。
例如我之前做的基于触发器和DBLINK的双向复制，
http://blog.163.com/digoal@126/blog/static/163877040201321125220134/
http://blog.163.com/digoal@126/blog/static/1638770402012731203716/
http://blog.163.com/digoal@126/blog/static/1638770402012731944439/
我前面的实现是通过在触发器函数中用客户端设置的application_name来区分数据来源，并规避死循环的。
但是这种方法的坏处显而易见，因为触发器还是会被触发，所以性能更低，同时如果其他应用程序使用了同名的远端application_name的话，会导致这个应用程序的DML不会复制到远端。
PostgreSQL提供了一个参数 session_replication_role (enum) 来区分当前数据库的角色是origin还是replica。
可以用于设置master-slave模式的复制，（不能用于设置master-master的复制）。
可以配置为三个值
origin (the default), replica and local。
session_replication_role (enum)
Controls firing of replication-related triggers and rules for the current session.
Setting this variable requires superuser privilege and results in discarding any previously cached query plans.
Possible values are origin (the default), replica and local.
See ALTER TABLE for more information。
这个参数需要结合表的配置来使用：
例如，master配置为origin，slave配置为replica。
触发器在表上面的配置很简单，disable replica triggre trigger_name即可。这样的话，master会触发，slave不会触发。
当主备角色需要切换时，只需要修改数据库的session_replication_role配置，调换一下。而不需要修改表的配置。
ALTER TABLE ......
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER trigger_name
DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE rewrite_rule_name
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
These forms configure the firing of trigger(s) belonging to the table.
A disabled trigger is still known to the system, but is not executed when its triggering event occurs.
For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed.
One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers
(this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints).
Disabling or enabling internally generated constraint triggers requires superuser privileges;
it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed.
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".
Triggers configured as ENABLE REPLICA will only fire if the session is in "replica" mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication mode.
This command acquires a SHARE ROW EXCLUSIVE lock.
DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
These forms configure the firing of rewrite rules belonging to the table.
A disabled rule is still known to the system, but is not applied during query rewriting.
The semantics are as for disabled/enabled triggers.
This configuration is ignored for ON SELECT rules, which are always applied in order to keep views working even if the current session is in a non-default replication role.
参考
1. http://www.postgresql.org/docs/devel/static/sql-altertable.html
2. http://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE
3. http://blog.163.com/digoal@126/blog/static/163877040201321125220134/
4. http://blog.163.com/digoal@126/blog/static/1638770402012731203716/
5. http://blog.163.com/digoal@126/blog/static/1638770402012731944439/