2

I've read https://www.postgresql.org/docs/9.6/static/sql-notify.html and the channel name is just described as an 'identifier'.

I'm using NodeJS and pg https://www.npmjs.com/package/pg to access postgres.

If I use a lower case word in both, e.g. pg_notify('foo', ... and LISTEN foo it works. I've tested all combinations:

pg_notify  LISTEN  outcome
lower      lower   works
lower      upper   works
upper      lower   fails
upper      upper   fails

Is this a bug, or is it a logical result of being an 'identifier'? (If so, should this be documented on pg_notify page?)

fadedbee
  • 42,671
  • 44
  • 178
  • 308
  • 1
    `pg_notify` is a function - so it is an identifier and works despite the case (unless you double quote it of course). what page you want it fixed at?.. – Vao Tsun May 26 '17 at 10:03
  • @VaoTsun Sorry, I haven't understood all of your comment. It *is* quoted, while I'm testing this issue, and is a string from a table when live. I would like a note on https://www.postgresql.org/docs/9.6/static/sql-notify.html sating that if you use an upper-case identifier string, then it will not work. – fadedbee May 26 '17 at 10:12
  • 1
    please share your not working code. ```t=# select PG_NOTIFY('a','a'); pg_notify ----------- (1 row) ``` works fine. If you double quote PG_NOTIFY to be "PG_NOTIFY" it will fail. and there is no reason to do "PG_NOTIFY". Do I understand right the issue? – Vao Tsun May 26 '17 at 10:14
  • 1
    He means that this will not work: `LISTEN Virtual; SELECT pg_notify('Virtual', 'payload');` with is correct. But he doesn't know how object identifiers work in pg and that this will work: `LISTEN "Virtual"; SELECT pg_notify('Virtual', 'payload');` – Łukasz Kamiński May 26 '17 at 10:18
  • @VaoTsun The reason that I am testing `pg_notify('FOO', ...` is that `pg_notify(concat(NEW.region, ...` isn't working and NEW.region is in capitals. – fadedbee May 26 '17 at 10:18
  • 1
    @chrisdew - sorry - I would never decode that from your post :) Lukasz - thank you! – Vao Tsun May 26 '17 at 10:21
  • @ŁukaszKamiński `listen 'Virtual'` fails with: ` events.js:160 throw er; // Unhandled 'error' event ^ error: syntax error at or near "'Virtual'" at Connection.parseE (/home/chris/node_modules/pg/lib/connection.js:572:11) at Connection.parseMessage (/home/chris/node_modules/pg/lib/connection.js:396:17) at Socket. (/home/chris/node_modules/pg/lib/connection.js:132:22) at emitOne (events.js:96:13) at Socket.emit (events.js:188:7)` – fadedbee May 26 '17 at 10:21
  • 1
    @chrisdew `listen 'Virtual'` is very much different from `listen "Virtual"` - first is liesten to a string, the other is listen to identifier, If you would not have case mixed, could be without quotes – Vao Tsun May 26 '17 at 10:23
  • 1
    @chrisdew youn you pass 'Virtual' to `pg_notify` function, you pass a string, so it is in single quotes. when you run `NOTIFY "Virtual"` you use virtaul as channel name - an identifier – Vao Tsun May 26 '17 at 10:25
  • @VaoTsun You've found the issue, changing my code from `client.query("LISTEN 'Virtual'");` to `client.query('LISTEN "Virtual"');`fixes it all. Make this an answer and I'll accept it. – fadedbee May 26 '17 at 10:25
  • 1
    try ` select pid,'pid',"pid" from pg_stat_activity limit 1;` to see how it works – Vao Tsun May 26 '17 at 10:27

2 Answers2

6

confusion comes from quotes. single quotes are used for a string, double are used for identifier and can be skipped to none if you don't use mixed case/start from number/ or other tricks.

when you run pg_notify function you pass 'foo' as a string argument and thus use single quotes, when you run NOTIFY "Virtual" you use Virtual as channel name - an identifier, so you need to use " here.

So as you answered yourself, changing 

client.query("LISTEN 'Virtual'");

to 

client.query('LISTEN "Virtual"');

fixes an issue

https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
3

You should look for an identifier definition: Identifiers and Key Words.

Unquoted identifiers are case insensitive in that sense that regardless of how they are written by a user, Postgres sees them as in lowercase. In the function pg_notify() the first argument is not an identifier but a text literal which is treated as an identifier without parsing, so it is not converted to lowercase.

klin
  • 112,967
  • 15
  • 204
  • 232