PostgreSQL JDBC Driver – Upper Limit on Parameters in PreparedStatement

A lesser known limit of the PostgreSQL JDBC driver is the passing of at most 32767 variables into a PreparedStatement. This upper bound value on the number of parameters is derived from the maximum value of the signed Short data type in Java.

This limit is very rarely reached by majority of its users, but is interesting to observe when it happens. In this post, I will show that this limit is not present when using the psql CLI client to connect directly to the PostgreSQL database, and only appears when using the JDBC driver. Subsequently I will suggest ways to address this limit.

Setup

  • Host OS: Ubuntu Server 18.04
  • PostgreSQL Database: PostgreSQL 11.7 on Alpine Linux v3.10 LXC container (setup)
  • JDBC Driver: PostgreSQL JDBC 4.2 Driver, 42.2.12 (official site)

Step 1: Setting a Password for the Test User

I am using the postgres user as the test user, and setting the password as postgres. Depending on your target database, this may not be a very secure option. However, this is acceptable for my throw-away demonstration environment:

host-VM $ lxc exec postgres -- su postgres -c psql

postgres=# \password
Enter new password: 
Enter it again:

Step 2: Creating the Target Table and Large Query

I created a new database and table as a target to query against. It is not required to insert any data into this table.

host-VM $ lxc exec postgres -- su postgres -c psql

postgres=# create database testing;
CREATE DATABASE
postgres=# \c testing;
You are now connected to database "testing" as user "postgres".
testing=# create table numbers (position integer);
CREATE TABLE
testing=# exit

As for the query, I wrote a simple Java program that attempts to select rows whose column values are contained within a large list of candidates:

public class LargeQueryGenerator {
  public static void main (String args[]) {
    String query = "SELECT * FROM numbers WHERE position IN (";
	int i = 0;
	for (; i < 32800; i++) {
		query += Integer.toString(i);
		query += ",";
	}
	query += Integer.toString(i) + ");";
	System.out.println(query);
  }
}

By running the program above, I get a query which looks like this:

SELECT * FROM numbers WHERE position IN (0,1,2,3, ... ,32799,32800);

I saved the output into a file that is arbitrarily called myLargeQuery.

Step 3: Running the Large Query via the psql CLI Client

In this step, I want to run the large query against the testing table via the psql CLI client, so as to show that it can execute successfully.

Before I can run the query, I need to place it into the LXC container:

host-VM $ lxc file push myLargeQuery postgres/tmp/myLargeQuery

Next, I use the following command to run it from inside the container via psql:

host-VM $ lxc exec postgres -- su postgres -c "psql -U postgres -d testing -a -f /tmp/myLargeQuery"
SELECT * FROM numbers WHERE position IN (0,1,2,3, ... ,32799,32800);
 position
----------
(0 rows)

The query completed successfully with no rows found (which is expected).

Step 4: Running the Large Query via the PostgreSQL JDBC Driver

In this step, I want to run the same large query against the testing table via the PostgreSQL JDBC driver, showing that it fails

