libpq ERROR: insufficient data left in message

Question

I am using libpq to interact with PostgreSQL DB. I'm getting below error while trying to update the table.

ERROR: insufficient data left in message

I have created a sample code with same parameters and it also is giving me the same error Below is the sample code

    std::string sQuery("update client_backlog_period set starttime = $1, endtime = $2, unavailable = $3 where periodid = $4");

    int nElem = 4;
    
    timestamp start_time = 736260060000000, end_time = 736260900000000;
    short nUnavailable = 0;
    long nPeriodid = 1;

    start_time = boost::endian::native_to_big(start_time);
    end_time = boost::endian::native_to_big(end_time);
    nUnavailable = boost::endian::native_to_big(nUnavailable);
    nPeriodid = boost::endian::native_to_big(nPeriodid);

    const char* paramValues[4] = { (char*)&start_time, (char*)&end_time, (char*)&nUnavailable, (char*)&nPeriodid};
    Oid paramTypes[4] = { TIMESTAMPOID, TIMESTAMPOID, INT2OID , INT8OID };

    int paramLengths[4] = { sizeof(start_time), sizeof(end_time),sizeof(nUnavailable), sizeof(nPeriodid) };
    int paramFormats[4] = { 1,1,1,1 };
    PGresult* res = PQexecParams(db.m_pPGConn,
            sQuery.c_str(),
            nElem,
            paramTypes,
            paramValues,
            paramLengths,
            paramFormats,
            1
        );

    ExecStatusType status = PQresultStatus(res);
    if (status != PGRES_COMMAND_OK)
    {
        std::cerr << "\n Update Query failed, Error: " << PQerrorMessage(res) << ",status: " << status << std::endl;
PQclear(res);
    }

PQerrorMessage(res) is returning ERROR: insufficient data left in message
status is PGRES_FATAL_ERROR

The insertion and select stmt are working fine.

score 0 · Answer 1 · answered May 02 '23 at 08:59

0

This might be down to a few things (referenced in the below post) I'll summarize them here.

There is a datatype mismatch between the value you are attempting to update and the datatype defined for those columns within the table (starttime,endtime or unavailable in your case)
Your data has a null character in it "\0"
An issue to do with DB encoding, change to UTF-8 and this works.

Postgres: org.postgresql.util.PSQLException: ERROR: insufficient data left in message

answered May 02 '23 at 08:59

Stephen Wood

1

I have already gone through the given link. In my case both server and client has same encoding which is UTF8 – Rakesh Mehta May 02 '23 at 09:07
Could you share your table definition for client_backlog_period. It also might be worth turning that update into an insert against a CTAS copy of that table minus any PK definition within this code just to see what is being written. – Stephen Wood May 02 '23 at 10:00
It might also by worth testing out a filter out of null characters on the assumption that there are some e.g.. `UPDATE my_table SET my_column = replace(my_column, '\x00', '') ` – Stephen Wood May 02 '23 at 10:19
Also.. Check that your libpq library version matches the postgres server version you are connecting to. When trying the insert suggestion above try using bytea datatypes that can store null characters – Stephen Wood May 02 '23 at 11:05
The libpq version I'm using is 15.2 (I installed it from VS vcpkg) and server major version is 11. I have updated the table description as image in question only. For now I have changed the the paramFormat from 1 to 0 for all and change the paramValues accordingly and now its working. If this issue is due to mismatch b/w client and server version than I believe its better to go with text format. – Rakesh Mehta May 03 '23 at 04:55
1

Good stuff, glad it's sorted. – Stephen Wood May 03 '23 at 06:17

libpq ERROR: insufficient data left in message

1 Answers1