Re: Problem with async notifications of table updates

"Tyler, Mark" <Mark(dot)Tyler(at)dsto(dot)defence(dot)gov(dot)au> writes:
> 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.

What makes you think NOTIFY doesn't guarantee delivery? If the
transaction commits then the notify update has happened.

Perhaps more to the point, have you reflected on the fact that your
technique has the opposite problem? Once you've given the message
to Spread, it'll deliver it whether your transaction subsequently
commits or not.

If you're really intent on re-inventing NOTIFY, you could use the
same synchronization trick it does: take out a lock on some otherwise
unused table just before sending the message, and have recipients lock
the same table on receipt of the message, before they go looking for
any effects in the database. The NOTIFY-side lock is held past commit
of its transaction, so once recipients can lock the table they must be
able to see the results of the NOTIFY's transaction. This is not
insanely great from a concurrency standpoint of course, but as long as
you keep the lock hold durations short it's workable.

regards, tom lane