在PostgreSQL中使用pg_notify触发函数

Question

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

此版本的触发函数按预期工作。我有一个正在侦听“mymessage”的 Java 程序，并且它收到带有“由 NOTIFY 触发”负载的通知。

-- 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 行。 （我没有修改正在侦听的 Java 应用程序。）我希望侦听“mymessage”的应用程序应该收到带有“由 FUNCTION 触发”有效负载的通知。实际行为是，即使在修改相应表 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;

但是，我真的感到困惑，因为相同的 pg_notify 命令按照 psql 控制台的预期工作！当我执行以下命令时，我的 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();

我尝试使用 pg_notify 因为我想要一个动态有效负载。现在，这是一个有争议的问题。 :) Postgres 9.0 手册表明这应该是可能的。 “payload”参数状态的 NOTIFY 文档：

（如果需要通信二进制数据或大量信息，最好将其放入数据库表中并发送记录的密钥。）

我还引用了相关的Stack Overflow问题，我想我已经回避了这个问题：在 PostgreSQL 中使用 pg_notify(text, text) 进行监听/通知< /a>.

数据库版本为：

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

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

提前致谢。

编辑：在下面添加了我的 Java 侦听器代码。它基于 PostgreSQL 文档中的示例：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 连接和一切。 加载驱动程序

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

我通过使用 postgresql-9.0-801.jdbc3.jar 库（我的类路径上只有一个）和 JDK 1.6.0_22

。回顾一下上面的内容，Java 代码可以与 psql 中的 NOTIFY 和触发器以及 psql 中的 pg_notify 一起正常工作。

Answer 1

这可能为时已晚，但也许其他人能够使用它。
使用 SELECT pg_notify('', '');触发器中的内容会导致数据库做出响应，

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.

将 SELECT 更改为 PERFORM，因为错误提示有助于解决此问题，并且通知会按预期传递。也许这可能就是问题所在。

我有同样的设置，也遇到了同样的问题。

Answer 2

它可能对那里的人有用。有时您想将整行传递给“观察者”，然后将整行序列化为 JSON 可能是一个好主意。您可以借助 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();

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

Answer 4

您可以直接在创建触发器函数中使用以下代码：

EXECUTE 'NOTIFY your_declared_notify';        

或者

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

Answer 5

我不知道这些是否有助于解决您的问题，但我遇到的一些问题是：

• 您必须使用 LISTEN 命令提交事务。我对Java不熟悉，我不知道你是否处于自动提交模式。
• 当您提交时会发送通知。我想无论出于何种原因，触发调用 pg_notify 的事务可能未提交或已回滚？
• 也许 LISTEN 连接正在连接到另一个数据库，而不是发送 NOTIFY 的数据库？ :)

然而，这些都不能解释为什么 NOTIFY 有效而 pg_notify 无效。

Answer 6

也许您会喜欢以下语法：

RAISE notice 'hstore %, patrm %, dt %, v% ', new_g, _param_id, _dt, new.v ;