Support log message for NOTIFY/LISTEN

[rliebz]

Feature

After running LISTEN, psql gives a text notification whenever the NOTIFY command is run on that channel. usql gives no indication that anything occurred.

I would like to see some indication with usql that a NOTIFY event has occurred. The exact text/format isn't relevant for my use case, as long as I can see the message come through.

Reproduction

With psql:

mydb=# LISTEN foo;
LISTEN
mydb=# NOTIFY foo;
NOTIFY
Asynchronous notification "foo" received from server process with PID 82080.
mydb=# NOTIFY foo, 'bar';
NOTIFY
Asynchronous notification "foo" with payload "bar" received from server process with PID 83069.

With usql:

px:admin@localhost/mydb=> LISTEN foo;
LISTEN
px:admin@localhost/mydb=> NOTIFY foo;
NOTIFY
px:admin@localhost/mydb=> NOTIFY foo, 'bar';
NOTIFY

[kenshaw]

You're using the wrong driver. Support for LISTEN was added 3 years ago. Try using the postgres driver:

pg:postgres@localhost=> LISTEN blah;
LISTEN
pg:postgres@localhost=> 
pg:postgres@localhost=> select 1;
Asynchronous notification "blah" with payload "hi" received from server process with PID 64.
 ?column? 
----------
        1 
(1 row)

pg:postgres@localhost=> 

Due to the nature of the way the underlying driver works, the receiving client won't get the message until a query of some kind has been executed against the PostgreSQL database. In the above, you can see the SELECT 1 that was called.

[kenshaw]

Sorry, just to clarify: as you did not share what you used to connect to the database, I was able to surmise based off the short prefix (px) from usql's interactive prompt that you connected using the pgx driver, which has its short prefix as px. The "more supported" github.com/lib/pq driver supports NOTICE, RAISE, etc.

I'll look into pgx and see if it's possible to do the same.

[rliebz]

Thanks for the quick response! You are correct and it shouldn't be a problem for me to use postgres over pgx.

For posterity, the command I ran was:

go run -tags 'no_base pgx' github.com/xo/usql@latest \
  "pgx://${PGUSER}:${PGPASSWORD}@${PGHOST}:${PGPORT}/${PGDATABASE}"

And with postgres, it works perfectly:

go run -tags 'no_base postgres' github.com/xo/usql@latest \
  "postgres://${PGUSER}:${PGPASSWORD}@${PGHOST}:${PGPORT}/${PGDATABASE}"

[kenshaw]

I was able to create the same functionality with pgx. This will be rolled up into the next release:

px:postgres@=> notify blah, 'yes';
Asynchronous notification "blah" with payload "yes" received from server process with PID 178.
NOTIFY
px:postgres@=>  

As I'm not yet ready to push a release, if you would like to use this immediately, then you'll need to build from source.

(after I push the update that is...)