[fpc-pascal] PostgreSQL notifications broken

[Mark Morgan Lloyd]

PostgreSQL has a useful feature where application programs can send 
notifications to each other, this tends to be much "cheaper" than 
periodically polling a table for changes.

I've had this working on various CPUs and OSes in a number of programs 
since at least 2.2.4, but it appears to have been broken at some point 
between 2.6.0 and 2.6.4 with problems persisting through to 3.0.0-rc1 
and trunk. The specific fragment of code that's failing looks like this:

   result := badPoll;
   if PQStatus(DbTF.PQConnection1.Handle) <> CONNECTION_OK then
     exit;
   pqConsumeResult := PQconsumeInput(DbTF.PQConnection1.Handle);
   if PQStatus(DbTF.PQConnection1.Handle) <> CONNECTION_OK then
     exit;
   result := okPollNo;
   notification := PQnotifies(DbTF.PQConnection1.Handle);
   if notification <> nil then

 From 2.6.4 onwards PQnotifies() always returns nil, despite other 
functionality behaving as expected.

I'm working on trying to pin this down a bit better, but would 
appreciate any pointers from anybody who remembers working in this area.

The thing that usually causes problems for this feature is if the 
database connection is closed and reopened, so that the server no longer 
knows that the app wants to receive notifications. I haven't yet 
researched what relevant info Postgres exposes.

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]