Re: Problem with async notifications of table updates - Mailing list pgsql-general

Tom Lane wrote:
>"Tyler, Mark" <Mark.Tyler@dsto.defence.gov.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.

The description of NOTIFY in the manual led me to think this -
especially the bit "if the same notification name is signaled multiple
times in quick succession, recipients might get only one notification
event". Re-reading the sentence I can see that I should be interpreting
it as "guaranteed notification of one of a stream of signals". Is there
any chance of loosing a notification if it occurs when I am handling a
previous signal? I guess not but I am not that used to signal behaviour.


My original thought was to use a single NOTIFY channel for notifications
of all changes and then have some secondary table to carry the payload
of the signalled message. If I don't get a notify for every change then
I have to do more work at the app end to try and work out what actually
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.

Which is why I would like to be able to fire the Spread message after
the transaction commits. If I can do that then all is good (I think).
Mind you if the transaction does not commit then that is a relatively
easy case to handle - any recipients of the message will just get a NULL
set when they do a query on the key in the message. Given that I have to
have that path in my subscriber apps anyway it is no overhead.

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

Thanks for the explanation of how NOTIFY and LISTEN work. I could take
the same approach as you suggest but it would again put too much
database-trickery into the subscriber apps for my taste. There is no a
big advantage between doing this and using NOTIFY directly.

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.