wake-up-neo.net

Verwendung von pg_notify in der PostgreSQL-Triggerfunktion

Ich versuche, eine Benachrichtigung von einer PostgreSQL-Triggerfunktion auszulösen. Ich kann den NOTIFY-Befehl erfolgreich verwenden, habe aber mit pg_notify kein Glück. Obwohl ich eine Benachrichtigung erhalte, wenn ich die Funktion pg_notify von der psql-Konsole aus aufrufe, erhalte ich keine Benachrichtigung, wenn ich dieselbe von meiner Triggerfunktion aus aufrufe.

Diese Version meiner Triggerfunktion funktioniert wie erwartet. Ich habe ein Java-Programm, das 'mymessage' abhört, und es erhält eine Benachrichtigung mit einer von NOTIFY ausgelösten Nutzlast.

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

Diese Version meiner Triggerfunktion funktioniert NICHT wie erwartet. Die einzigen Änderungen sind das Auskommentieren der pg_notify-Zeile und das Auskommentieren der NOTIFY-Zeile. (Ich habe die Java-Anwendung, die LISTENING ist, nicht geändert.) Ich gehe davon aus, dass meine Anwendung LISTENING to 'mymessage' eine Benachrichtigung mit einer Payload "Fired by FUNCTION" erhalten sollte. Das eigentliche Verhalten ist, dass auch 30 Sekunden nach dem Ändern der entsprechenden Tabelle nichts empfangen wird.

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

Allerdings bin ich wirklich verwirrt, weil derselbe Befehl pg_notify wie von der psql-Konsole erwartet funktioniert! Wenn ich den folgenden Befehl ausführe, erhält meine Java-Anwendung eine Benachrichtigung mit einer von CONSOLE ausgelösten Nutzlast:

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

Der Vollständigkeit halber ist hier meine Auslöserdefinition:

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

Ich versuche, pg_notify zu verwenden, weil ich eine dynamische Nutzlast haben möchte. Im Moment ist das ein strittiger Punkt. :) Das Postgres 9.0-Handbuch gibt an, dass dies möglich sein sollte. Die NOTIFY-Dokumente für den Parameter "Payload" -Status:

(Wenn binäre Daten oder große Mengen an Informationen übermittelt werden müssen, ist es am besten, diese in eine Datenbanktabelle zu stellen und den Schlüssel des Datensatzes zu senden.)

Ich habe auch auf eine verwandte Stapelüberlauf-Frage verwiesen, und ich glaube, ich bin diesem Problem ausgewichen: LISTEN/NOTIFY using pg_notify (text, text) in PostgreSQL .

Die Datenbankversion ist:

PostgreSQL 9.0.3, kompiliert von Visual C++ Build 1500, 32-Bit

Mein Betriebssystem ist Windows XP Professional, Version 2002, SP3.

Danke im Voraus.

BEARBEITEN: Fügte meinen Java-Listener-Code unten hinzu. Es basiert auf diesem Beispiel aus den PostgreSQL-Dokumenten: 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();
            }
        }
    }
}

Dies ist eine einfache Java 1.6 SE-Desktopanwendung, daher verwalte ich meine eigene JDBC-Verbindung und alles. Ich lade den Treiber über

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

Ich verwende die Bibliothek postgresql-9.0-801.jdbc3.jar (nur eine in meinem Klassenpfad) und JDK 1.6.0_22.

Um es von oben zusammenzufassen: Der Java-Code funktioniert einwandfrei mit NOTIFY von psql und dem Trigger sowie mit pg_notify von psql.

16
Tom

Dies könnte zu spät sein, um zu helfen, aber vielleicht kann es jemand anderes verwenden. Verwenden von SELECT pg_notify ('', ''); im Trigger bewirkt, dass der DB mit antwortet

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.

Wenn Sie SELECT auf PERFORM ändern, während der Fehler besagt, kann das Problem behoben werden, und die Benachrichtigung wird wie erwartet zugestellt. Vielleicht könnte dies das Problem gewesen sein.

Ich habe das gleiche Setup und hatte das gleiche Problem.

27
The Badger

Es könnte für jemanden da draußen nützlich sein. Manchmal möchten Sie eine ganze Zeile an "observer" übergeben, und dann ist es möglicherweise eine gute Idee, eine ganze Zeile in JSON zu serialisieren. Sie erreichen dies mit Hilfe von 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();
14
Oto Brglez
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();
6
Invincible

Ich weiß nicht, ob diese bei Ihrem Problem helfen, aber einige Fallstricke, die ich getroffen habe, sind:

  • Sie müssen die Transaktion mit dem Befehl LISTEN festschreiben. Ich kenne Java nicht, ich weiß nicht, ob Sie sich im Autocommit-Modus befinden oder nicht.
  • Benachrichtigungen werden versendet, wenn Sie ein Commit durchführen. Ich nehme aus irgendeinem Grund an, dass die Transaktion, die den Aufruf von pg_notify ausgelöst hat, nicht festgeschrieben oder zurückgesetzt wurde.
  • Möglicherweise verbindet sich die LISTEN-Verbindung mit einer anderen Datenbank als der, an die NOTIFY gesendet wird? :)

Keines davon kann jedoch erklären, warum NOTIFY funktioniert und pg_notify nicht.

2
intgr

Sie können den folgenden Code direkt in Ihre Funktion zum Erstellen von Triggern verwenden:

EXECUTE 'NOTIFY your_declared_notify';        

OR

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

Vielleicht gefällt Ihnen folgende Syntax:

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