Обсуждение: 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@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
Scott Ribe <scott_ribe@killerbytes.com> writes: > Uhm, yeah? 2 changes committed, 1 notice sent, 1 change visible on receipt > of notice. Possibly you got bit by the recently-fixed LISTEN/NOTIFY race condition? http://archives.postgresql.org/pgsql-bugs/2008-03/msg00094.php IIRC this could only be a problem if your listener had only just started listening, though. regards, tom lane
> Possibly you got bit by the recently-fixed LISTEN/NOTIFY race condition? > http://archives.postgresql.org/pgsql-bugs/2008-03/msg00094.php > IIRC this could only be a problem if your listener had only just started > listening, though. - Geez, I realize I forget to state the version I'm using: 3.1. - I had been doing all my testing from a cold start-up. But when I go back now and keep testing over & over, I keep seeing the problem over & over. But reading the report you linked to, it *does* seem to me like it describes my problem, and that it could happen any time, not just soon after starting a listen. If this gets fixed in a near-term release I'm good. Right now, I can kludge around it with a double-query of the modified table--because right now the usage patterns are such that I don't think this will ever skip an update. (Longer-running updates, or a heavier load, and I might have a bigger problem.) Four years of using PostegreSQL, and finally there's a bug that actually affects the correct operation of my software. Not too shabby ;-) -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
Scott Ribe <scott_ribe@killerbytes.com> writes: >> Possibly you got bit by the recently-fixed LISTEN/NOTIFY race condition? >> http://archives.postgresql.org/pgsql-bugs/2008-03/msg00094.php >> IIRC this could only be a problem if your listener had only just started >> listening, though. > - Geez, I realize I forget to state the version I'm using: 3.1. You mean 8.3.1? That does contain the aforementioned patch, so whatever you're seeing is something new. Please see if you can put together a self-contained test case. regards, tom lane