Trouble with PQnotifies()

From: seiliki(at)so-net(dot)net(dot)tw
To: pgsql-general(at)postgresql(dot)org
Subject: Trouble with PQnotifies()
Date: 2012-12-13 09:31:04
Message-ID: 20121213093108.E4513F481AC@m5.so-net.net.tw
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 by via libpq.dll and execute SQL requests from clients. Several other worker threads, as illustrated below, also connect to PostgreSQL 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 calls PG front end functions provided in libpq.lib which are all statically linked to this application. Using two copies of PG front end functions codes makes the size of my applciation bloated but I presume there is no other side effect except the 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 thread then 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 but PQnotifies() 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Haifeng Liu 2012-12-13 10:44:56 Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)
Previous Message Martin French 2012-12-13 07:34:12 Re: C Function Memory Management