Database 在 PostgreSQL 触发函数中使用 pg_notify

我正在try 从PostgreSQL触发器函数发出通知.我可以成功地使用NOTIFY命令，但是我使用pg_NOTIFY没有任何成功.即使在从psql控制台调用pg_NOTIFY函数时收到通知，从触发器函数调用相同的函数时也从未收到通知.

This version of my trigger function works as expected.我有一个Java程序正在侦听"mymessage"，它接收到一个带有"由通知触发"有效负载的通知.

-- 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.唯一的更改是取消pg_notify行的注释，并注释掉下面的notify行.(我没有修改正在侦听的Java应用程序.)我希望监听"mymessage"的应用程序会收到一个带有"按函数触发"负载的通知.实际情况是，即使在修改相应的表30多秒后，也不会收到任何内容.

-- 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!当我执行以下命令时，我的Java应用程序收到一个带有"由控制台触发"有效负载的通知:

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

为了完整起见，以下是我对触发器的定义:

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

我正在try 使用pg_notify，因为我想要一个动态负载.现在，这是一个没有实际意义的观点.:)Postgres 9.0手册指出这应该是可能的.The NOTIFY docs表示"有效载荷"参数状态:

(如果需要传递二进制数据或大量信息，最好将其放入数据库表中并发送记录的键.)

我还引用了一个相关的堆栈溢出问题，我想我已经回避了这个问题:LISTEN/NOTIFY using pg_notify(text, text) in PostgreSQL.

数据库版本为:

PostgreSQL 9.0.3，由Visual C++内部版本号1500编译，32位

我的操作系统是Windows XP Professional 2002 SP3版.

提前谢谢您.

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

这是一个简单的Java1.6SE桌面应用程序，所以我要管理我自己的JDBC连接和所有东西.我正在通过以下方式加载驱动程序

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

我用的是postgresql-9.0-801.jdbc3.jar库(我的类路径上只有一个)和JDK 1.6.0_22.

简单回顾一下，Java代码与来自psql的NOTIFY和触发器以及来自psql的pg_NOTIFY配合良好.