Jooq: UDT data types are unqualified in PostgreSQL, when there is more than one UDT data type per schema

[desiderantes]

Expected behavior and actual behavior:

When inserting an array of UDT in postgres, the generated query adds a cast that does not use the full name of the type, which results in failure when they type does not belong to the default schema.

Steps to reproduce the problem:

Create a separate schema

define a type there

use your new type in an array column.

generate an insert to that column.

Versions:

• jOOQ: 3.10.6
• Java: 8 / Kotlin 1.2.41
• Database (include vendor):PostgreSQL
• OS: GNU/ Linux 4.15.18
• JDBC Driver (include name if inofficial driver): official pgjdbc

[lukaseder]

Thanks for your report. For the avoidance of doubt, would you mind showing your steps in actual DDL? Specifically

use your new type in an array column.

In a column of a table on the other schema or in the default schema?

generate an insert to that column.

How exactly did you do the insert statement?

[desiderantes]

In a column of a table on the other schema or in the default schema?

In the same schema as the type

How exactly did you do the insert statement?

Both by setting ith with the DSL (dsl.insertInto(TABLE).set(TABLE.UDT_ARR, arrayOfUDTRecord).execute()) and by using a prefilled Record ( dsl.insertInto(TABLE).set(tableRecordContainingArray).execute())

[lukaseder]

I cannot reproduce this with the following test, neither on jOOQ 3.11 nor on jOOQ 3.10.7:

SQL:

CREATE SCHEMA IF NOT EXISTS s_7496;
CREATE TYPE s_7496.u_7496 AS (
  a INT,
  b INT
);
CREATE TABLE s_7496.t_7496 (
  id INT NOT NULL,
  arr s_7496.u_7496[],

  CONSTRAINT pk_t_7496 PRIMARY KEY (id) 
);

Java:

    @Test
    public void testPostgresMultiSchemaArrayOfUdt() {
        clean(T_7496);

        U_7496Record r = new U_7496Record(1, 2);
        assertEquals(1,
            create().insertInto(T_7496)
                    .columns(T_7496.ID, T_7496.ARR)
                    .values(1, null)
                    .execute());

        assertEquals(1,
            create().insertInto(T_7496)
                    .columns(T_7496.ID, T_7496.ARR)
                    .values(2, new U_7496Record[0])
                    .execute());

        assertEquals(1,
            create().insertInto(T_7496)
                    .columns(T_7496.ID, T_7496.ARR)
                    .values(3, new U_7496Record[] { r })
                    .execute());

        Result<T_7496Record> result = create()
            .selectFrom(T_7496)
            .orderBy(T_7496.ID)
            .fetch();

        assertEquals(3, result.size());
        assertEquals(asList(1, 2, 3), result.getValues(T_7496.ID));
        assertNull(result.get(0).getArr());
        assertEquals(0, result.get(1).getArr().length);
        assertEquals(1, result.get(2).getArr().length);
        assertEquals(r, result.get(2).getArr()[0]);
    }

How can this be reproduced? Please, can you provide an MVCE rather than describing the situation with text:
https://stackoverflow.com/help/mcve

[desiderantes]

To provide an example: inside our example schema, we create :

CREATE TYPE CUSTOM_TYPE AS (first_field TEXT, second_field TEXT);

CREATE TABLE custom_table (
    table_name TEXT NOT NULL,
    table_arr CUSTOM_TYPE[] NOT NULL
);

When inserting from jOOQ, we do :

CustomTypeRecord[] arr = new CustomTypeRecord[]{new CustomTypeRecord("first", "second")};
dsl.insertInto(CUSTOM_TABLE).columns(CUSTOM_TABLE.TABLE_NAME, CUSTOM_TABLE.TABLE_ARR).values( "example", arr);

The generated query is:

INSERT INTO "example"."custom_table" (
    "custom_table"."table_name",
    "custom_table"."table_arr"
) values (
    'example',
    cast('{"(\"first\",\"second\")"}' as custom_type[])
);

Which fails with ERROR: type "custom_type[]" does not exist

