digoal
2021-07-08
PostgreSQL , 逻辑复制 , 触发器
primary node
logical replication node
如果在primary node有一个表, 表上有个触发器. 那么当数据复制到logical replication node时, 逻辑节点这个表上的触发器会不会被执行呢?
答案是和触发器的设置、参数有关.
如果使用的是内置的sub, pub方法进行订阅, 在逻辑复制的回放节点, 会话参数 session_replication_role=replica. 如果你在逻辑复制回放节点的trigger是默认方法创建的, 即没有指定REPLICA | ALWAYS, 那么这个trigger就不会触发.
trigger = default , 只有当会话参数 session_replication_role=local, origin时 触发.
trigger = REPLICA , 只有当会话参数 session_replication_role=replica时 触发.
trigger = always , 任何时候都触发.
https://www.postgresql.org/docs/devel/sql-altertable.html
alter table
DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
The trigger firing mechanism is also affected by the configuration variable session_replication_role.
Simply enabled triggers (the default) 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 role.
The effect of this mechanism is that in the default configuration, triggers do not fire on replicas.
This is useful because if a trigger is used on the origin to propagate data between tables,
then the replication system will also replicate the propagated data, and the trigger should not fire a second time on the replica,
because that would lead to duplication.
However, if a trigger is used for another purpose such as creating external alerts,
then it might be appropriate to set it to ENABLE ALWAYS so that it is also fired on replicas.
https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE
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.
The intended use of this setting is that logical replication systems set it to replica when they are applying replicated changes.
The effect of that will be that triggers and rules (that have not been altered from their default configuration) will not fire on the replica.
See the ALTER TABLE clauses ENABLE TRIGGER and ENABLE RULE for more information.
PostgreSQL treats the settings origin and local the same internally.
Third-party replication systems may use these two values for their internal purposes,
for example using local to designate a session whose changes should not be replicated.
Since foreign keys are implemented as triggers, setting this parameter to replica also disables all foreign key checks,
which can leave data in an inconsistent state if improperly used.
src/backend/replication/logical/worker.c
/*-------------------------------------------------------------------------
* worker.c
* PostgreSQL logical replication worker (apply)
*
* Copyright (c) 2016-2021, PostgreSQL Global Development Group
*
* IDENTIFICATION
* src/backend/replication/logical/worker.c
*
* NOTES
* This file contains the worker which applies logical changes as they come
* from remote logical replication stream.
*
* The main worker (apply) is started by logical replication worker
* launcher for every enabled subscription in a database. It uses
* walsender protocol to communicate with publisher.
*
* This module includes server facing code and shares libpqwalreceiver
* module with walreceiver for providing the libpq specific functionality.
*
*
* STREAMED TRANSACTIONS
* ---------------------
...
/* Logical Replication Apply worker entry point */
void
ApplyWorkerMain(Datum main_arg)
{
...
/* Run as replica session replication role. */
SetConfigOption("session_replication_role", "replica",
PGC_SUSET, PGC_S_OVERRIDE);
...
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
