PostgreSQL Trigger and Notification Function Not Working as Expected

Question

Question:

I have set up a PostgreSQL function and triggers to send notifications when there are INSERT, UPDATE, or DELETE operations on the RiderDataPosition table. However, despite my efforts, I'm not receiving any notifications as expected. I'm seeking guidance on troubleshooting this issue. My end result would be that I would like to get a notification when something in the RiderDataPosition table changes.

UPDATE

When sending a query NOTIFY RiderPositionUpdate to the database, my test application receives a response. That means something is going wrong with the trigger!

Problem Description:

I have a PostgreSQL database running in a Docker container, and I'm using the latest version. The goal is to send notifications to the RiderPositionUpdate channel whenever there's a change in the RiderDataPosition table. Here's what I've done so far:

1. Notification Function:

   I've created a notification function as follows:

   CREATE OR REPLACE FUNCTION "TrackSolutionsCore".notify_rider_position_change()
   RETURNS TRIGGER AS $$
   BEGIN
       IF TG_OP = 'INSERT' THEN
           PERFORM pg_notify('RiderPositionUpdate', json_build_object('operation', 'insert', 'record_id', NEW."TransponderId")::text);
           RAISE NOTICE 'Inserted record with TransponderId %', NEW."TransponderId";
           RETURN NEW;
       ELSIF TG_OP = 'UPDATE' THEN
           PERFORM pg_notify('RiderPositionUpdate', json_build_object('operation', 'update', 'record_id', NEW."TransponderId")::text);
           RAISE NOTICE 'Updated record with TransponderId %', NEW."TransponderId";
           RETURN NEW;
       ELSIF TG_OP = 'DELETE' THEN
           PERFORM pg_notify('RiderPositionUpdate', json_build_object('operation', 'delete', 'record_id', OLD."TransponderId")::text);
           RAISE NOTICE 'Deleted record with TransponderId %', OLD."TransponderId";
           RETURN OLD;
       END IF;
   END;
   $$ LANGUAGE plpgsql;
   

2. Triggers:

   I've created triggers for AFTER INSERT, AFTER UPDATE, and AFTER DELETE operations on the RiderDataPosition table. Here's an example of one of those triggers:

   CREATE TRIGGER rider_data_position_insert_trigger
   AFTER INSERT ON "TrackSolutionsCore"."RiderDataPosition"
   FOR EACH ROW
   EXECUTE FUNCTION "TrackSolutionsCore".notify_rider_position_change();
   

3. Table Structure:

   The RiderDataPosition table has the following structure:

   CREATE TABLE "RiderDataPosition"
   (
       "TransponderId" integer generated by default as identity
           constraint "PK_RiderDataPosition"
               primary key,
       -- Other columns...
   );
   

Issues Faced:

Despite setting up these triggers and the notification function, I'm not receiving any notifications on the RiderPositionUpdate channel when I perform CRUD operations on the RiderDataPosition table. I've attempted to receive these notifications through a C# application using the NotificationEventHandler and directly via DataGrip by executing LISTEN "RiderPositionUpdate" in the console, but neither approach yields results.

Environment:

• PostgreSQL version: [16]
• Docker Engine: [v24.0.6]

Test Application

using Npgsql;

var conn = new NpgsqlConnection("connectionString");
conn.Open();
conn.Notification += (o, e) => Console.WriteLine("Received notification");

using (var cmd = new NpgsqlCommand("LISTEN RiderPositionUpdate", conn)) {
    cmd.ExecuteNonQuery();
}

while (true) {
    conn.Wait();   // Thread will block here
}

Output in DataGrip after inserting a new record into the table

Question:

1. What could be causing this issue, and how can I troubleshoot it further?
2. Are there any specific PostgreSQL configurations or Docker settings I should check to ensure that notifications work correctly?
3. Is there a way to verify that notifications are being sent by the triggers and function, perhaps through PostgreSQL logs or a different method?
4. Are there any common pitfalls or mistakes in setting up PostgreSQL triggers and notification functions that I might be overlooking?

Any insights or guidance on how to diagnose and resolve this issue would be greatly appreciated. Thank you!

Answer 1

You have a case mismatch. If your channel name contains real capital letters, that name must be enclosed in double quotes in your LISTEN command. Your NOTIFY command works with your LISTEN, as it case-folds the channel name same as LISTEN does, while pg_notify does not case-field its first argument so it doesn't work.

But better yet, change your channel name so it doesn't have capital letters in it.