在PostgreSQL中使用pg_notify(text, text)进行监听/通知。[英] LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL

本文是小编为大家收集整理的关于在PostgreSQL中使用pg_notify(text, text)进行监听/通知。的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我一直在使用PostgreSQL的通知系统,并且无法确定我为何pg_notify(文本,文本)永远无法使用.此功能没有过多记录,我找不到许多在野外使用的示例,因此我认为没人会介意我在这里问.

运行以下操作完全按预期工作:

LISTEN my_channel;

NOTIFY my_channel, 'my message text';

使用pg_notify()函数,但是返回null值,并且永远不会发送通知.也没有错误.使用的一个示例是:

SELECT pg_notify('my_channel', 'my message text');

我可以使用通知函数,但是我的目标是将通知简化为这样的查询:

select pg_notify(get_player_error_channel(username)::TEXT, 'test'::TEXT)
    from player;

我认为我一定会缺少一些荒谬的东西,但是我的运气却为零.讨论通知的页面可以在此处找到:/static/sql-notify.html

上,它提到了有关pg_notify()的信息,这使我认为没有什么大不相同的.

pg_notify 要发送通知,您还可以使用pg_notify函数(文本,文本).该函数将频道名称作为第一个参数和有效载荷作为第二个参数.如果您需要使用非恒定频道名称和有效负载,则该功能比Notify命令容易得多.

一如既往地感谢您的帮助

编辑:数据库版本是: " I686-PC-Linux-GNU上的PostgreSQL 9.0.3,由GCC GCC(GCC)(GCC)4.2.4,32位编辑"

推荐答案

我已经在PostgreSQL邮件列表上进行了讨论( http://archives.postgresql.org/pgsql-bugs/2011-03/msg00041.php ),并告知行为的推理.

他们的答案是" ..您必须双引号relnames(听"测试").如果您 希望服务器不要折叠它们. pg_notify取一个字符串,而不是 Relname,使用不同的规则."(感谢Merlin和Tom)

这意味着以下作用,因为该通道始终被迫降低案例

LISTEN ERRORCHANNEL;

NOTIFY ERRORCHANNEL, 'something!';
NOTIFY eRrorChanNel, 'something!';

如果您要围绕频道名称添加双引号,则可以维护案例.

因此,在以下内容中,您将收到第一个通知,但不会收到第二个通知:

LISTEN "ERRORCHANNEL";

NOTIFY "ERRORCHANNEL", 'something!'; 
NOTIFY "eRrorChanNel", 'something!';

同样,以下内容将起作用,因为双引号迫使要维护错误渠道的情况:

LISTEN "ERRORCHANNEL";

SELECT pg_notify('ERRORCHANNEL', 'something!');

虽然这不起作用:

LISTEN ERRORCHANNEL;

SELECT pg_notify('ERRORCHANNEL', 'something!');

在这种情况下,错误渠道不在侦听命令中的双引号中,因此PostgreSQL迫使其降低案例.通道参数为类型文本而不是relname,因此在pg_notify()函数中将情况不触及.频道在一起不匹配(errorChanne!= errorChannel),因此永远不会收到通知.

本文地址:https://www.itbaoku.cn/post/597466.html

问题描述

I have been playing with PostgreSQL's notification system and cannot for the life of my figure out why pg_notify(text, text) never works. This feature is not overly documented and I cannot find many examples of it being used in the wild so I figured nobody would mind me asking here.

Running the following works exactly as expected:

LISTEN my_channel;

NOTIFY my_channel, 'my message text';

Using the pg_notify() function however returns a null value and no notification is ever sent. No error is given either. An example of the use is:

SELECT pg_notify('my_channel', 'my message text');

I could use the NOTIFY function however my goal is to streamline the notification into a query like so:

select pg_notify(get_player_error_channel(username)::TEXT, 'test'::TEXT)
    from player;

I assume I must be missing something ridiculous but I have had zero luck figuring out the reason for this. The page discussing NOTIFY can be found here: http://www.postgresql.org/docs/9.0/static/sql-notify.html

On it, it mentions this about pg_notify(), which makes me assume there would be nothing drastically different.

pg_notify To send a notification you can also use the function pg_notify(text, text). The function takes the channel name as the first argument and the payload as the second. The function is much easier to use than the NOTIFY command if you need to work with non-constant channel names and payloads.

Thanks as always for the assistance

Edit: Database version is: "PostgreSQL 9.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.4, 32-bit"

推荐答案

I have discussed this on the PostgreSQL mailing list (http://archives.postgresql.org/pgsql-bugs/2011-03/msg00041.php) and was informed on the reasoning for the behavior.

Their answer is that "..you have to double quote relnames (listen "Test"). if you want the server not to case fold them. pg_notify takes a string, not a relname, which uses different rules." (Thanks Merlin and Tom)

This means that the following works because the channel is always forced to lower case

LISTEN ERRORCHANNEL;

NOTIFY ERRORCHANNEL, 'something!';
NOTIFY eRrorChanNel, 'something!';

If you were to add double quotes around the channel name, the case would be maintained.

So, with the following, you would receive the first notification but not the second:

LISTEN "ERRORCHANNEL";

NOTIFY "ERRORCHANNEL", 'something!'; 
NOTIFY "eRrorChanNel", 'something!';

Similarly, the following will work because the double quotes force the case of ERRORCHANNEL to be maintained:

LISTEN "ERRORCHANNEL";

SELECT pg_notify('ERRORCHANNEL', 'something!');

While this will not work:

LISTEN ERRORCHANNEL;

SELECT pg_notify('ERRORCHANNEL', 'something!');

In this situation ERRORCHANNEL is not in double quotes in the LISTEN command so PostgreSQL forces it to lower case. The channel parameter is of type text rather then relname so the case is left untouched in the pg_notify() function. Together the channels do not match (ERRORCHANNE != errorchannel) so the notification is never received.