jOOQ Migration Issue from Version 3.14 to 3.19 with PostgreSQL 14 with alter table drop cascade

[samuelguzmanajala]

Expected behavior

In jOOQ version 3.14, the following SQL query executes successfully:

ALTER TABLE my_table
    DROP my_column CASCADE;

Actual behavior

Environment:

jOOQ Version: 3.19
Database: PostgreSQL 14

However, after upgrading to jOOQ 3.19, the same project fails during the Maven build process (maven clean package) with errors related to schema export:

[ERROR] Failed to execute goal org.jooq:jooq-codegen-maven:3.19.7:generate (java-generator) on project my_project: Error running jOOQ code generation tool: Error while exporting schema: SQL [alter table "my_table" drop "column_name" cascade]; Syntax error in SQL statement "alter table "my_table" drop "column_name" [*]cascade"
[ERROR] Syntax error in SQL statement "alter table "my_table" drop "column_name" cascade"; SQL statement:
[ERROR] alter table "my_table" drop "column_name" cascade [42000-224]

This error appears to be a syntax issue with the SQL command generated by the new version of jOOQ when attempting to modify the schema.

Steps to reproduce the problem

ALTER TABLE my_table
    DROP my_column CASCADE;

jOOQ Version

3.19.7

Database product and version

PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Java Version

openjdk version "21"

JDBC / R2DBC driver name and version (include name if unofficial driver)

No response

[lukaseder]

Thanks for your report.

Unfortunately, from your description, it is not clear what exactly fails, and why. While it may not be necessary to provide a fully self-contained reproducer (e.g. using our template here https://github.com/jOOQ/jOOQ-mcve), can you please provide more information about what you're doing.

E.g.:

• Please post the complete stack trace
• If this is about code generation, please provide your code generation configuration

Also, any other additional information that is required to reproduce the issue, please add it.

[samuelguzmanajala]

Fork:

Here is the link to the fork of the project where the issue can be reproduced:
GitHub Repository

Issue Description:

While executing the jOOQ code generation, I received a syntax error related to an SQL statement attempting to modify a table in the database. Here's the error log from the console:

org.jooq.exception.DataAccessException: SQL [alter table "mcve"."test" drop "cd" cascade]; Error de Sintaxis en sentencia SQL "alter table ""mcve"".""test"" drop ""cd"" [*]cascade"
Syntax error in SQL statement "alter table ""mcve"".""test"" drop ""cd"" [*]cascade"; SQL statement:
alter table "mcve"."test" drop "cd" cascade [42000-224]
    at org.jooq_3.19.7.H2.debug (Unknown Source)
    at org.jooq.impl.Tools.translate (Tools.java:3572)
    at org.jooq.impl.Tools.translate (Tools.java:3560)
    at org.jooq.impl.DefaultExecuteContext.sqlException (DefaultExecuteContext.java:827)
    at org.jooq.impl.AbstractQuery.execute (AbstractQuery.java:362)
    at org.jooq.meta.extensions.ddl.DDLDatabase.load (DDLDatabase.java:199)
    at org.jooq.meta.extensions.ddl.DDLDatabase.lambda$export$0 (DDLDatabase.java:166)
    at org.jooq.FilePattern.load0 (FilePattern.java:310)
    at org.jooq.FilePattern.load (FilePattern.java:290)
    at org.jooq.FilePattern.load (FilePattern.java:303)
    at org.jooq.FilePattern.load (FilePattern.java:259)
    at org.jooq.meta.extensions.ddl.DDLDatabase.export (DDLDatabase.java:166)
    at org.jooq.meta.extensions.AbstractInterpretingDatabase.connection (AbstractInterpretingDatabase.java:100)
    at org.jooq.meta.extensions.AbstractInterpretingDatabase.create0 (AbstractInterpretingDatabase.java:77)
    at org.jooq.meta.AbstractDatabase.create (AbstractDatabase.java:408)
    at org.jooq.meta.AbstractDatabase.create (AbstractDatabase.java:398)
    at org.jooq.meta.AbstractDatabase.setConnection (AbstractDatabase.java:376)
    at org.jooq.codegen.GenerationTool.run0 (GenerationTool.java:566)
    at org.jooq.codegen.GenerationTool.run (GenerationTool.java:246)
    at org.jooq.codegen.GenerationTool.generate (GenerationTool.java:241)
    at org.jooq.codegen.maven.Plugin.execute (Plugin.java:216)
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo (DefaultBuildPluginManager.java:126)
    at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute2 (MojoExecutor.java:328)
    at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute (MojoExecutor.java:316)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:212)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:174)
    at org.apache.maven.lifecycle.internal.MojoExecutor.access$000 (MojoExecutor.java:75)
    at org.apache.maven.lifecycle.internal.MojoExecutor$1.run (MojoExecutor.java:162)
    at org.apache.maven.plugin.DefaultMojosExecutionStrategy.execute (DefaultMojosExecutionStrategy.java:39)
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:159)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject (LifecycleModuleBuilder.java:105)
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject (LifecycleModuleBuilder.java:73)
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build (SingleThreadedBuilder.java:53)
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute (LifecycleStarter.java:118)
    at org.apache.maven.DefaultMaven.doExecute (DefaultMaven.java:261)
    at org.apache.maven.DefaultMaven.doExecute (DefaultMaven.java:173)
    at org.apache.maven.DefaultMaven.execute (DefaultMaven.java:101)
    at org.apache.maven.cli.MavenCli.execute (MavenCli.java:906)
    at org.apache.maven.cli.MavenCli.doMain (MavenCli.java:283)
    at org.apache.maven.cli.MavenCli.main (MavenCli.java:206)
    at jdk.internal.reflect.DirectMethodHandleAccessor.invoke (DirectMethodHandleAccessor.java:103)
    at java.lang.reflect.Method.invoke (Method.java:580)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced (Launcher.java:283)
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch (Launcher.java:226)
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode (Launcher.java:407)
    at org.codehaus.plexus.classworlds.launcher.Launcher.main (Launcher.java:348)

