Postgres Notify不适用于逻辑复制 [英] Postgres Notify not working with logical replication

问题描述

我正在使用逻辑复制将数据从Postgres 10.4复制到另一个Postgres 10.4实例。

I am replicating data from Postgres 10.4 to another Postgres 10.4 instance using logical replication.

订户具有多个将事件记录到单个表的触发器。该表具有一个触发器，该触发器执行另一个函数（返回触发器）以为下游侦听器调用NOTIFY。

The subscriber has several triggers that log events to a single table. This table has a trigger that executes another function (that returns a trigger) to call NOTIFY for a downstream listener.

审计表上的触发器如下：

Trigger on the audit table looks like this:

CREATE TRIGGER queue_insert
    AFTER INSERT ON schema_name.table_name FOR EACH ROW
     EXECUTE PROCEDURE notify_downstream()
GO

通知下游定义：

CREATE OR REPLACE FUNCTION schema_name.notify_downstream () RETURNS trigger AS
'
declare
message character varying;
begin

raise log ''notify running!'';

message := ''
{ "id": 'edited for brevity' }

'';
execute ''notify chan_name, '''''' || message || '''''''';

raise log ''Value: %'', message;
return new;
end;
'
LANGUAGE 'plpgsql'
GO

使用日志记录，我可以证明这种情况正在触发。我还可以看到有数据在使用：

Using the logging, I'm able to prove that this is firing. I can also see that there is data using:

select pg_notification_queue_usage()

问题在于，直到我将插入表（读为：在逻辑复制之外）以触发触发器之前，没有任何侦听器会收到消息。

The problem is that none of the listeners get the message until I insert into the table (read as: outside of logical replication) to make the trigger fire, then the listener receives all of the messages that notify should have sent from logical replication.

在我们转向逻辑复制之前，所有这些方法都运行良好（我们有一个自行开发的解决方案，该解决方案退休了，对此我一无所知。

All of this worked well until we moved to logical replication (we had a home grown solution that was retired and I don't know anything about it).

我没有收到任何错误或奇怪的消息，它们可能给我任何线索。我也出现了详细的日志记录，除了我添加到函数以验证它们正在运行的日志语句之外，没有看到与通知相关的任何内容。

I receive no errors or strange messages that could give me any clues. I turned up verbosity of the logging as well and see nothing related to Notify other than the log statements I added to the functions to verify that they are running.

另一份来自堆栈溢出中的某人：在PG逻辑复制表上从触发器通知

Another report from someone on Stack Overflow: Notify From Trigger On PG Logical Replicated Table

问题：如何调试此问题？如何让监听者无需手动插入行即可让消息接收者突然出现？

Question: How do I debug this issue? How do I get the listeners to receive the messages without manually inserting a row to have them appear all of a sudden?

推荐答案

更新：看来这是PostgreSQL 10.4中的 bug ，并且至少不超过 11.4 。 此处

Update: It looks like this is a bug with PostgreSQL 10.4, and at least up to at least 11.4. There's an experimental patch available here.

---

根据此帖子看起来默认情况下，逻辑复制通常不会导致触发器在副本上触发

---

According to this post on the PostgreSQL mailing list it looks like by default logical replication won't cause triggers to fire on replicas because tables generally have the "local" replication role and on logical replicas the data gets inserted with the "replica" role.

看起来您可以更改表以始终触发触发器，包括通过执行以下操作进行复制（请参见文档此处） ：

It looks like you can alter your table to always fire triggers, including on replication by doing the following (see the documentation here):

ALTER TABLE my_table ENABLE ALWAYS TRIGGER my_trigger;

这篇关于Postgres Notify不适用于逻辑复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！