[fpc-pascal] PostgreSQL notifications broken

[Michael Van Canneyt]

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;


>
> 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.

Michael.