Re: Problem with async notifications of table updates

Tom Lane wrote:
>"Tyler, Mark" <Mark(dot)Tyler(at)dsto(dot)defence(dot)gov(dot)au> writes:
>> What I want to do is to guarantee that the row is available for
>> selection prior to sending the message.
>
> You cannot do that with an AFTER trigger, because whatever it
> does necessarily happens before your transaction commits.

I somehow suspected that this was the answer. It would be nice to have
some sort of FINALLY style of clause for the trigger which was able to
be initiated after the transaction was committed. Of course there would
be very large restrictions on what sort of things could be done in such
a clause.

Clearly NOTIFY itself works around this very problem. I have not looked
at the code but I suspect the NOTIFY command sets a flag that tells the
server to fire the notification as soon as the transaction commits -
thus the command can be inside the trigger context but have an effect
after the trigger completes.

> I suggest rethinking your dislike of NOTIFY.

I have thought very hard about using NOTIFY for this but it has two
large problems (from my point of view). The first is that it forces me
to put far more smarts and state into the subscriber applications. This
is because I cannot pass any information with the NOTIFY apart from the
fact that "something happened". Due to this restriction my subscriber
apps would have to go and look up some secondary table to get sufficient
information to construct the real query. That is just plain ugly in my
view.

Secondly, the lack of any delivery guarantee means my subscriber
applications may miss event notifications. This is a very bad thing for
my particular application.

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.