Search code examples
javapostgresqljooq

How to configure JOOQ to pass hex strings instead of octal for bytea column insertion in PostgreSQL?

We are using JOOQ to insert records into postgresql table with bytea column.

The byte array we are sending is about 150Mb and we are getting an error from Postgresql server:

ERROR: invalid memory alloc request size 1073741824.

After digging into the code and the query JOOQ is generating, we noticed that the byteArray is converted into octal (org.jooq.util.postgres.PostgresUtils#toPGString(byte[])) And the query ends up looking something like:

INSERT INTO test_table (my_data) VALUES (E'\\042\\145\\171...\\042'::bytea);

Testing with smaller byte arrays revels that after the data is inserted, its size is indeed the size of the byte array and not the size of the octal string (which is much longer).

The issue seems to be that the octal string is so long that postgresql server fails on casting it to bytea even before trying to save it to the column.

Is there a way to configure JOOQ to pass hex string as Value instead of octal?

We are using JOOQ version 3.16.4 and postgresql 13.9

Clarification edit:

JOOQ is configured with the default DefaultConfiguration. No significant changes are done.

The sql above is taken from the exception thrown in: org.jooq.impl.Tools#translate(java.lang.String, java.lang.RuntimeException)

And the sql that it prints looks like that:

org.jooq.exception.DataAccessException: SQL [insert into "public"."table_name" ("id", "name", "version", "description", "archived", "other_id", "jws_data") values (829720463911690240, 'v-0', 'rb-0-6HW7', null, 0, 829720463899107328, E'\\042\\145\\171\\112\\162\\141\\127\\....\135\\175'::bytea) returning "public"."table_name"."id"]; Unspecified RuntimeException

We are not generating the SQL insert, instead we are using org.jooq.impl.DAOImpl#insert(P) and pass a JOOQ generated Record object that holds the byteArray as one of the members (jws_data). this name also corresponds to the column name in the table.

Note: I changed the SQL a bit to hide the exact column names so please ignore the names and the mismatch to the other SQLs.

StackTrace:

    toPGString:631, PostgresUtils (org.jooq.util.postgres)
    sqlInline0:2044, DefaultBinding$DefaultBytesBinding (org.jooq.impl)
    sqlInline0:1941, DefaultBinding$DefaultBytesBinding (org.jooq.impl)
    sql:937, DefaultBinding$AbstractBinding (org.jooq.impl)
    sql:929, DefaultBinding$AbstractBinding (org.jooq.impl)
    accept:186, Val (org.jooq.impl)
    visit0:720, DefaultRenderContext (org.jooq.impl)
    visit:295, AbstractContext (org.jooq.impl)
    toSQL92Values:326, FieldMapsForInsert (org.jooq.impl)
    toSQL92Values:278, FieldMapsForInsert (org.jooq.impl)
    accept:137, FieldMapsForInsert (org.jooq.impl)
    visit0:720, DefaultRenderContext (org.jooq.impl)
    visit:295, AbstractContext (org.jooq.impl)
    toSQLInsert:739, InsertQueryImpl (org.jooq.impl)
    accept0:624, InsertQueryImpl (org.jooq.impl)
    accept:642, AbstractDMLQuery (org.jooq.impl)
    visit0:720, DefaultRenderContext (org.jooq.impl)
    visit:295, AbstractContext (org.jooq.impl)
    getSQL0:484, AbstractQuery (org.jooq.impl)
    execute:287, AbstractQuery (org.jooq.impl)
    storeInsert0:191, TableRecordImpl (org.jooq.impl)
    lambda$storeInsert$0:157, TableRecordImpl (org.jooq.impl)
    apply:-1, TableRecordImpl$$Lambda$2120/0x0000000801a5bef8 (org.jooq.impl)
    operate:143, RecordDelegate (org.jooq.impl)
    storeInsert:156, TableRecordImpl (org.jooq.impl)
    insert:144, TableRecordImpl (org.jooq.impl)
    insert:139, TableRecordImpl (org.jooq.impl)
    insert:180, DAOImpl (org.jooq.impl)
    insert:156, DAOImpl (org.jooq.impl)
    save:80, RDBDao (org.jfrog.bintray.distribution.rdb)

Same behaviour can be reproduced by calling:

dslContext.insertInto(TEST_TABLE)
          .set(MY_DATA, jws_data_byte_array)
          .execute();

Or

dslContext.execute("insert into test_table (my_data) values (?)", jws_data_byte_array);

Same toPGString:631, PostgresUtils (org.jooq.util.postgres) is triggered.

Solution

  • As @Lukas Eder mentioned, We have found a place where the statementType was changed to STATIC_STATEMENT instead of the default PREPARED_STATEMENT.

    After removing this config JOOQ stoped creating the long octal strings and the insert works perfectly.