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

问题描述

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.