database - pg_notify - listen postgres

Usando pg_notify en la función de disparo de PostgreSQL

Question

Estoy intentando emitir una notificación desde una función de activación de PostgreSQL. Puedo usar con éxito el comando NOTIFY, pero no estoy teniendo suerte con pg_notify. Aunque recibo una notificación cuando invoco la función pg_notify desde la consola psql, nunca recibo una notificación cuando invoco la misma desde mi función de activación.

Esta versión de mi función de activación funciona como se esperaba. Tengo un programa Java que está ESCUCHANDO "mymessage" y recibe una notificación con una carga útil de "activada por 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;

Esta versión de mi función de activación NO funciona como se esperaba. Los únicos cambios son sin comentarios de la línea pg_notify y comentando la línea NOTIFY a continuación. (No modifiqué la aplicación Java que está escuchando.) Espero que mi aplicación ESCUCHANDO "mymessage" reciba una notificación con una carga útil "activada por FUNCTION ''. El comportamiento real es que no se recibe nada, incluso más de 30 segundos después de que se modifique la tabla correspondiente.

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

Sin embargo, estoy realmente confundido, porque el mismo comando pg_notify funciona como se esperaba desde la consola psql. Cuando ejecuto el siguiente comando, mi aplicación Java recibe una notificación con una carga útil ''activada por CONSOLE'':

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

Para completar, aquí está mi definición de disparador:

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

Estoy tratando de usar pg_notify porque me gustaría tener una carga útil dinámica. En este momento, eso es un punto discutible. :) El manual de Postgres 9.0 indica que esto debería ser posible. Los documentos NOTIFY para el estado del parámetro ''carga útil'':

(Si es necesario comunicar datos binarios o grandes cantidades de información, es mejor colocarlos en una tabla de la base de datos y enviar la clave del registro).

También hice referencia a una pregunta relacionada sobre el desbordamiento de pila, y creo que he esquivado este problema: ESCUCHAR / NOTIFICAR usando pg_notify (texto, texto) en PostgreSQL .

La versión de la base de datos es:

PostgreSQL 9.0.3, compilado por Visual C ++ build 1500, de 32 bits

Mi sistema operativo es Windows XP Professional, versión 2002, SP3.

Gracias por adelantado.

EDITAR: Agregué mi código de escucha Java a continuación. Se basa en este ejemplo de la documentación de PostgreSQL: .

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

Esta es una sencilla aplicación de escritorio Java 1.6 SE, así que estoy administrando mi propia conexión JDBC y todo. Estoy cargando el driver via

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

Estoy usando la biblioteca postgresql-9.0-801.jdbc3.jar (solo una en mi classpath) y JDK 1.6.0_22.

Solo para resumir desde arriba, el código Java funciona bien con NOTIFY de psql y el disparador, y con pg_notify de psql.

Answer 1

Esto puede ser demasiado tarde para ayudar, pero tal vez alguien más pueda usarlo. Usando SELECT pg_notify ('''', ''''); en el disparador hace que el DB responda con

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.

Cambiar el SELECCIONAR para REALIZAR, como dice el error, ayuda a resolver este problema y la notificación se entrega como se esperaba. Quizás este podría haber sido el problema.

Tengo la misma configuración, y tuve el mismo problema.

Answer 2

No sé si esto ayuda con tu problema, pero algunos de los que he recibido son:

• Tienes que confirmar la transacción con el comando LISTEN . No estoy familiarizado con Java, no sé si está en modo de confirmación automática o no.
• Las notificaciones se envían cuando te comprometes. Supongo que por alguna razón, podría ser que la transacción que desencadenó la llamada a pg_notify no se pg_notify o se retrotrajo.
• ¿Es posible que la conexión LISTEN se conecte a otra base de datos que no sea la que se envía NOTIFY? :)

Sin embargo, ninguno de estos puede explicar por qué NOTIFY funciona y pg_notify no lo hizo.

Answer 3

Podría ser útil para alguien por ahí. A veces desea pasar una fila completa a "observador" y luego podría ser una buena idea serializar una fila completa en JSON. Puedes lograr esto con la ayuda de 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 4

Puede usar el siguiente código directamente en su función de creación de activador:

EXECUTE ''NOTIFY your_declared_notify'';        

O

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

Answer 5

Tal vez te guste seguir la sintaxis:

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

Answer 6

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