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;
   result := okPollNo;
   notification := PQnotifies(DbTF.PQConnection1.Handle);
   if notification  nil then

From 2.6.4 onwards PQnotifies() always returns nil, despite other functionality behaving as expected.

I'm working on trying to pin this down a bit better, but would appreciate any pointers from anybody who remembers working in this area.

The thing that usually causes problems for this feature is if the database connection is closed and reopened, so that the server no longer knows that the app wants to receive notifications. I haven't yet researched what relevant info Postgres exposes.

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.

Michael.

Thanks, I'll investigate (although it's curious or at least unfortunate that PQStatus() etc. is OK).

For the purpose of backward compatibility, what was the preferred way to get the handle before 2.6.4? Was it in TPQConnection or has there always been one in the transaction object?

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

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

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.

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.

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.