Incorrect sql generated by jooq on rare occasions

Question

I have a spring cloud gateway application saving authentication data to db. Application runs in kubernetes. I have too different cases, sometimes I have json data to save and other times json data is null. Code to save this data looks like this:

    with(AUTHENTICATED_SESSION) {
        context.insertInto(this, ACCESS_TOKEN_MD5_HASH, REFRESH_TOKEN, CONTEXT_DATA)
            .values(
                accessTokenMd5,
                refreshToken,
                if (contextData != null) {
                    JSONB.jsonb(objectMapper.writeValueAsString(contextData))
                } else {
                    null
                }
            )
            .execute()
    }

Generated column definition

public final TableField<AuthenticatedSessionRecord, JSONB> CONTEXT_DATA = createField(DSL.name("context_data"), SQLDataType.JSONB, this, "");

Saving null value usually works like it should be:

insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, cast(? as jsonb))
    : -> with bind values      : insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values ('hash', 'token', cast(null as jsonb))

Sometimes query looks like this and it causes exception below

insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, ?)
    : -> with bind values      : insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values ('hash', 'token', null)

org.jooq.exception.DataAccessException: SQL [insert into "public"."authenticated_session" ("access_token_md5_hash", "refresh_token", "context_data") values (?, ?, ?)]; ERROR: column "context_data" is of type jsonb but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 121
    at org.jooq_3.14.6.DEFAULT.debug(Unknown Source) ~[na:na]
    at org.jooq.impl.Tools.translate(Tools.java:2880) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:757) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:389) ~[jooq-3.14.6.jar:na]
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:119) ~[jooq-3.14.6.jar:na]

So it seems that on rare occasions jooq generates incorrect sql (without cast to jsonb).

The problem is really annoying as application is not usable at all when this happens. Restarting the pod helps. Most likely this problem appears only after application is started.

Used versions:

• jooq 3.14.6
• spring boot 2.3.5.RELEASE
• spring cloud gateway 2.2.5.RELEASE
• Postgresql 10.12 in docker container
• postgresql driver 42.2.5
• kotlin 1.4.10

Answer 1

There's a hint in your question, and every time I see it, I'm very happy to have implemented this feature:

 at org.jooq_3.14.6.DEFAULT.debug(Unknown Source) ~[na:na]

DEFAULT refers to SQLDialect.DEFAULT, but it should be SQLDialect.POSTGRES in your case. The reason why you're getting invalid SQL is because in those cases, you don't have a correctly configured Configuration.

That should help you track down the problem, keeping in mind that jOOQ Query objects aren't thread safe.