-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org]on Behalf Of Dmitri Girski
Sent: Monday, 23 March 2009 10:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] LISTEN/NOTIFY problem
Hi everybody,
I've got a weird problem with LISTEN/NOTIFY.
My C++ app subscribes for the notifications, just like in libpq examples:
http://www.postgresql.org/docs/8.3/static/libpq-example.html
The only difference, that I am setting the timeout on select just to check if
application wants to exit.
//open session
//subscribe
while(!exit)
{
sock = PGsocket(conn)
res = select(sock);
if (res)
//check if it timeout
//or if there was a notification
}
The problem that I am facing is that after some time notifications stop coming.
select() returns on timeout and nothing else.
Just as a test a included UNLISTEN/LISTEN sequence into the loop, so after
timeout or event application re-subscribes. And this helps.
The question is, what I am doing wrong with the code in the first place?
Any help is appreciated.
Cheers,
Dmitri.
--
@Gmail
Cannot tell what is wrong with you piece of code.
But below is a routine I use for notifications.
No problems with it so far.
Hope it helps.
Allan
/*
** Structure to hold the connection data
*/
typedef struct _condetails
{
char *pghost;
char *pgport;
char *pgoptions;
char *pgtty;
char *dbname;
char *pguser;
char *pgpswd;
PGconn *conn;
int bpid;
} CONDETAILS;
/*
** Register for a database notification
*/
int reg_notification( CONDETAILS *cd, const char *notif )
{
char *sqlnotify, sql[128];
PGconn* conn;
PGresult* res;
conn = cd-conn;
sqlnotify = listen %s;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( reg_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
/*
** Register
*/
sprintf( sql, sqlnotify, notif );
res = PQexec( conn, sql );
if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
{
sysErr( reg_notification(). listen command failed. %s,
PQerrorMessage( conn ) );
PQclear( res );
return -1;
}
PQclear( res );
return 0;
}
/*
** Wait for a database notification or time out
** This is an async method.
**
** Return only the first notification not generated by me
** and flush the rest.
**
** Probly should do something smarter.
*/
int wait_db_notification( CONDETAILS *cd, char *notif, const long sec, const
long usec )
{
int sock, ret;
fd_set input_mask;
PGconn* conn;
PGnotify *notify;
struct timeval timeout;
conn = cd-conn;
/*
** check to see that the backend connection was successfully made
*/
if ( PQstatus( conn ) == CONNECTION_BAD )
{
sysErr( wait_db_notification(). %s, PQerrorMessage( conn ) );
PQfinish( conn );
condetails( cd );
return -1;
}
sock = PQsocket( conn );
if ( sock 0 )
{
sysErr( wait_db_notification(). Could not get socket
descriptor. %s, PQerrorMessage( conn ) );
return -1;
}
FD_ZERO( input_mask );
FD_SET( sock, input_mask );
timeout.tv_sec = sec;
timeout.tv_usec = usec;
ret = select( sock + 1, input_mask, NULL, NULL, timeout );
if ( ret 0 )
{
sysErr( %d: %s, __LINE__, wait_db_notification(). Select on
db socket failed );
if ( errno != EINTR )
exit( 1 );
/*
** Been interrupted by a trappable signal
** Force going to the top of the loop to handle it.
-*/
return -1;
}
if ( ret == 0 )
{
/*
** Time out occurred.
*/
return 0;
}
/*
** Some activity on the db
*/
ret = -1;
PQconsumeInput( conn );
do
{
notify = PQnotifies( conn );
if ( notify != NULL )
{
if ( cd-bpid != notify-be_pid )
{
strcpy( notif, notify-relname );
ret = 1
