Jooq: Work around PostgreSQL's limit of 32767 bind variables per statement

[nkoterba]

This issue report is more edification for others as well as also some ideas on how to possibly handle the situation we encountered.

Using latest Jooq with Postgres 9.6. We have to supply 30k+ items to a where clause in Postgres. We know...this is a bad design but we have our reasons and are limited in the short term on workarounds.

NOTE: The IDs below are NOT actual auto-incrementing numbers or we could do things with range. I'm just using them to exemplify the large # of items we are passing into the where clause.

The sql query:

select * from products WHERE ID in (1, 2, 3, 4, ..., 31234, 31235)

In Jooq, we create the fluent where clause as so:

Collection<Long> ids = (1, 2, 3, 4, ..., 31234, 31235);
dslContext().selectFrom(PRODUCTS).where(PRODUCTS.ID.in(ids)).fetch();

However, we've noticed that using the default Jooq in behavior fails when the size of the INPUT array reaches ~30k items or more.

Since Jooq parameterizes queries by default, the actual query passed to Postgres looks like:

select * FROM products WHERE ID = ($1, $2, $3, ..., $31234, $31235)

The items with $ represent a parameterized item that JDBC and/or the Postgres Driver passes along to Postgres.

Here is the error we receive:

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 70663
       at org.postgresql.core.PGStream.sendInteger2(PGStream.java:211) ~[postgresql-9.4.1212.jar:9.4.1212]
       at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1409) ~[postgresql-9.4.1212.jar:9.4.1212]
       at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1729) ~[postgresql-9.4.1212.jar:9.4.1212]
       at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1294) ~[postgresql-9.4.1212.jar:9.4.1212]
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:280) ~[postgresql-9.4.1212.jar:9.4.1212]
       ... 113 common frames omitted

I've run the query in-lined in PG Admin (Postgres' management tool), and Postgres quickly returns the desired results (especially with appropriate indexes).

I believe the issue either lies with JDBC, the Postgres JDBC driver, or perhaps Postgres itself regarding the sheer # of parameterized items being sent to the DB.

We find if we use Jooq's DSL.inline and DSL.condition to turn the 30k+ items from being parameterized into a "SQL string" things work:

DSL.condition("{0} = any({1})", field, DSL.inline(idArray))

I'm not sure what the actual hard limit is (e.g. 32,000 or 30,000, etc) but it's near 30k.

I haven't given much thought to how Jooq could internally handle or correct such an issue. I do see that InCondition.java already has a unique case for FIREBIRD when a list of 1,000 or more items are used. However, I think that's just syntax adjustments and not a fundamental change in query strategy (e.g. going from parameterized to inlined).

While inlined does work, I do believe it imposes some additional SQL injection security considerations which should not be overlooked if passing in an array or collection of String objects over 30k+.

[lukaseder]

Thank you very much for your detailed report. Indeed, jOOQ already has a built-in guard against this problem in DefaultRenderContext.checkForceInline(). We've found such limits for:

• Access: 768
• Ingres: 1024
• SQL Server: 2100
• SQLite: 999
• Sybase ASE: 2000

I tried it with PostgreSQL 9.5 and pgjdbc 9.4.1209 and I've successfully passed 32767 bind variables to a statement. I've run some binary search to find out this value:

    public void testLargeBindVariableSets() throws Exception {
        Exception e = null;
        int i = 0x20000;
        int step = 0x10000;

        List<Integer> list = new ArrayList<>();

        for (;;) {
            try {
                testLargeBindVariableSet0(i);
                list.add(i);

                if (e == null || step == 0)
                    break;

                i = i + step;
            }
            catch (Exception e1) {
                e = e1;
                i = i - step;
            }

            step = step / 2;
        }

        if (e != null)
            Assert.fail("Maximum number of bind variables: " + i + " (successfully tried: " + list + ")");
    }

    private void testLargeBindVariableSet0(int i) {
        assertEquals(1, (int) create().selectOne().where(one().in(Collections.nCopies(i, 1))).fetchOne(0, int.class));
    }

[nkoterba]

@lukaseder Thanks for the followup.

So does that mean you've found Postgres' limit or that you have yet to find a limit?

(Also, as an aside, is there a reason DSL.inline does not support Collections and just arrays?) We just ran into an issue where we tried

DSL.condition("{0} = any({1})", f1, DSL.inline(items)))

items is a Collection (ArrayList). But when this hits Postgres we get an error that the DIALECT does not support ArrayList.

So we changed it to:

DSL.condition("{0} = any({1})", f1, DSL.inline(items.toArray)))

Is there a reason DSL.inline(Collection) is not supported?

[lukaseder]

So does that mean you've found Postgres' limit or that you have yet to find a limit?

The limit (on my installation) is 32767. The 32768th variable breaks the query.

Also, as an aside, is there a reason DSL.inline does not support Collections and just arrays

jOOQ (and PostgreSQL) needs to know the type of your PostgreSQL array. With JDK collections, that type is erased. Java arrays retain the array element type.

Hope this helps

[nkoterba]

@lukaseder Thanks for the clarification. Need my afternoon coffee. As for the JDK collection vs array, makes total sense.

As always, thanks for great support and great product.

[lukaseder]

Sure, you're welcome!

[tioback]

@lukaseder - Sorry for my ignorance, but does that mean that a solution has been coded into jOOQ to prevent such error, or do I have to manually check it?

[lukaseder]

@tioback : Yes, there's a solution in jOOQ 3.9+: As soon as jOOQ is about to generate more than 32767 bind variables for PostgreSQL, it will re-render the SQL statement with all bind variables inlined as constants (same as if you wrote DSL.inline() or if you used Settings.statementType == STATIC_STATEMENT)