title: Using pg_notify in PostgreSQL trigger function
body: <p>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.</p>
  
  <p><strong>This version of my trigger function works as expected.</strong> I have a Java program that is LISTENing to 'mymessage', and it receives a notification with a 'fired by NOTIFY' payload.</p>
  
  <pre><code>-- 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;
  </code></pre>
  
  <p><strong>This version of my trigger function DOES NOT work as expected.</strong> 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.</p>
  
  <pre><code>-- 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;
  </code></pre>
  
  <p><strong>However, I'm <em>really</em> confused, because the same pg_notify command works as expected from the psql console!</strong> When I execute the following command, my Java application receives a notification with a 'fired by CONSOLE' payload:</p>
  
  <pre><code>select pg_notify('mymessage', 'fired by CONSOLE');
  </code></pre>
  
  <p>For completeness, here is my trigger definition:</p>
  
  <pre><code>-- 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();
  </code></pre>
  
  <p>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. <a href="http://www.postgresql.org/docs/9.0/static/sql-notify.html" rel="noreferrer">The NOTIFY docs</a> for the 'payload' parameter state:</p>
  
  <blockquote>
    <p>(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.)</p>
  </blockquote>
  
  <p>I've also referenced a related Stack Overflow question, and I think I've dodged this issue: <a href="https://stackoverflow.com/questions/5173323/listen-notify-using-pg-notifytext-text-in-postgresql">LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL</a>.</p>
  
  <p>The database version is: </p>
  
  <blockquote>
    <p>PostgreSQL 9.0.3, compiled by Visual C++ build 1500, 32-bit</p>
  </blockquote>
  
  <p>My OS is Windows XP Professional, Version 2002, SP3.</p>
  
  <p>Thanks in advance.</p>
  
  <p>EDIT: Added my Java listener code below. It's based on this sample from the PostgreSQL docs: <a href="http://jdbc.postgresql.org/documentation/81/listennotify.html" rel="noreferrer"><a href="http://jdbc.postgresql.org/documentation/81/listennotify.html" rel="noreferrer">http://jdbc.postgresql.org/documentation/81/listennotify.html</a></a>.</p>
  
  <pre><code>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();
              }
          }
      }
  }
  </code></pre>
  
  <p>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</p>
  
  <pre><code>Class.forName("org.postgresql.Driver");
  </code></pre>
  
  <p>I'm using the postgresql-9.0-801.jdbc3.jar library (only one on my classpath), and JDK 1.6.0_22.</p>
  
  <p>Just to recap from above, the Java code works fine with NOTIFY from psql and the trigger, and with pg_notify from psql.</p>