在PostgreSQL触发函数中使用pg_notify

我正在尝试从PostgreSQL触发功能发出通知。我可以成功使用NOTIFY命令，但是pg_notify没有任何运气。即使从psql控制台调用pg_notify函数时收到通知，但从触发器函数调用pg_notify函数时也从未收到通知。

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.

我的触发器函数的版本可以正常工作。我有一个监听 mymessage的Java程序，它会收到一条带有 NOTIFY触发有效载荷的通知。

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;

此版本的触发功能无法正常工作。所做的更改取消了pg_notify行的注释，并注释了下面的NOTIFY行。 （我没有修改名为LISTENing的Java应用程序。）我希望我的应用程序LISTENing为 mymessage应收到带有由FUNCTION触发有效负载的通知。实际的行为是，即使在修改了相应的表之后的30秒以上，也什么也没收到。

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;

但是，我真的很困惑，因为相同pg_notify命令可以从psql控制台按预期工作！当我执行以下命令时，我的Java应用程序会收到一条通知，通知中带有由CONSOLE触发有效载荷：

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();

我想使用pg_notify，因为我想拥有一个动态的有效负载。现在，这是一个有争议的问题。 :) Postgres 9.0手册表明应该可行。 有效载荷参数状态的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.)

我还引用了一个相关的Stack Overflow问题，我想我已经回避了这个问题：在PostgreSQL中使用pg_notify（text，text）收听/ NOTIFY 。

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.

数据库版本为：

PostgreSQL 9.0.3，由Visual C ++ build 1500编译，32位

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

我的操作系统是Windows XP Professional，版本2002，SP3。

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

预先感谢。

编辑：在下面添加了我的Java侦听器代码。它基于PostgreSQL文档中的以下示例： http://jdbc.postgresql.org/documentation/81/listennotify.html 。

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();
            }
        }
    }
}

此是一个简单的Java 1.6 SE桌面应用程序，因此我要管理自己的JDBC连接以及所有内容。我通过

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");

我正在使用postgresql-9.0-801.jdbc3.jar库（我的类路径中只有一个）和JDK 1.6.0_22。

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

只需从上面回顾一下，Java代码就可以与psql和触发器中的NOTIFY以及psql中的pg_notify一起很好地工作。

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