Re: LISTEN/NOTIFY problem

["Harvey, Allan AC" <HarveyA@xxxxxxxxxxxx>]

 

-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of Dmitri Girski
Sent: Monday, 23 March 2009 10:00 PM
To: pgsql-general@xxxxxxxxxxxxxx
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.