BUG #5911: pg_notify() function only works when channel name is lower case

The following bug has been logged online:

Bug reference: 5911
Logged by: Joshua McDougall
Email address: josh(at)schemaverse(dot)com
PostgreSQL version: 9.0.3
Operating system: Slackware Linux Kernel 2.6.28.6
Description: pg_notify() function only works when channel name is
lower case
Details:

When using the pg_notify(text,text) function, the channel name MUST be lower
case otherwise the message does not go through.

So, while this will work:

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

this will not:
SELECT pg_notify('ERRORCHANNEL','something!');

You must use:
SELECT pg_notify('errorchannel','something!');

On Thu, Mar 3, 2011 at 9:20 AM, Joshua McDougall <josh(at)schemaverse(dot)com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5911
> Logged by:          Joshua McDougall
> Email address:      josh(at)schemaverse(dot)com
> PostgreSQL version: 9.0.3
> Operating system:   Slackware Linux  Kernel  2.6.28.6
> Description:        pg_notify() function only works when channel name is
> lower case
> Details:
>
> When using the pg_notify(text,text) function, the channel name MUST be lower
> case otherwise the message does not go through.
>
> So, while this will work:
>
> LISTEN ERRORCHANNEL;
> NOTIFY ERRORCHANNEL, 'something!';
> NOTIFY eRrorChanNel, 'something!';
>
> this will not:
> SELECT pg_notify('ERRORCHANNEL','something!');
>
> You must use:
> SELECT pg_notify('errorchannel','something!');

not a bug. 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.

merlin

"Joshua McDougall" <josh(at)schemaverse(dot)com> writes:
> When using the pg_notify(text,text) function, the channel name MUST be lower
> case otherwise the message does not go through.

It's not clear to me that this is a bug. The argument of NOTIFY is a
SQL identifier, which is folded to lower case by the lexer if not
double-quoted, but the argument of pg_notify is a string constant which
is a different matter altogether.

We could have pg_notify lowercase its argument at runtime, but then
we'd have to introduce quoting rules, so that you could do

select pg_notify('"IntentionallyMixedCase"', '...');

This isn't a lot clearer than the current behavior, and it definitely
wouldn't be backwards compatible. So I'm inclined to leave it alone.

regards, tom lane

Thank you both for clearing that up (and doing so quite quickly!).
The behavior makes complete sense now that I understand what is
happening here behind the scenes.

Regards,
Josh

On 3/3/2011 11:24 AM, Tom Lane wrote:
> "Joshua McDougall"<josh(at)schemaverse(dot)com> writes:
>> When using the pg_notify(text,text) function, the channel name MUST be lower
>> case otherwise the message does not go through.
> It's not clear to me that this is a bug. The argument of NOTIFY is a
> SQL identifier, which is folded to lower case by the lexer if not
> double-quoted, but the argument of pg_notify is a string constant which
> is a different matter altogether.
>
> We could have pg_notify lowercase its argument at runtime, but then
> we'd have to introduce quoting rules, so that you could do
>
> select pg_notify('"IntentionallyMixedCase"', '...');
>
> This isn't a lot clearer than the current behavior, and it definitely
> wouldn't be backwards compatible. So I'm inclined to leave it alone.
>
> regards, tom lane