Race condition with notifications

Table foo has a trigger that on insert raises a notice.

Process A listens for that notice, and blocks on the socket (using kqueue).

Process B commits an insert of a row into that table, then commits an insert
of another row immediately.

Process A wakes up and reads the notice, queries table foo for new records,
finds only the first, processes it, checks the socket and blocks.

In other words, in the second insert transaction, pg seems to be coalescing
the second notice after the first notice has been delivered, but before the
second transaction is visible to other processes...

> So it is a bad idea to depend on the number of notifications received.
> Instead, use NOTIFY to wake up applications that need to pay attention to
> something, and use a database object (such as a sequence) to keep track of
> what happened or how many times it happened.

Uhm, yeah? 2 changes committed, 1 notice sent, 1 change visible on receipt
of notice.

Of course I can query table foo for new records twice, and that seems to
work, but it also seems like a bad idea to depend on that kind of timing
detail. (FYI, merely inserting a couple of log statements when process B
wakes up results in both changes being seen by the query.)

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 722-0567 voice