database 在 PostgreSQL 触发器函数中使用 pg_notify
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5412474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using pg_notify in PostgreSQL trigger function
提问by Tom
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.
这个版本的触发器功能按预期工作。我有一个正在侦听“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;
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 行并注释掉下面的 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;
However, I'm reallyconfused, 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 docsfor the 'payload' parameter state:
我正在尝试使用 pg_notify，因为我想要一个动态有效负载。现在，这是一个有争议的问题。:) Postgres 9.0 手册表明这应该是可能的。'payload' 参数状态的 NOTIFY 文档：
(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.
我还参考了一个相关的 Stack Overflow 问题，我想我已经避开了这个问题：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
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 一起工作得很好。
回答by CJCombrink
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
这可能为时已晚，但也许其他人将能够使用它。使用 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.
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 更改为 PERFORM 有助于解决此问题，并且通知按预期传递。也许这可能是问题所在。
I have the same setup, and had the same problem.
我有相同的设置，并且遇到了同样的问题。
回答by Oto Brglez
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
它可能对那里的人有用。有时您想将整行传递给“观察者”，然后将整行序列化为 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();
回答by Invincible
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();
回答by intgr
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
LISTENcommand. I'm not familiar with Java, I don't know if you're in autocommit mode or not. - Notifies are dispatched when you commit. I suppose for whatever reason, it could be that the transaction that triggered calling
pg_notifydid not commit or was rolled back? - Maybe the LISTEN connection is connecting to another database than the one where NOTIFY is sent? :)
- 您必须使用
LISTEN命令提交事务。我不熟悉 Java，不知道您是否处于自动提交模式。 - 提交时发送通知。我想无论出于何种原因，可能是触发调用的事务
pg_notify没有提交或回滚？ - 也许 LISTEN 连接正在连接到另一个数据库而不是发送 NOTIFY 的数据库？:)
However, none of these can explain why NOTIFY works and pg_notify didn't.
然而，这些都不能解释为什么 NOTIFY 有效而 pg_notify 没有。
回答by imamalis
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));
回答by adel
Maybe you'll like following syntax:
也许你会喜欢下面的语法：
RAISE notice 'hstore %, patrm %, dt %, v% ', new_g, _param_id, _dt, new.v ;
