Re: Problem with async notifications of table updates

Rodrigo Gonzalez wrote:
> I am almost sure you've defined a BEFORE trigger and
> you need and AFTER trigger, so it's fired after commiting.

No - I am definitely using an AFTER trigger. Following is a simplified
version of what I am trying to do.

/* messages - log messages */
CREATE TABLE messages
                 (id         SERIAL PRIMARY KEY,
            time           TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
            severity_level    INTEGER NOT NULL,
            severity    TEXT NOT NULL,    /*
ENUM('Info','Warning','Critical') */
            facility    CHAR(10) NOT NULL,
            msg        TEXT NOT NULL);

CREATE OR REPLACE FUNCTION message_alert() RETURNS TRIGGER AS
$message_alert$
    BEGIN
        PERFORM send_mesg('notify_channel', 'DB:Log:' || NEW.id
|| ':');
        RETURN NULL;
    END;
$message_alert$ LANGUAGE plpgsql;

CREATE TRIGGER message_alert AFTER INSERT ON messages
FOR EACH ROW
EXECUTE PROCEDURE message_alert();

I have a Python program which is waiting on the message being sent via
send_mesg(). The message is received correctly but if I do an immediate
"SELECT msg FROM messages WHERE id=<the message id that came via the
send_msg() call>;" then it returns a NULL set. If I put a small sleep
between receiving the message and doing the select then I get the data.

What I want to do is to guarantee that the row is available for
selection prior to sending the message.

Mark



IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of
section70 of the CRIMES ACT 1914.  If you have received this email in error, you are requested to contact the sender
anddelete the email.