3

I am trying to setup a simple LISTEN/NOTIFY functionality in PostgreSQL and notify node.js code using pg-promise library.

The pg code I have scaffolded is

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $$
DECLARE
BEGIN
  RAISE NOTICE '%', 'HI';
  PERFORM pg_notify('watchers', TG_TABLE_NAME || ', tags:,' || NEW.tags );
  RETURN new;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER watched_table_trigger BEFORE INSERT OR UPDATE ON assets
FOR EACH ROW EXECUTE PROCEDURE notify_trigger();

It does notify on psql console.

However when I use pg-promise code, it doesn't output console messages as I would hope. The relevant node.js code:

const pgoptions = require('./pgoptions.config.js');
const connectObject = require('./pgconnect.config.js');
const db = require('pg-promise')(pgoptions)(connectObject);

// added listener code for pg listen / notify
db.connect({direct: true})
    .then(function (sco) {
        sco.client.on('assets', function (data) {
            console.log('Received: ', data);
    });
    return sco.none('LISTEN assets');
})
.catch(function (error) {
    console.error('Error:', error);
});

module.exports = resources;

The nodejs code straight out of the pg-promise's Learn By Example documentation here. I did review the other posts about this topic, but didn't find the pg-promise-based solution.

Community
  • 1
  • 1
Piyush Mehta
  • 53
  • 1
  • 8

1 Answers1

3

It is not

sco.client.on('assets', function (data) {

it is

sco.client.on('notification', function (data) {

i.e. you are subscribing to the generic notification message, not to the channel name. That's how it is shown the very example you quoted.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138
  • I made that change and still do not get a notification back. Anything else I should look for? – Piyush Mehta Aug 01 '17 at 01:02
  • @PiyushMehta It looks like you are raising on the `watchers` channel, while issuing `LISTEN` on channel `assets` instead. – vitaly-t Aug 01 '17 at 01:04
  • Thanks, that did it. – Piyush Mehta Aug 01 '17 at 01:14
  • upvoted! as per the guide here https://github.com/vitaly-t/pg-promise/wiki/Robust-Listeners I keep getting this warning WARNING: Creating a duplicate database object for the same connection. is this expected – PirateApp Jun 24 '20 at 13:23
  • 1
    @PirateApp https://stackoverflow.com/questions/34382796/where-should-i-initialize-pg-promise – vitaly-t Jun 24 '20 at 14:36