Not retrieving NOTIFY with libpq in a C++ program

Question

I'm enhancing a ROS (robot operating system) stack sql_database to be able to handle postgresqls LISTEN and NOTIFY commands. As mentioned, I'm using libpq version 9.1.10-0 on Ubuntu12.04 within a C++ program. But for some reasons I'm not able to retrieve a NOTIFY.

I know, that there's an example (Example 28-2. libpq Example Program 2) and it works perfectly fine. I've played around with it quite a long time and also tried to copy it to my code as exactly as possible and change the example code in a way making it more similar to the code I'm having problems with. But that didn't help for my problem.

I can received notifies in the example program and in a manual login into the database, but not in the code I want to use.

What else did I try:

• Connecting to a different database - didn't change anything.
• Doing a COMMIT; after I performed a LISTEN <channel>; command. But this caused the warning as expected as I didn't have an open transaction.
• Check if the connection is dead before I do PQconsumeInput(connection_); - it's purely alive
• perform the LISTEN command in the same function, where I'm checking for a NOTIFY (using a breakpoint to trigger a NOTIFY in between) - didn't change anything.

The NOTIFY was always triggered manually with NOTIFY <channel>;

Codes structure

The code can also be seen here on github (on the unstable branches):

• class PostgresqlDatabase (in sql_interface->database_interface->src on github)
  This class holds the connection PGconn and provides tasks like

   bool listenToChannel(std::string channel);
  

  The main purpose of that class is to abstract the sql queries, so that ROS-programmers don't have to care about them anymore.

• class databaseBinding
  it's the glue between ROS and the database functionalities. It holds a PostgresqlDatabase object to get a database connection and to call the tasks.

• A main function
  Does the following things

  1. do some ROS initialization stuff
  2. create a databaseBinding object, which will initializes a PostgresqlDatabase object, which builds up a connection to my database
  3. call the PostgresqlDatabase::listenToChannel(std::string channel)-function
  4. Go in a loop checking for a NOTIFY periodically using the PostgresqlDatabase::checkNotify(notification &no)-function

Some code

Checking for a NOTIFY

The checkNotify function, which is triggered about 5 times per second:

/*! Checks for a received NOTIFY and returns it. */
bool PostgresqlDatabase::checkNotify(notification &no)
{
  PGnotify   *notify;
  PQconsumeInput(connection_);
  if ((notify = PQnotifies(connection_)) != NULL)
    {
    no.channel = notify->relname;
    no.sending_pid = notify->be_pid;
    no.payload = notify->extra;
    PQfreemem(notify);
    return true;
    } else
    {
    no.channel = "";
    no.sending_pid = 0;
    no.payload = "";
    PQfreemem(notify);
    return false;
    }
}

Listening to a channel

/*! Listens to a specified channel using the Postgresql LISTEN-function.*/
bool PostgresqlDatabase::listenToChannel(std::string channel) {
  //look, if we're already listening to the channel in our list
  if (std::find(channels_.begin(),channels_.end(),channel) == channels_.end() )
    {
      std::string query = "LISTEN " + channel;
      PGresultAutoPtr result = PQexec(connection_,query.c_str());
      if (PQresultStatus(*result) != PGRES_COMMAND_OK)
          {
              ROS_WARN("LISTEN command failed: %s", PQerrorMessage(connection_));
              return false;
          }
      ROS_INFO("Now listening to channel \"%s\"",channel.c_str());
      channels_.push_back(channel);
      return true;
    }
  ROS_INFO("We are already listening to channel \"%s\" - nothing to be done",channel.c_str());
  return true;
}

Answer 1

So it turned out, that there was something wrong with the connection. It was created with that code:

void PostgresqlDatabase::pgMDBconstruct(std::string host, std::string port, 
std::string user, std::string password, std::string dbname )
{
  std::string conn_info = "host=" + host + " port=" + port +
    " user=" + user + " password=" + password + " dbname=" + dbname;
  connection_= PQconnectdb(conn_info.c_str());
  if (PQstatus(connection_)!=CONNECTION_OK)
  {
    ROS_ERROR("Database connection failed with error message: %s", PQerrorMessage(connection_));
  }
}

With host=192.168.10.100, port=5432, user=turtlebot, password= , dbname=rosdb.
But an empty username doesn't satisfy the usage of PQconnectdb, which, for some parsing-reason, caused it to login to the database "turtlebot". That database unfortunately existed on my server. And there it - of course - didn't get any notifications sent out in the "rosdb"-database and had a good connection.

What an, for me, awkward and unlucky behaviour.