The correct output should be
```sql
INSERT INTO "example"."custom_table" (
"custom_table"."table_name",
"custom_table"."table_arr"
) values (
'example',
cast('{"(\"first\",\"second\")"}' as "example".custom_type[])
);

[lukaseder]

Interesting, thanks for following up. In the past, there had been such problems, but not in the current version anymore. The query that my integration test produces is:

insert into "s_7496"."t_7496" ("id", "arr") values (?, ?::s_7496.u_7496[])

Or, with inlined bind values:

insert into "s_7496"."t_7496" ("id", "arr") values (3, cast('{"(\"1\",\"2\")"}' as s_7496.u_7496[]))

As you can see, the types are fully qualified.

Do you perhaps have some schema mapping in place? How did you configure your Configuration and Settings? Also, how did you get to that SQL string exactly, because the way it is formatted is not how jOOQ outputs it (mixed case keywords, specific indentation)

[lukaseder]

Also, are you sure you're on jOOQ 3.10.6? Perhaps, there's an older version of jOOQ on your classpath without you noticing, e.g. because of some third party dependency?

[desiderantes]

Oh sorry, i just rewrote the current error, I must have changed the casing. And yes, I'm pretty sure we're on 3.10.6 (via a version override in our pom.xml) with Spring Boot 2 and the jooq starter. The current configuration is mostly automated by Spring, I only do

@Bean
@Primary
fun jooqConfig(connectionProvider: ConnectionProvider,
               transactionProvider: TransactionProvider, executeListenerProvider: ExecuteListenerProvider): org.jooq.Configuration {

    return DefaultConfiguration() //
            .derive(connectionProvider) //
            .derive(transactionProvider) //
            .derive(executeListenerProvider) //
            .derive(SQLDialect.POSTGRES)
}

So no fancy schema mapping. We just use a different mapping because the default one is used for something else, so it's not a multitenant situation.

[desiderantes]

Replicating this has been quite difficult, but moving to the config provided by Spring Boot solved my issue. Thanks Lukas for your time.

[lukaseder]

OK. Thanks for the feedback!

[eduramiba]

Hi,
I am able to reproduce this when my catalog contains more than one UDT.

I had some code that worked fine but stopped working when I added a second UDT, without any other change.

My jOOQ version is 3.11.5

Thanks!

[eduramiba]

The generated code does not seem to be the problem (a diff does not show anything related to the unchaged but broken table), it must be the runtime logic.

I will try to provide a MCVE.

[eduramiba]

Hi @lukaseder
I just uploaded a MCVE for this issue that is very similar to my real code: https://github.com/eduramiba/jooq-issue-7496

It should run just with mvn install, but it also needs docker to run PostgreSQL. Please contact me if you can't run it or something.

Thanks for your support!

[eduramiba]

For anyone having this problem, a workaround is changing PostgresSQL search path:

SET search_path TO my_schema, public;

[lukaseder]

@eduramiba: Thanks a lot for the report and the MCVE. I haven't forgotten about this issue but there had been quite a few other ones in the pipeline recently. Will hopefully get to looking into this very soon!

[eduramiba]

Thanks Lukas!

[lukaseder]

OK, finally - I can now reproduce the issue. Will proceed to fixing right away

[eduramiba]

Awesome! :)

[lukaseder]

Can confirm now. Very mysterious! As soon as the test type and table from this comment https://github.com/jOOQ/jOOQ/issues/7496#issuecomment-389552036 is duplicated, and the test is run on both tables, the problem appears, but only on the second execution.

[lukaseder]

It looks like a very weird class initialisation issue. The problem is that the overridden UDTImpl.getSchema() method in the second UDT returns null when it is called the first time (unlike in the first UDT). This null value indicates that there is no schema (e.g. the default schema), the value is cached in the UDT.getDataType() instance, and thus the observed behaviour.

The first call to getSchema() is:

The second call is:

So, when accessing the first UDT, accidentally, the second UDT is initialised.

[lukaseder]

A related issue is this: #7685

[lukaseder]

This seems to be a regression introduced by #4869

[lukaseder]

... it could have been introduced by other features as well, though. The real problem is the UDT.getDataType() call, which transitively depends on an initialised Schema instance. This is not allowed. The data type must initialise itself lazily. This is definitely not an easy fix, I'm afraid.

A possible workaround is to use <generateGlobalTableReferences>false</generateGlobalTableReferences>, as this would prevent eager initialisation of all tables from the schema reference.

[lukaseder]

While it is not easy to fix the static initialisation graph, in this particular case, I might pull off a trick in the code generator and generate this:

    public Schema getSchema() {
        return S_7496.S_7496 != null ? S_7496.S_7496 : new SchemaImpl(DSL.name("s_7496"));
    }

... instead of this ...

    public Schema getSchema() {
        return S_7496.S_7496;
    }

I'm not particularly proud of this solution o_O. But it does fix the problem, for now.

[lukaseder]

Fixed for jOOQ 3.12. Backports scheduled for 3.11.8 (#8059) and 3.10.9 (#8060)

[eduramiba]

There seems to be a regression in 3.12.2 https://github.com/eduramiba/jooq-issue-7496/commit/3760840ca41e1d30af2e2267ff7db3ca453ad24b

It works fine with 3.12.1

Thanks!

[lukaseder]

@eduramiba Thanks for your message - sorry to hear that. We'll look at it ASAP early next week, and hopefully fix it for 3.12.3, which is also scheduled for next week.

[eduramiba]

@lukaseder Awesome, thank you! :)

[lukaseder]

I can reproduce the regression. Looking into this now.

[lukaseder]

Our integration tests for this case also fail. It seemsthat the failure was not reported correctly prior to releasing...

[lukaseder]

The regression has been introduced with this commit:
https://github.com/jOOQ/jOOQ/commit/39bb771e1be1a659d986c020ad651a0eeb339ed6

The underlying bug is that a UDT.getQualifiedName() call does not call UDT.getSchema(), which provides the qualification.

[lukaseder]

The regression was caused by this change:
https://github.com/jOOQ/jOOQ/commit/39bb771e1be1a659d986c020ad651a0eeb339ed6

In fact, the regression is rooted at another issue, which should be fixed in this case:
https://github.com/jOOQ/jOOQ/issues/7172

I won't reopen this issue here, but fix https://github.com/jOOQ/jOOQ/issues/7172 instead.

Thanks again for your report, and for maintaining your useful MCVE!

[lukaseder]

I can confirm https://github.com/jOOQ/jOOQ/issues/7172 fixes our integration test and your test case.