[fpc-pascal] PostgreSQL notifications broken

[Mark Morgan Lloyd]

Michael Van Canneyt wrote:
> On Mon, 28 Sep 2015, Mark Morgan Lloyd wrote:
> 
>> 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?
> 
> pqconnection, start of the unit:
> 
>   { TPQTrans }
> 
>   TPQTrans = Class(TSQLHandle)
>   protected
>     PGConn : PPGConn;
>     FList  : TThreadList;
>     Procedure RegisterCursor(Cursor : TPQCursor);
>     Procedure UnRegisterCursor(Cursor : TPQCursor);
>   Public
>     Constructor Create;
>     Destructor Destroy; override;
>   end;
> 
> 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.

I can see handles changing state in debug output from both 
lazarus-1.0.0+2.6.0 and lazarus-1.0.14+2.6.4 (intermediate versions e.g. 
lazarus-1.0.8+2.6.2 are more of a problem), and can see them behaving 
differently. Knowing that there isn't a third one that I've not yet 
located is a big help.

>> 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.
> 
> 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.
> 
> If you look at the pqeventmonitor unit, you will see that it bypasses 
> completely the usual TSQLConnection mechanisms.

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

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