JBDC prepared statement test with large Bind packet gets stuck

[JelteF]

Use odyssey-dev.conf, but with the following default database and listen on port 5432

database default {
    user default {
        authentication "md5"

        password "test"

        storage "postgres_server"
        pool "transaction"
        pool_size 0

        pool_reserve_prepared_statement yes
        client_max 1000
    }
}

Then do

git clone https://github.com/pgjdbc/pgjdbc
cd pgjdbc
./gradlew cleanTest && ./gradlew test --tests org.postgresql.test.jdbc42.PreparedStatement64KBindsTest

[x4m]

Hi Jelte! That's interesting, thanks for reporting. We'll look into this.

[jayant07-yb]

@x4m I am also getting a similar problem if I run large queries or long passwords. It seems like the client application gets stuck if it sends a large packet to the Odyssey.

I used this c++ program to test this issue.

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
#include <string.h>

using namespace std;
// Database connection parameters
const char *conninfo = "dbname=yugabyte user=yugabyte password=yugabyte host=10.150.1.208 port=5433";

// Table creation SQL query
const char *create_table_query =
    "CREATE TABLE IF NOT EXISTS my_table ("
    "   id serial PRIMARY KEY,"
    "   name VARCHAR(255) NOT NULL,"
    "   age INT"
    ");";

int main(int argc, char *argv[]) {
    if (argc != 3) {
        fprintf(stderr, "Usage: %s <starting_point> <ending_point>\n", argv[0]);
        return 1;
    }

    int starting_point = atoi(argv[1]);
    int ending_point = atoi(argv[2]);

    if (starting_point > ending_point) {
        fprintf(stderr, "Starting point cannot be greater than ending point\n");
        return 1;
    }

    // Establish a connection to the PostgreSQL database
    PGconn *conn = PQconnectdb(conninfo);

    // Check if the connection was successful
    if (PQstatus(conn) != CONNECTION_OK) {
        fprintf(stderr, "Connection to database failed: %s", PQerrorMessage(conn));
        PQfinish(conn);
        return 1;
    }

    // Create the table if it doesn't exist
    PGresult *create_table_res = PQexec(conn, create_table_query);
    if (PQresultStatus(create_table_res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Table creation failed: %s", PQerrorMessage(conn));
        PQclear(create_table_res);
        PQfinish(conn);
        return 1;
    }
    PQclear(create_table_res);
    int buf_size = 12 * ending_point * 2 +  1024;
    // Construct a single INSERT query for the specified range
    char insert_query[buf_size];  // Adjust buffer size as needed
    snprintf(insert_query, sizeof(insert_query), "INSERT INTO my_table (name, age) VALUES ");
    for (int i = starting_point; i <= ending_point; i++) {
        snprintf(insert_query + strlen(insert_query), sizeof(insert_query) - strlen(insert_query),
                 "('%s', %d)", "Person", 20 + i);
        if (i < ending_point) {
            strncat(insert_query, ", ", sizeof(insert_query) - strlen(insert_query));
        }
    }

    // Execute the single INSERT query
    PGresult *insert_res = PQexec(conn, insert_query);
    if (PQresultStatus(insert_res) != PGRES_COMMAND_OK) {
        fprintf(stderr, "Insertion failed: %s", PQerrorMessage(conn));
        PQclear(insert_res);
        PQfinish(conn);
        return 1;
    }
    PQclear(insert_res);

    // Clean up and close the database connection
    PQfinish(conn);

    return 0;
}

To run this program change the host and port in the code, compile it and run it with "number of columns to insert" as the input arg.