LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL

Question

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"

Does entering `LISTEN my_channel;` `SELECT pg_notify('my_channel', 'my message text');` into a psql session not output anything? It does for me, the result of pg_notify is accompanied by a "Asynchronous notification..." message. — araqnid, Mar 02 '11 at 21:33
Thanks araqnid, this helped assure me I wasn't going crazy! Unfortunately, it also helps make me look a tad stupid. O well. My answer is below. — Abstrct, Mar 02 '11 at 21:44
Don't feel bad Abstrct, the answer you gave does highlight an understanding of the way the parser works, which not many know. Heck, something new for me and would explain a few non-firing notifications for me too, so good answer. :) — Guy Park, May 24 '17 at 02:18

score 40 · Accepted Answer · edited Jul 01 '22 at 23:25

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.

LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL

1 Answers1

Linked