[FIXED] How to catch NOTICE from PostgreSQL in a c program

Issue

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.

Solution

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.

Answered By - Laurenz Albe

Answer Checked By - Cary Denson (PHPFixing Admin)