Thread: Trouble with PQnotifies()

Dear gurus,

CREATE OR REPLACE RULE r1i AS ON INSERT TO t1 DO NOTIFY NotifyMe;
CREATE OR REPLACE RULE r1u AS ON UPDATE TO t1 DO NOTIFY NotifyMe;
CREATE OR REPLACE RULE r1d AS ON DELETE TO t1 DO NOTIFY NotifyMe;

The main thread in my Windows application launches several worker threads. Several worker threads connect to PostgreSQL
byvia libpq.dll and execute SQL requests from clients. Several other worker threads, as illustrated below, also connect
toPostgreSQL via libpq and listen to notifications. 

The SQL execution thread calls PG front end functions provided in libpq.dll. On the other hand, the listening thread
callsPG front end functions provided in libpq.lib which are all statically linked to this application. Using two copies
ofPG front end functions codes makes the size of my applciation bloated but I presume there is no other side effect
exceptthe program size. 

The following listening worker thread behaves as expected if I insert/delete rows into/from table "t1" in psql prompt.

My trouble is when the SQL execution worker thread inserts/ deletes rows into/from table "t1", the listening worker
threadthen goes crazy: PQnotifies() always returns NULL which pushes the listening thread to grab all CPU power because
select()returns immediately in every iteration. The weird part is that select() says that there is something available
butPQnotifies() returns NULL. 

---------
PGconn *c=/* Take one connection from connection pool */;
PGresult *result=PQexec(c,"LISTEN NotifyMe");
PQclear(result);
fd_set InputMask;
int sock=PQsocket(c);
struct timeval TimeOut={1,200000};
int SelectResult;
PGnotify *notify;
int terminated=0;
while(!terminated){
    FD_ZERO(&InputMask);
    FD_SET((unsigned int)sock,&InputMask);
    SelectResult=select(sock+1,&InputMask,NULL,NULL,&TimeOut);
    if(SelectResult == SOCKET_ERROR){
        puts("select() failed:");
        break;
    }
    if(SelectResult == 0)
        continue;
    if(!FD_ISSET(sock,&InputMask))
        continue;
    PQconsumeInput(c);
    while((notify=PQnotifies(c)) != NULL){ //here: unstable!
        if(stricmp(notify->relname,"NotifyMe") == 0)
            puts("Got notification");
        PQfreemem(notify);
    }
}
PQfinish(c);
---------

What can be the possible cause of the abnormality?

Best Regards,

CN

> The following listening worker thread behaves as expected if I insert/delete rows into/from table "t1" in psql
prompt.
>
> My trouble is when the SQL execution worker thread inserts/ deletes rows into/from table "t1", the listening worker
threadthen goes crazy: PQnotifies() always returns NULL which pushes the listening thread to grab all CPU power because
select()returns immediately in every iteration. The weird part is that select() says that there is something available
butPQnotifies() returns NULL. 
>
> ---------
> PGconn *c=/* Take one connection from connection pool */;
> PGresult *result=PQexec(c,"LISTEN NotifyMe");
> PQclear(result);
> fd_set InputMask;
> int sock=PQsocket(c);
> struct timeval TimeOut={1,200000};
> int SelectResult;
> PGnotify *notify;
> int terminated=0;
> while(!terminated){
>     FD_ZERO(&InputMask);
>     FD_SET((unsigned int)sock,&InputMask);
>     SelectResult=select(sock+1,&InputMask,NULL,NULL,&TimeOut);
>     if(SelectResult == SOCKET_ERROR){
>         puts("select() failed:");
>         break;
>     }
>     if(SelectResult == 0)
>         continue;
>     if(!FD_ISSET(sock,&InputMask))
>         continue;
>     PQconsumeInput(c);
>     while((notify=PQnotifies(c)) != NULL){ //here: unstable!
>         if(stricmp(notify->relname,"NotifyMe") == 0)
>             puts("Got notification");
>         PQfreemem(notify);
>     }
> }
> PQfinish(c);

Please ignore this question!

My connection pool implementation seems to have flaw. Somehow and somewhere the connection acquired by a thread is
robbedby other threads. The PGconn  sending "LISTEN NotifyMe" becomes different from the PGconn passed to PQsocket(),
PQconsumeInput(),and/or PQnotifies(). 

Please also pardon me for asking inappropriate questions like this one. As far as I can recall, every issue I
encounteredbefore always finally proved that PostgreSQL is flawless. 

Best Regards,
CN

seiliki@so-net.net.tw wrote:
>> The following listening worker thread behaves as expected if I insert/delete rows into/from table "t1" in psql
prompt.
>>
>> My trouble is when the SQL execution worker thread inserts/ deletes rows into/from table "t1", the listening worker
threadthen goes crazy: PQnotifies() always returns NULL which pushes the listening thread to grab all CPU power because
select()returns immediately in every iteration. The weird part is that select() says that there is something available
butPQnotifies() returns NULL. 
..
> Please ignore this question!
>
> My connection pool implementation seems to have flaw. Somehow and somewhere the connection acquired by a thread is
robbedby other threads. The PGconn  sending "LISTEN NotifyMe" becomes different from the PGconn passed to PQsocket(),
PQconsumeInput(),and/or PQnotifies(). 

I was looking at it carefully, and was about to ask about the
connection- in particular whether it was shared across threads etc. Glad
you've found the issue, I've been caught by something very similar using
list/notify on Lazarus/FPC where you can end up with several handles
only one of which is reliable.

> Please also pardon me for asking inappropriate questions like this one. As far as I can recall, every issue I
encounteredbefore always finally proved that PostgreSQL is flawless. 

But at least it demonstrates that somebody's using that facility.

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

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