在 PostgreSQL 触发器函数中使用 pg_notify [英] Using pg_notify in PostgreSQL trigger function

问题描述

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

我的触发器函数的这个版本按预期工作.我有一个 Java 程序正在侦听mymessage"，它收到一个带有由 NOTIFY 触发"负载的通知.

-- 函数:conversation_notify()-- 删除功能对话通知()；CREATE OR REPLACE FUNCTION session_notify()返回触发 AS$BODY$开始--SELECT pg_notify('mymessage', 'fired by FUNCTION');NOTIFY mymessage, '由 NOTIFY 触发';返回空；结尾;$BODY$语言 plpgsql 易失性成本 100；ALTER FUNCTION session_notify() OWNER TO postgres;

我的触发器函数的这个版本没有按预期工作.唯一的变化是取消注释 pg_notify 行并注释掉下面的 NOTIFY 行.(我没有修改正在侦听的 Java 应用程序.)我希望我的应用程序侦听mymessage"应该收到带有由 FUNCTION 触发"有效负载的通知.实际行为是什么都没有收到，即使是在相应表修改后 30+ 秒.

-- 函数:conversation_notify()-- 删除功能对话通知()；CREATE OR REPLACE FUNCTION session_notify()返回触发 AS$BODY$开始SELECT pg_notify('mymessage', '由功能触发');--NOTIFY mymessage, '由 NOTIFY 触发';返回空；结尾;$BODY$语言 plpgsql 易失性成本 100；ALTER FUNCTION session_notify() OWNER TO postgres;

但是，我真的很困惑，因为相同的 pg_notify 命令在 psql 控制台中按预期工作！当我执行以下命令时，我的 Java 应用程序收到一个带有由控制台触发"负载的通知:

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

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

-- 触发器:conversation_notify on ofconversation-- DROP TRIGGER 对话_通知开启对话；CREATE TRIGGER session_notify插入或更新后通话开始每行执行程序会话通知()；

我正在尝试使用 pg_notify，因为我想要一个动态负载.现在，这是一个有争议的问题.:) Postgres 9.0 手册表明这应该是可能的.'payload' 参数状态的 NOTIFY 文档:><块引用>

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

我还参考了一个相关的堆栈溢出问题，我想我已经避开了这个问题:在 PostgreSQL 中使用 pg_notify(text, text) 收听/通知.

数据库版本为:

<块引用>

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;导入 java.sql.ResultSet;导入 java.sql.SQLException;导入 java.sql.Statement;导入 org.postgresql.PGConnection;导入 org.postgresql.PGNotification;公共类 ConversationListener 扩展了线程{私人连接连接；私人 PGConnection pgConn;public ConversationListener(Connection conn) 抛出 SQLException{this.conn = conn;this.pgConn = (PGConnection) conn;语句listenStatement = conn.createStatement();listenStatement.execute("听我的消息");listenStatement.close();}@覆盖公共无效运行(){而(真){尝试{//发出一个虚拟查询来联系后端//并接收任何待处理的通知.语句 selectStatement = conn.createStatement();结果集 rs = selectStatement.executeQuery("SELECT 1");rs.close();selectStatement.close();PGNotification 通知[] = pgConn.getNotifications();如果(通知！= null){for (PGNotification pgNotification : 通知){System.out.println("收到通知:" + pgNotification.getName() +" 与有效载荷:" + pgNotification.getParameter());}}//稍等片刻再检查线程睡眠(500)；}捕获(SQLException sqlException){sqlException.printStackTrace();}catch (InterruptedException 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 一起工作得很好.

解决方案

这可能为时已晚，但也许其他人可以使用它.使用 SELECT pg_notify('', '');在触发器中导致数据库响应

错误:查询没有结果数据的目的地SQL 状态:42601提示:如果您想丢弃 SELECT 的结果，请改用 PERFORM.

根据错误提示将 SELECT 更改为 PERFORM 有助于解决此问题，并且通知按预期交付.也许这就是问题所在.

我有同样的设置，也有同样的问题.

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.

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;

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;

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

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.)

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.

The database version is:

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

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

Thanks in advance.

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

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

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

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

解决方案

This might be to late to help but perhaps someone else will be able to use it. Using SELECT pg_notify('', ''); in the trigger causes the DB to respond with

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.

Changing the SELECT to PERFORM as the error say helps to resolve this issue and the notification gets delivered as expected. Perhaps this could have been the problem.

I have the same setup, and had the same problem.

这篇关于在 PostgreSQL 触发器函数中使用 pg_notify的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！