I'm trying to problem a notification from the PostgreSQL trigger function. I'm able to effectively make use of the Inform command, but I'm not getting any luck with pg_inform. Despite the fact that I get a notification after i invoke the pg_inform function in the psql console, I never get a notification when invoking exactly the same from the trigger function.

This version of my trigger function works not surprisingly. I've got a Java program that's Hearing 'mymessage', also it gets to be a notification having 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 Doesn't work not surprisingly. The only real changes are uncommenting the pg_inform line and leaving comments the Inform line below. (I didn't customize the Java application that's LISTENing.) I expect that my application Hearing 'mymessage' should get a notification having a 'fired by FUNCTION' payload. The particular behavior is the fact that there is nothing received, even 30+ seconds following 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 am really confused, since the same pg_inform command works not surprisingly in the psql console! After I execute the next command, my Java application gets to be a notification having a 'fired by CONSOLE' payload:

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

For completeness, here's 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 am attempting to use pg_inform because I must possess a dynamic payload. At this time, this is a moot point. :) The Postgres 9. manual signifies that this ought to be possible. The NOTIFY docs for that 'payload' parameter condition:

(If binary data or considerable amounts of knowledge have to be conveyed, it is best to place it inside a database table and send the important thing from the record.)

I have also recommended an associated Stack Overflow question, and i believe I have dodged this problem: LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL.

The database version is:

PostgreSQL 9..3, put together by Visual C++ build 1500, 32-bit

My OS is Home windows XP Professional, Version 2002, SP3.

Thanks ahead of time.

EDIT: Added my Java listener code below. It's according to this sample in the PostgreSQL paperwork: 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 can be a simple Java 1.6 SE desktop application, so I am controlling my very own JDBC connection and everything. I am loading the motive force via

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

I am while using postgresql-9.-801.jdbc3.jar library (just one on my small classpath), and JDK 1.6._22.

Simply to recap previously mentioned, the Java code works fine with Inform from psql and also the trigger, with pg_inform from psql.