database 在 PostgreSQL 中使用 pg_notify(text, text) 收听/通知
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5173323/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL
提问by Abstrct
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.
我一直在玩 PostgreSQL 的通知系统，但终生无法弄清楚为什么 pg_notify(text, text) 永远不起作用。这个功能没有被过多记录，我找不到很多在野外使用它的例子，所以我想没有人会介意我在这里问。
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:
然而，使用 pg_notify() 函数会返回一个空值并且不会发送任何通知。也没有给出错误。使用的一个例子是：
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:
我可以使用 NOTIFY 函数，但我的目标是将通知简化为查询，如下所示：
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
我想我一定错过了一些可笑的东西，但我没有找到原因的运气为零。可以在此处找到讨论 NOTIFY 的页面：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() 的这一点，这让我认为不会有什么太大的不同。
pg_notifyTo 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.
pg_notify要发送通知，您还可以使用函数 pg_notify(text, text)。该函数将通道名称作为第一个参数，将有效负载作为第二个参数。如果您需要使用非常量的通道名称和有效负载，该函数比 NOTIFY 命令更易于使用。
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"
编辑：数据库版本是：“PostgreSQL 9.0.3 on i686-pc-linux-gnu，由 GCC gcc (GCC) 4.2.4, 32-bit 编译”
回答by Abstrct
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.
我已经在 PostgreSQL 邮件列表 (http://archives.postgresql.org/pgsql-bugs/2011-03/msg00041.php) 上讨论过这个问题，并了解了这种行为的原因。
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)
他们的回答是“..你必须双引号 relnames（听“测试”）。如果你想让服务器不折叠它们。pg_notify 需要一个字符串，而不是一个使用不同规则的 relname。（感谢梅林和汤姆）
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:
同样，以下将起作用，因为双引号强制维护 ERRORCHANNEL 的情况：
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.
在这种情况下， ERRORCHANNEL 不在 LISTEN 命令中的双引号中，因此 PostgreSQL 强制其为小写。通道参数的类型是 text 而不是 relname ，因此在 pg_notify() 函数中保持大小写不变。通道一起不匹配 (ERRORCHANNE != errorchannel)，因此永远不会收到通知。
