Using pg_notify in PostgreSQL trigger function

Question

I am attempting to issue a notification from a PostgreSQL trigger function. I can successfully use the NOTIFY command, but I am not having any luck with pg_notify. Even though I receive a notification when I invoke the pg_notify function from the psql console, I never receive a notification when invoking the same from my trigger function.

This version of my trigger function works as expected. I have a Java program that is LISTENing to 'mymessage', and it receives a notification with a 'fired by NOTIFY' payload.

-- Function: conversation_notify()

-- DROP FUNCTION conversation_notify();

CREATE OR REPLACE FUNCTION conversation_notify()
  RETURNS trigger AS
$BODY$
    BEGIN
        --SELECT pg_notify('mymessage', 'fired by FUNCTION');
        NOTIFY mymessage, 'fired by NOTIFY';
        RETURN NULL;
    END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION conversation_notify() OWNER TO postgres;

This version of my trigger function DOES NOT work as expected. The only changes are uncommenting the pg_notify line and commenting out the NOTIFY line below. (I did not modify the Java application that is LISTENing.) I expect that my application LISTENing to 'mymessage' should receive a notification with a 'fired by FUNCTION' payload. The actual behavior is that nothing is received, even 30+ seconds after the corresponding table is modified.

-- Function: conversation_notify()

-- DROP FUNCTION conversation_notify();

CREATE OR REPLACE FUNCTION conversation_notify()
  RETURNS trigger AS
$BODY$
    BEGIN
        SELECT pg_notify('mymessage', 'fired by FUNCTION');
        --NOTIFY mymessage, 'fired by NOTIFY';
        RETURN NULL;
    END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION conversation_notify() OWNER TO postgres;

However, I'm really confused, because the same pg_notify command works as expected from the psql console! When I execute the following command, my Java application receives a notification with a 'fired by CONSOLE' payload:

select pg_notify('mymessage', 'fired by CONSOLE');

For completeness, here is my trigger definition:

-- Trigger: conversation_notify on ofconversation

-- DROP TRIGGER conversation_notify ON ofconversation;

CREATE TRIGGER conversation_notify
  AFTER INSERT OR UPDATE
  ON ofconversation
  FOR EACH ROW
  EXECUTE PROCEDURE conversation_notify();

I'm trying to use pg_notify because I would like to have a dynamic payload. Right now, that's a moot point. :) The Postgres 9.0 manual indicates that this should be possible. The NOTIFY docs for the 'payload' parameter state:

(If binary data or large amounts of information need to be communicated, it's best to put it in a database table and send the key of the record.)

I've also referenced a related Stack Overflow question, and I think I've dodged this issue: LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL.

The database version is:

PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit

My OS is Windows XP Professional, Version 2002, SP3.

Thanks in advance.

EDIT: Added my Java listener code below. It's based on this sample from the PostgreSQL docs: http://jdbc.postgresql.org/documentation/81/listennotify.html.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.postgresql.PGConnection;
import org.postgresql.PGNotification;

public class ConversationListener extends Thread
{   
    private Connection conn;
    private PGConnection pgConn;

    public ConversationListener(Connection conn) throws SQLException
    {
        this.conn = conn;
        this.pgConn = (PGConnection) conn;
        Statement listenStatement = conn.createStatement();
        listenStatement.execute("LISTEN mymessage");
        listenStatement.close();
    }

    @Override
    public void run()
    {
        while (true)
        {
            try
            {
                // issue a dummy query to contact the backend
                // and receive any pending notifications.
                Statement selectStatement = conn.createStatement();
                ResultSet rs = selectStatement.executeQuery("SELECT 1");
                rs.close();
                selectStatement.close();

                PGNotification notifications[] = pgConn.getNotifications();

                if (notifications != null)
                {
                    for (PGNotification pgNotification : notifications)
                    {
                        System.out.println("Got notification: " + pgNotification.getName() +
                            " with payload: " + pgNotification.getParameter());
                    }
                }

                // wait a while before checking again
                Thread.sleep(500);
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            catch (InterruptedException ie)
            {
                ie.printStackTrace();
            }
        }
    }
}

This is a simple Java 1.6 SE desktop application, so I'm managing my own JDBC connection and everything. I'm loading the driver via

Class.forName("org.postgresql.Driver");

I'm using the postgresql-9.0-801.jdbc3.jar library (only one on my classpath), and JDK 1.6.0_22.

Just to recap from above, the Java code works fine with NOTIFY from psql and the trigger, and with pg_notify from psql.

Solution 1

This might be to late to help but perhaps someone else will be able to use it. Using SELECT pg_notify('', ''); in the trigger causes the DB to respond with

ERROR: query has no destination for result data
SQL state: 42601
Hint: If you want to discard the results of a SELECT, use PERFORM instead.

Changing the SELECT to PERFORM as the error say helps to resolve this issue and the notification gets delivered as expected. Perhaps this could have been the problem.

I have the same setup, and had the same problem.

Solution 2

It might be useful to someone out there. Sometimes you want to pass whole row to "observer" and then it might be a nice idea to serialise whole row into JSON. You can achieve this with help of row_to_json

-- Notify when record was inserted into 'prices' table
CREATE OR REPLACE FUNCTION notify_pricesinserted()
  RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify(
    CAST('pricesinserted' AS text),
    row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER notify_pricesinserted
  AFTER INSERT ON prices
  FOR EACH ROW
  EXECUTE PROCEDURE notify_pricesinserted();

Solution 3

CREATE OR REPLACE FUNCTION notifyshipment() RETURNS trigger AS $$
DECLARE
BEGIN
  PERFORM pg_notify(CAST('snc' AS text),CAST(NEW.id AS text)|| ' ' || CAST(NEW.tracking_number AS text));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER shipmentnotify AFTER UPDATE ON shipments FOR EACH ROW EXECUTE PROCEDURE notifyshipment();

Solution 4

You can use the following code directly into your create trigger function:

EXECUTE 'NOTIFY your_declared_notify';        

OR

 PERFORM pg_notify(CAST('your_declared_notify' AS text), CAST(NEW.nameAS text));

Solution 5

I don't know if these help with your problem, but some gotcha's I've hit are:

• You have to commit the transaction with the LISTEN command. I'm not familiar with Java, I don't know if you're in autocommit mode or not.
• Notifies are dispatched when you commit. I suppose for whatever reason, it could be that the transaction that triggered calling pg_notify did not commit or was rolled back?
• Maybe the LISTEN connection is connecting to another database than the one where NOTIFY is sent? :)

However, none of these can explain why NOTIFY works and pg_notify didn't.

View more solutions

Comments

[Kjetil S.]

Why CAST('pricesinserted' AS text) when just 'pricesinserted' also works? (for me).

[BigFilhao]

@KjetilS. maybe because of pg versions. He answered in 2015.