PSQL prepared statement query hangs when size of parameters exceeds 393166 characters

Question

I am working on a C++ app and making inserts into a local postgres database using the libpq library. I am running into an issue where if I attempt to query the database with a prepared statement, the query appears to hang if the parameters are too long (specifically this starts to happen at exactly 393167 characters [combined character count of paremeters]). I am new to psql so I will try to be as specific as I can. Let me know if more information is needed.

Here are some notes / steps I took so far:

• I changed the log_statments to 'all' in postgresql.conf. It appears that the query is reaching the database because it's logging the query.
• If the the lengths of the paramters is even one less (393166) it will run with no issue.
• If I run a similar INSERT query through libpq without parameters/prepared statement and just execute directly, it runs fine for much larger queries (millions of characters). EX: insert into...values('test12', 'aaa....');
• It doesn't seem to matter what the characters are.
• Query performance doesn't slow down as it approaches this limit. It's very fast but appears to hit a wall when it reaches this character limit.
• I've increased work_mem to the maximum allowable "2097151kB", but that hasn't helped. I am guessing that there is some issue with my postgres setup but I'm having a lot trouble figuring out what it could or where to look.

Here is an example query: (As it appears in the logs)

2023-05-04 16:25:04.657 CDT [36340] LOG:  execute 2: INSERT INTO public.knowledge 
(userid, knowledge) VALUES ($1, $2)

2023-05-04 16:25:04.657 CDT [36340] DETAIL:  parameters: $1 = 'test12', $2 = 
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa.....'

Answer 1

I tried to replicate the issue on Linux and couldn't. I don't have a Windows system available at the moment. I created a test C++ file that wrote large text to PostgreSQL 15.2 and was able to write it without an issue using libpq5.

System

uname -a
Linux 75db15f94bd2 5.15.49-linuxkit #1 SMP Tue Sep 13 07:51:46 UTC 2022 x86_64 x86_64 x86_64 GNU/Linux

cat /etc/*release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=22.04
DISTRIB_CODENAME=jammy
DISTRIB_DESCRIPTION="Ubuntu 22.04.2 LTS"
PRETTY_NAME="Ubuntu 22.04.2 LTS

DB

psql (15.2 (Ubuntu 15.2-1.pgdg22.04+1))

g++

g++ --version
g++ (Ubuntu 11.3.0-1ubuntu1~22.04) 11.3.0

libpq

apt list --installed | grep libpq

libpq-dev/jammy-pgdg,now 15.2-1.pgdg22.04+1 amd64 [installed]
libpq5/jammy-pgdg,now 15.2-1.pgdg22.04+1 amd64 [installed]

Table

test=# \dt
           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
 public | knowledge | table | postgres
(1 row)

test=# \d knowledge
                      Table "public.knowledge"
  Column   |          Type          | Collation | Nullable | Default
-----------+------------------------+-----------+----------+---------
 userid    | character varying(255) |           |          |
 knowledge | text                   |           |          |

C++ code

#include <stdio.h>
#include <postgresql/libpq-fe.h>
#include <string>
#include <iostream>

int main()
{
        PGconn          *conn;
        PGresult        *res;
        int             rec_count;
        int             row;
        int             col;

        conn = PQconnectdb("dbname=test host=localhost user=postgres password=test");

        if (PQstatus(conn) == CONNECTION_BAD)
        {
                puts("We were unable to connect to the database");
                exit(0);
        }

        res = PQexec(conn, "select userid, knowledge from knowledge");
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
                puts("We did not get any data!");
                exit(0);
        }

        rec_count = PQntuples(res);

        printf("We received %d records.\n", rec_count);
        puts("==========================");
        PQclear(res);

        const char command[] = "insert into knowledge values($1, $2);";
        char cid[] = "10";
        char name[] = "aaaaaaaaaa bbbbbb...many, many characters";
        int nParams = 2;
        const char *const paramValues[] = {cid, name};
        const int paramLengths[] = {sizeof(cid), sizeof(name)};
        const int paramFormats[] = {0, 0};
        int resultFormat = 0;

        res = PQexecParams(conn, command, nParams, NULL, paramValues, paramLengths, paramFormats,resultFormat);

        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                puts("Data NOT entered");
                std::cout << "PQexecParams failed: " << PQresultErrorMessage(res) << std::endl;
                exit(0);
        }

        PQclear(res);
        PQfinish(conn);

        return 0;
}

Compile and run

g++ test.cpp -lpq

./a.out
We received 5 records.
==========================

After it prints the output, it also does the insert.

Let's look at the database now.

test=# select userid, length(knowledge) from knowledge;
 userid | length
--------+--------
 10     |     11
 10     | 393165
 10     | 393166
 10     | 393167
 10     | 393168
 10     | 393173

I was able to insert large text through parameterization.

You are welcome to take my code and supply it your data and see if the issue persists for you.

If you DON'T see issue with this code on Windows, then libpq is fine.

If you see the issue, then I'd be curious whether it is libpq or PG 15.2. Then, we'd have to do elimination test.

• Keep libpq and run the code against PG 12 and see if that works better, or
• Keep PG 15.2 and use a different library than libpq.

References

• https://gist.github.com/ictlyh/12fe787ec265b33fd7e4b0bd08bc27cb
• https://www.linuxjournal.com/content/accessing-postgresql-cc
• https://www.postgresql.org/docs/current/libpq-connect.html
• https://www.tutorialspoint.com/postgresql/postgresql_c_cpp.htm
• https://zetcode.com/db/postgresqlc/