I created a new Java program to make use of a PreparedStatement and the PostgreSQL JDBC driver to execute the same query:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
public class PsqlLargeQueryTest {
    private static int TOTAL_PARAMTER_COUNT;
    public static void main(String args[]) {
        TOTAL_PARAMTER_COUNT = Integer.parseInt(args[0]);
        try {
            Connection conn = setupConnection();
            PreparedStatement st = conn.prepareStatement(generateLongQuery());
            setValues(st);
            ResultSet rs = st.executeQuery();
            if (rs.next()) {
                System.out.println("Found some rows");
            } else {
                System.out.println("No rows found");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static Connection setupConnection() throws SQLException {
        String url = "jdbc:postgresql://(POSTGRES_LXC_CONTAINER_IP_ADDR)/testing?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);
        return conn;
    }
    private static String generateLongQuery() {
        StringBuilder query = new StringBuilder("SELECT * FROM numbers WHERE position IN (");
        for (int i = 0; i < TOTAL_PARAMTER_COUNT; i++) {
            query.append("?,");
        }
        query.setCharAt(query.length() - 1, ')');
        query.append(";");
        return query.toString();
    }
    private static void setValues(PreparedStatement st) throws SQLException {
        for (int i = 0; i < TOTAL_PARAMTER_COUNT; i++) {
            st.setInt(i + 1, i);
        }
    }
}

Side note: I referenced the PostgreSQL jdbc driver PreparedStatement example

Next, I run the new Java program with the “PostgreSQL JDBC 4.2 Driver, 42.2.12”, which is the latest version at the time of writing this post:

host-VM $ javac PsqlLargeQueryTest.java
host-VM $ java -cp 'postgresql-42.2.12.jar:.' PsqlLargeQueryTest 38000
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:340)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
        at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
        at PsqlLargeQueryTest.main(PsqlLargeQueryTest.java:14)
Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 38000
        at org.postgresql.core.PGStream.sendInteger2(PGStream.java:275)
        at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1536)
        at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1859)
        at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1422)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:305)
        ... 5 more

We can see that the query now fails when using a PreparedStatment via the PostgreSQL JDBC driver.

However, when we run it with the maximum value of a signed short:

host-VM $ java -cp 'postgresql-42.2.12.jar:.' PsqlLargeQueryTest 32767
No rows found

Step 5: Finding the Root Cause

When we look at the error, we observe that the top most error is a PSQLException, which indicates that there was an issue with communicating with the backend. Digging deeper, the underlying cause is actually a Java IOException caused by the PGStream sendInteger2() method.

This method, as per the comments, “sends a 2-byte integer (short) to the back end”. As such, PreparedStatements have a upper limit of 32767 parameters.

Step 6: Workarounds

Here are some ways to workaround the limits of PreparedStatement parameters:

  1. Rewriting the Query
    • It might be a good time to take a look at the steps leading up to the query itself to see if there is anyway to streamline the flow to avoid passing in that many parameters into a single PreparedStatement.
  2. Chunking the Parameter List
    • Instead of passing in all the parameters in the same PreparedStatement, chunk the input (e.g. using the Arrays.copyOfRange() method) into manageable sizes.
  3. Using an Array
    • Similar to the first method, one could rewrite the query to use arrays instead. For example: SELECT * FROM numbers WHERE position = ANY(?);
    • This reduces the number of parameters in the PreparedStatement, whilst allowing an arbitrary large number of elements in the array. Here is a modification of the code doing just that:
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;

public class PsqlLargeQueryIntArray {
    private static int TOTAL_PARAMTER_COUNT;
    public static void main(String args[]) {
        TOTAL_PARAMTER_COUNT = Integer.parseInt(args[0]);
        try {
            Connection conn = setupConnection();
            PreparedStatement st = conn.prepareStatement(generateLongQuery());
            setValues(conn, st);
            ResultSet rs = st.executeQuery();
            if (rs.next()) {
                System.out.println("Found some rows");
            } else {
                System.out.println("No rows found");
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
    private static Connection setupConnection() throws SQLException {
        String url = "jdbc:postgresql://(POSTGRES_LXC_CONTAINER_IP_ADDR)/testing?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);
        return conn;
    }
    private static String generateLongQuery() {
        return "SELECT * FROM numbers WHERE position = ANY(?);";
    }
    private static void setValues(Connection conn, PreparedStatement st) throws SQLException {
        ArrayList < Integer > intArray = new ArrayList < Integer > ();
        for (int i = 0; i < TOTAL_PARAMTER_COUNT; i++) {
            intArray.add(i);
        }
        Array myArray = conn.createArrayOf("integer", intArray.toArray());
        st.setArray(1, myArray);
    }
}

The compilation and the execution remain unchanged from the previous step.

This list is not exhaustive – feel free to share some of your methods to workaround this limitation 🙂

One thought on “PostgreSQL JDBC Driver – Upper Limit on Parameters in PreparedStatement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s