Migrating jOOQ from 3.11 to 3.14 - struggling to debug indexing issue related to convertors/bindings for custom class to JSONB

[Sahil Dogra]

Hello, I am currently working on migrating jOOQ from version 3.11.3 to 3.14.9 and making all the necessary changes. For the most part, the migration is complete, but when I deploy my code and insertions/updates are made (via DSLContext's executeInsert and executeUpdate) to our Postgres DB via jOOQ DSL methods, for resources that have been created via jOOQ code generation from a production table, I am encountering an indexing issue. 

I have included relevant screenshots above that indicate the indexing issue (note I have intentionally omitted parts of the stack trace for privacy reasons), please ask if you have any questions. 

I am only seeing these errors occur when writing records to tables with JSONB objects for which we have implemented custom converter/bindings (following the doc: https://www.jooq.org/doc/3.14/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-binding/). 

Here is a relevant code snippet from our binding class that is very similar to the example in the documentation, was wondering if anything seemed off? Left out a couple details, but basically this binding is for generic custom List<T> and we have other classes extending it with the actual converter with to() and from() methods, where we basically just use GSON to convert between JSONB and our custom class. We have unit tests that test the converter's to() and from() methods so the issue shouldn't be arising from the converter. 

@Override
public void sql(final BindingSQLContext<List<T>> bindingSQLContext) throws SQLException {

// Depending on how you generate your SQL, you may need to explicitly distinguish
// between jOOQ generating bind variables or inlined literals.
if (bindingSQLContext.render().paramType() == ParamType.INLINED) {
bindingSQLContext.render().visit(DSL.inline(bindingSQLContext.convert(converter()).value())).sql("::jsonb");
} else {
bindingSQLContext.render().sql(bindingSQLContext.variable()).sql("?::jsonb");
}
}

@Override
public void register(final BindingRegisterContext<List<T>> bindingRegisterContext)
throws SQLException {
bindingRegisterContext.statement().registerOutParameter(bindingRegisterContext.index(), Types.VARCHAR);
}

@Override
public void set(final BindingSetStatementContext<List<T>> bindingSetStatementContext)
throws SQLException {
JSONB json = bindingSetStatementContext.convert(converter()).value();
bindingSetStatementContext.statement().setString(bindingSetStatementContext.index(), json != null ? json.data() : null);
}

@Override
public void set(final BindingSetSQLOutputContext<List<T>> bindingSetSQLOutputContext)
throws SQLException {
throw new SQLFeatureNotSupportedException();
}

@Override
public void get(final BindingGetResultSetContext<List<T>> bindingGetResultSetContext)
throws SQLException {
bindingGetResultSetContext.convert(converter()).value(JSONB.valueOf(bindingGetResultSetContext.resultSet().getString(bindingGetResultSetContext.index())));
}

@Override
public void get(final BindingGetStatementContext<List<T>> bindingGetStatementContext)
throws SQLException {
bindingGetStatementContext.convert(converter()).value(JSONB.valueOf(bindingGetStatementContext.statement().getString(bindingGetStatementContext.index())));
}

@Override
public void get(final BindingGetSQLInputContext<List<T>> bindingGetSQLInputContext)
throws SQLException {
throw new SQLFeatureNotSupportedException();
}

[Rob Sargent]

On 3/27/23 14:43, Sahil Dogra wrote:
> Hello, I am currently working on migrating jOOQ from version 3.11.3 to
> 3.14.9 and making all the necessary changes. For the most part, the
> migration is complete, but when I deploy my code and
> insertions/updates are made (via DSLContext's executeInsert and
> executeUpdate) to our Postgres DB via jOOQ DSL methods, for resources
> that have been created via jOOQ code generation from a production
> table, I am encountering an indexing issue.

(Always best to copy paste text rather than screen shots)
You might need to look at the exact classpaths in play as you run your
tests:  The code and the database disagree on at least one table so I
suspect you're pointing new code at old database or vice versa.

[Lukas Eder]

Thank you for your message Sahil.

Can you please post the error message from the stack trace (i.e. the complete stack trace)?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/8137e295-be48-42bd-af61-cbaccce186aan%40googlegroups.com.

[Lukas Eder]

For the record, I'm fine with screenshots.

[Sahil Dogra]

Thank you both for your responses, I was actually just able to figure out my issue! Changing the line 

bindingSQLContext.render().sql(bindingSQLContext.variable()).sql("?::jsonb");

to 

bindingSQLContext.render().sql("?::jsonb");

resolved it. 

[Lukas Eder]

Ah yes, I totally missed that. Do you recall what may have been the rationale behind duplicating the variable? I don't recall any changes in jOOQ that would have produced the regression you've encountered. Did you perhaps also update the JDBC driver at the same time? Recent pgjdbc versions implemented a feature where ?? is now the way to escape the ? character if you want to use the ? operator, e.g. for JSON. See:

https://stackoverflow.com/q/38370972/521799

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/571135e3-0703-41a0-a85c-69c5ce749334n%40googlegroups.com.

[Sahil Dogra]

As for the rationale - not sure on my end, I am picking up the codebase after it had initially been setup a few years ago. Thanks for your input regarding the ?? feature in recent pgjdbc versions, was not aware. 

Thanks a lot Lukas, really appreciate all the work you put in for jOOQ! 

[Lukas Eder]

You're welcome, and thanks for your nice words.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/cc8c4569-20cf-4462-b971-1424968efeb9n%40googlegroups.com.