[fpc-pascal] PostgreSQL notifications broken

Mark Morgan Lloyd markMLl.fpc-pascal at telemetry.co.uk
Wed Oct 7 13:01:54 CEST 2015

Mark Morgan Lloyd wrote:
> Michael Van Canneyt wrote:
>> On Mon, 28 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.

>> PGConn is what you're looking for.
>> TPQTrans is referenced in :
>>   TPQCursor = Class(TSQLCursor)
>>   protected
>>     Statement    : string;
>>     StmtName     : string;
>>     tr           : TPQTrans;
> Thanks, I'll take another look but one of the handles I'm tracking is 
> that PGConn field.

It turns out that the "best" handle to use- at least in the case of 
connections to PostgreSQL- is still the one associated with the 
connection object. The issue is not so much that the handles have been 
shuffled round, but that some of the database stuff was changed to 
reduce the number of spurious transactions that were issued.

Adding a couple of explicit CommitRetaining calls seems to improve 
things a lot:

   res := PQexec(postgresHandle(), PChar('LISTEN ' + 
sanitizeName(EditTableName.Text, true)));


   pqConsumeResult := PQconsumeInput(postgresHandle());
   notification := PQnotifies(postgresHandle());

where postgresHandle() gets the call from the connection object by 
default. To be extended to other programs and to also using Firebird as 
the backend.

>>> 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.
>> The above should be sufficient ?
>> But the mechanisms are different for each DB, so a unified mechanism 
>> is not something we are considering, to my knowledge.

But there does appear to be some common level of functionality where a 
database can either initiate or relay a notification. The amount of 
extra payload varies, e.g. Postgres (but not Firebird) includes the 
backend PID while I'm told that Oracle can filter notifications through 
a query.

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

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

More information about the fpc-pascal mailing list