[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)));
PQclear(res)
SQLTransaction1.CommitRetaining
and
DatabaseTableForm.SQLTransaction1.CommitRetaining;
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