Обсуждение: BUG #5911: pg_notify() function only works when channel name is lower case
BUG #5911: pg_notify() function only works when channel name is lower case
От
"Joshua McDougall"
Дата:
The following bug has been logged online: Bug reference: 5911 Logged by: Joshua McDougall Email address: josh@schemaverse.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!');
Re: BUG #5911: pg_notify() function only works when channel name is lower case
От
Merlin Moncure
Дата:
On Thu, Mar 3, 2011 at 9:20 AM, Joshua McDougall <josh@schemaverse.com> wro= te: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05911 > Logged by: =A0 =A0 =A0 =A0 =A0Joshua McDougall > Email address: =A0 =A0 =A0josh@schemaverse.com > PostgreSQL version: 9.0.3 > Operating system: =A0 Slackware Linux =A0Kernel =A02.6.28.6 > Description: =A0 =A0 =A0 =A0pg_notify() function only works when channel = name is > lower case > Details: > > When using the pg_notify(text,text) function, the channel name MUST be lo= wer > 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@schemaverse.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@schemaverse.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