Postgres: No payload received when using pg_notify with `node-postgres` Javascript library

Question

I have specified a function that is executed after inserting a row to a table. This function uses pg_notify function to notify channel jobqueue with JSON payload of new row.

job_notifier function:

  CREATE FUNCTION job_notifier() RETURNS TRIGGER AS $$
  BEGIN
    PERFORM pg_notify('jobqueue', row_to_json(NEW)::text);
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;

This is the trigger: CREATE TRIGGER job_created AFTER INSERT ON jobs EXECUTE FUNCTION job_notifier();

The jobs table is made up of several columns.

I'm using node-postgres library for accessing the database. One of my JS clients created with this library receives the message so I know the function is triggered at the right moment.

However the payload is empty string. When I replace row_to_json(NEW)::text with a simple string like 'abcd' I get this payload so for some reason the conversion to JSON object fails.

I am not sure if this is a problem with my database function but I suspect it might be issue with the Javascript library.

Has anyone used node-postgres for this kind of setup? The database runs inside docker container (official postgres image from Dockerhub), maybe I could enable some kind of logging to see if the payload is being generated in the database?

jjanes · Accepted Answer · 2020-03-02 22:47:20Z

1

The default for a trigger is to be fired once for each statement, for which NEW is set to NULL, resulting in no payload. If you want your trigger to fire once for each row, with NEW populated, you must specify it.

CREATE TRIGGER job_created AFTER INSERT ON jobs FOR EACH ROW 
    EXECUTE FUNCTION job_notifier();

answered Mar 2, 2020 at 22:47

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres: No payload received when using pg_notify with `node-postgres` Javascript library

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related