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 section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email.