Re: [GENERAL] LISTEN/NOTIFY problem

[Harvey, Allan AC]

 

-----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;
                                }
 
                        PQfreemem( notify );
                        }
                }
        while ( notify != NULL );
 
        return ret;
}
 
 


The material contained in this email may be confidential, privileged or 
copyrighted. If you are not the intended recipient, use, disclosure or copying 
of this information is prohibited. If you have received this document in error, 
please advise the sender and delete the document. Neither OneSteel nor the 
sender accept responsibility for any viruses contained in this email or any 
attachments.