Question 113908 in PostgreSQL Question posted in
The official documentation can be found here.

How to catch NOTICE from PostgreSQL in a c program

I use the following code to create a table if it does not exist yet:



    PGconn*     conn;
    PGnotify*   notify;
    PGresult*   createTableRes;

        createTableRes = PQexec(conn, "CREATE TABLE IF NOT EXISTS t_xyz (xyzId SERIAL PRIMARY KEY NOT NULL, xyz VARCHAR(100) UNIQUE NOT NULL)");
        notify = PQnotifies(conn);
        if (notify != NULL)
        {
            char* extraStr = notify->extra;
        }
        errorDescribed = PQerrorMessage(conn);

        if (PQresultStatus(createTableRes) != PGRES_COMMAND_OK)
        {
            errorDescribed = PQerrorMessage(conn);
            my_logger.error("could not create table t_xyz, errorMessage:{}", errorDescribed);
            PQclear(createTableRes);
            exit_nicely(conn);
        }
        else
        {
            my_logger.info("created table t_xyz successfully.");

I would have expected to get a notify when the table exists since the console logs "relation already exists skipping" but notify is always NULL.
I would like to catch that notification so that I could log something like "table t_xyz existed already" instead of "created table t_xyz successfully".
How could I do that?

I’ve searched for solutions but only found hints to use "set client_min_messages=NOTICE" and I tried to do that using

setRes = PQexec(conn, "set client_min_messages=INFO");

before creating the table but it did not change anything.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Earlier, I just had time for a quick and dirty try to confirm Laurenz's solution works.

    For anyone interested in how exactly it works, here is the modified version of my original code. Since PQsetNoticeReceiver expects a void function, I created a global variable notifyStr that stores the received notice so that I can evaluate it.

    PGconn*     conn;
PGnotify*   notify;
PGresult*   createTableRes;
std::string notifyStr = "";

static void
myrecv(void *arg, const PGresult *res)
{
    notifyStr = PQresultErrorField(res, PG_DIAG_MESSAGE_PRIMARY);
    printf("Received NOTICE: "%s"n", notifyStr.c_str()); 
}

    PQsetNoticeReceiver(conn, myrecv, NULL);

    createTableRes = PQexec(conn, "CREATE TABLE IF NOT EXISTS t_xyz (xyzId SERIAL PRIMARY KEY NOT NULL, xyz VARCHAR(100) UNIQUE NOT NULL)");
    notify = PQnotifies(conn);
    if (notify != NULL)
    {
        char* extraStr = notify->extra;
    }
    errorDescribed = PQerrorMessage(conn);

    if (PQresultStatus(createTableRes) != PGRES_COMMAND_OK)
    {
        errorDescribed = PQerrorMessage(conn);
        my_logger.error("could not create table t_xyz, errorMessage:{}", errorDescribed);
        PQclear(createTableRes);
        exit_nicely(conn);
    }
    else
    {
        if (notifyStr.find("already exists") != std::string::npos)
        {
            my_logger.info("table t_xyz existed already.");
        }
        else
        {
            my_logger.info("created table t_xyz successfully.");
        }
        notifyStr = "";
    }

  2. 0

    You are mixing up two different things: notice processing and asynchronous notification. The former is what you want, the latter is what PQnotifies() is about.

    You should call PQsetNoticeReceiver() to register a notice receiver, which will be called whenever a notice arrives in the course of statement execution.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search