在PostgreSQL触发器函数中使用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.

我正在尝试发出一个来自PostgreSQL触发器函数的通知。我可以成功地使用NOTIFY命令，但是我对pg_notify没有任何好感。尽管我在从psql控制台调用pg_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.

我的触发器函数的这个版本可以正常工作。我有一个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. 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.

这个版本的触发器功能不像预期的那样工作。唯一的更改是取消pg_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! When I execute the following command, my Java application receives a notification with a 'fired by CONSOLE' payload:

但是，我真的很困惑，因为相同的pg_notify命令在psql控制台可以正常工作!当我执行以下命令时，我的Java应用程序会收到一个带有“控制台触发”负载的通知:

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:

我尝试使用pg_notify，因为我希望有一个动态有效负载。现在，这是一个有争议的问题。:) Postgres 9.0手册指出这是可能的。“有效载荷”参数状态的通知文档:

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

我还引用了一个相关的堆栈溢出问题，我认为我避开了这个问题:在PostgreSQL中使用pg_notify(文本、文本)侦听/通知。

The database version is:

数据库版本是:

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

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

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

我的操作系统是Windows XP Professional，版本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.

编辑:将我的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();
            }
        }
    }
}

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

这是一个简单的Java 1.6 SE桌面应用程序，因此我正在管理我自己的JDBC连接和一切。我正在给司机装货。

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.

我使用postgresql - 9.0 - 801. - jdbc3。jar库(在我的类路径中只有一个)和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.

简单回顾一下，Java代码可以很好地处理来自psql和触发器的NOTIFY，以及来自psql的pg_notify。

6 个解决方案

#1

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

这可能会导致帮助的延迟，但是其他人可能会使用它。使用选择pg_notify(",");在触发器中，使DB响应

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.

将SELECT更改为按error say执行有助于解决此问题，并按预期交付通知。也许这就是问题所在。

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

我有相同的设置，也有同样的问题。

#2

It might be useful to someone out there. Sometimes you want to pass whole row to "observer" and then it might be a nice idea to serialise whole row into JSON. You can achieve this with help of row_to_json

它可能对外面的人有用。有时，您希望将整行传递给“observer”，然后将整行序列化为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();

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

#4

I don't know if these help with your problem, but some gotcha's I've hit are:

我不知道这些对你的问题是否有帮助，但我遇到的一些问题是:

• You have to commit the transaction with the LISTEN command. I'm not familiar with Java, I don't know if you're in autocommit mode or not.
• 您必须使用LISTEN命令提交事务。我不熟悉Java，我不知道您是否处于自动提交模式。
• Notifies are dispatched when you commit. I suppose for whatever reason, it could be that the transaction that triggered calling pg_notify did not commit or was rolled back?
• 当您提交时，将发出通知。我认为无论出于什么原因，触发调用pg_notify的事务可能没有提交或回滚?
• Maybe the LISTEN connection is connecting to another database than the one where NOTIFY is sent? :)
• 也许侦听连接正在连接另一个数据库，而不是发送通知的数据库?:)

However, none of these can explain why NOTIFY works and pg_notify didn't.

然而，这些都不能解释为什么NOTIFY起作用，而pg_notify没有。

#5

You can use the following code directly into your create trigger function:

您可以将以下代码直接用于创建触发器功能:

EXECUTE 'NOTIFY your_declared_notify';        

OR

或

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

#6

Maybe you'll like following syntax:

也许你会喜欢下面的语法:

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