PostgreSQL notifications aren't working

Question

I'm trying to use PostgreSQL notifications but they are not working for me.

Docker-Compose

docker-compose.yml

version: "3.8"
secrets:
  db_root:
    file: ./db/.secrets/.psql_name_admin
  db_pwd:
    file: ./db/.secrets/.psql_pwd_admin
  db_dbname:
    file: ./db/.secrets/.psql_name_db
services: 
  db:
    build: 
      context: ./db
    restart: unless-stopped
    secrets:
      - db_root
      - db_pwd
      - db_dbname
    environment:
      - POSTGRES_DB_FILE=/run/secrets/db_dbname
      - POSTGRES_USER_FILE=/run/secrets/db_root
      - POSTGRES_PASSWORD_FILE=/run/secrets/db_pwd
    ports: 
      - 5442:5432
    volumes: 
      - ./db/data:/var/lib/postgresql/data

./db/Dockerfile

FROM postgres:latest
COPY init/*.sql /docker-entrypoint-initdb.d/

./db/init/0_blogpost.sql

CREATE TYPE BLOGPOST_ENUM AS ENUM ('Technical', 'Agile');
CREATE TABLE writer (
    id SERIAL PRIMARY KEY ,
    name TEXT NOT NULL ,
    surname TEXT NOT NULL ,
    nick TEXT
);
CREATE TABLE blog_post (
    id SERIAL PRIMARY KEY ,
    title TEXT NOT NULL ,
    content TEXT NOT NULL ,
    blogpost_type BLOGPOST_ENUM NOT NULL ,
    writer_id SERIAL REFERENCES writer(id)
);
CREATE OR REPLACE FUNCTION blogpost_added_subscription()
RETURNS trigger AS
$$
BEGIN
    PERFORM pg_notify(
        'blogpost_added' ,
--      json_build_object(
--          'event' , 'newBlogpostAdded' ,
--          'subject' , new.id
--      )::text
    new.id::text
    );
RETURN NEW;

END;
$$ LANGUAGE 'plpgsql';
CREATE OR REPLACE TRIGGER notifiy_after_blogpost_insert
  AFTER INSERT ON blog_post
  FOR EACH ROW
  EXECUTE PROCEDURE blogpost_added_subscription()
;
INSERT INTO writer (name, surname) VALUES ('Emanuel', 'Vollmer');
INSERT INTO blog_post (title, content, blogpost_type, writer_id)
VALUES (
 'Graphql',
 'Graphql is a query language...',
 'Technical',
 (SELECT id FROM writer WHERE name = 'Emanuel')
);
INSERT INTO blog_post (title, content, blogpost_type, writer_id)
VALUES (
 'Flutter',
 'Flutter is a framework for Dart...',
 'Technical',
 (SELECT id FROM writer WHERE name = 'Emanuel')
);

Working with pgcli

I opened two sessions with

$ pgcli -h 0.0.0.0 -p 5442 -U sysmanager -d demo_db

On session 1 I run

sysmanager@0:demo_db> listen blogpost_added;
LISTEN
Time: 0.001s

On session 2 I run

sysmanager@0:demo_db> INSERT INTO blog_post (title, content, blogpost_type, writer_id)
 VALUES (
  'PostgreSQL Notifiers',
  'PostgreSQL is a powerful, open source object-relational database system...',
  'Technical',
  (SELECT id FROM writer WHERE name = 'Emanuel')
 );
INSERT 0 1
Time: 0.021s

But I'm not receiving any notifications on Session 1, Am I missing something?

Answer 1

The current version of pgcli does not display notifications.

There is an open issue about this feature.

The solution is to use psql instead.

Note that notifications are not displayed in psql immediately as they're produced by the server, but when there are interactions with the database. When psql is waiting for user input, it's not waken up by incoming notifications.

From the documentation:

Whenever a command is executed, psql also polls for asynchronous notification events generated by LISTEN and NOTIFY.