[fpc-pascal] PostgreSQL notifications broken

[Mark Morgan Lloyd]

Michael Van Canneyt wrote:
> On Fri, 11 Sep 2015, Mark Morgan Lloyd wrote:
> 
>> 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;
> 
> If  DbTF.PQConnection1 is of type TPQConnection then I think this is 
> your problem. The low-level handle has been moved to the transaction. 
> DbTF.PQConnection1.Handle is then a stub, of no value.

Any chance of a hint where to find the new long-life handle, i.e. the 
one that corresponds to the connect action with username, password etc. 
properties?

The change was between 2.6.2 and 2.6.4, I've been looking at sqldb.pp 
and related files but so far haven't tracked it down.

Discussing this sort of thing elsewhere, at least PostgreSQL, 
Firebird/Interbase and Oracle provide comparable notification/event 
features with varying degrees of functionality. If encapsulating this 
sort of thing in libraries or components, it would be highly desirable 
to be able to rely on the availability of a persistent handle.

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

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