Code Generation Configuration:

Here is the relevant section of my Maven configuration for the jOOQ code generation:

<groupId>${org.jooq.groupId}</groupId>
<artifactId>jooq-codegen-maven</artifactId>
<version>${org.jooq.version}</version>
<executions>
   <execution>
       <id>generate-java</id>
       <phase>generate-sources</phase>
       <goals>
           <goal>generate</goal>
       </goals>
       <configuration>
           <generator>
               <database>
                   <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
                   <excludes>schema_version | brand_.* | module_.* | rule.* | event.*</excludes>
                   <properties>
                       <property>
                           <key>sort</key>
                           <value>flyway</value>
                       </property>
                       <property>
                           <key>scripts</key>
                           <value>${basedir}/src/main/resources/db/migration/*sql</value>
                       </property>
                       <property>
                           <key>defaultNameCase</key>
                           <value>lower</value>
                       </property>
                   </properties>
               </database>
               <target>
                   <packageName>org.jooq.mcve.java.postgres.jooq</packageName>
                   <directory>${project.build.directory}/generated-sources/jooq/</directory>
               </target>
           </generator>
       </configuration>
   </execution>
</executions>
<dependencies>
   <dependency>
       <groupId>org.jooq</groupId>
       <artifactId>jooq-meta-extensions</artifactId>
       <version>${org.jooq.version}</version>
   </dependency>
</dependencies>

Could you please help in identifying the root cause of this syntax error and suggest any possible solutions?

Thank you in advance for your assistance.

[lukaseder]

I see, so you're using the DDLDatabase. It simulates your migration against an in-memory H2 database, which doesn't support CASCADE at the moment:

• https://www.h2database.com/html/commands.html#alter_table_drop_column

The difference in behaviour may be caused by the fact that a previous jOOQ version didn't generate the unsupported keyword in H2.

This issue cannot be fixed easily for the DDLDatabase. There's a task where we aim to remove the H2 dependency, and instead, interpret the DDL ourselves:

• Add configuration in DDLDatabase, LiquibaseDatabase, JPADatabase matching Settings.interpreterDialect #7034

Alternatively, you can always use testcontainers for your code generation, for better results as it won't run into such dialect incompatibilities:

• https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/

As a short term workaround, if you can't avoid the DDLDatabase, you'll have to avoid the CASCADE keyword, instead, or you can render it invisible from jOOQ's parser:

• https://blog.jooq.org/the-jooq-parser-ignore-comment-syntax/