Why does Jooq code-generation break with PostGIS?

Question

Context - I am trying out Postgres' Geographic Information System extension PostGis that enables stories latitude and longitudes as Point and operations on it.

If I understand correctly then I need to add a custom converter that can convert the point between JOOQ and PostGis and add it to the gradle file.

Problem - When I generate the jooq-code, few files are generated incorrectly and have the fields defined twice which fail compilation. These are:

<configured-generation-dir>/tables/StValuecount.java
<configured-generation-dir>/tables/records/StValuecountRecord.java
<configured-generation-dir>/tables/records/StValuepercentRecord.java
<configured-generation-dir>/tables/_StValuecount.java
<configured-generation-dir>/tables/records/_StValuecountRecord.java
<configured-generation-dir>/tables/_StHistogram.java
<configured-generation-dir>/tables/records/_StHistogramRecord.java
<configured-generation-dir>/tables/_StQuantile.java

Gradle config =>

jooq{
    myAwesomeApp(sourceSets.main){
        logging = 'WARN'
        jdbc {
            driver = 'org.postgresql.Driver'
            url = db_url
            user = db_user
            password = db_password
        }
        generator {
            name = 'org.jooq.codegen.DefaultGenerator'
            strategy {
                name = 'org.jooq.codegen.DefaultGeneratorStrategy'
            }
            database {
                name = 'org.jooq.meta.postgres.PostgresDatabase'
                inputSchema = 'public'
                forcedTypes {
                    forcedType {
                        userType = 'org.postgis.Point'
                        converter = 'com.example.JooqBreaksWithPostGis.jooq.converters.PostgresPointJooqConverter'
                        expression = '.*\\.point'
                        types = '.*'
                    }
                }
            }
            generate {
                routines = false
                relations = true
                deprecated = false
                records = true
                immutablePojos = false
                fluentSetters = true
            }
            target {
                packageName = 'jooq.fancy.app'
                directory = 'src/main/java/generated'
            }
        }
    }
}

What am I doing wrong?

---

I have also created a minimal project where I have reproduced the problem in case someone wants to quickly try it.

Steps to reproduce

• Checkout project

git clone [email protected]:raj-saxena/JooqBreaksWithPostGis.git

• Go to the project directory and start postgis docker container with

docker-compose up

• Similarly, to remove postgis docker container run

docker-compose down 

• Run migrations that add a simple City table containing Point type with

./gradlew flywayMigrate

• I have added few rows in a second migration to verify if the DB structure was working. Details to connect to Postgres instance in the build.gradle file.

• Generate jooq files with

./gradlew generateMyAwesomeAppJooqSchemaSource

• Verify that the files are generated in the configured src/main/java/generated directory.

• Verify that the files mentioned above fail to compile.

---

Taking Lukas' advice, I added the exclude configuration to the jooq config as below:

            database {
                name = 'org.jooq.meta.postgres.PostgresDatabase'
                ...
                excludes = '.*ST_ValueCount' +
                        '|.*St_Valuepercent' +
                        '|.*St_Histogram' +
                        '|.*St_Quantile' +
                        '|.*St_Approxhistogram' +
                        '|.*St_PixelOfValue' +
                        '|.*St_Approxquantile' +
                        '|.*ST_Tile'
            }

This allowed the code to compile.

Answer 1

This sounds a lot like https://github.com/jOOQ/jOOQ/issues/4055. jOOQ 3.11 currently cannot handle overloaded table valued functions in any RDBMS that supports table valued functions. Your best option here is to exclude all the affected functions from the code generation, using <excludes>:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-includes-excludes/