CteInsertStrategy can only be used with Dialects that support CTE that can take UPDATE or DELETE statements as well

Question

Hibernate 6.0.1 with PostgreSQL JDBC driver 42.3.5 causes the following exception:

java.lang.UnsupportedOperationException:
CteInsertStrategy can only be used with Dialects that support CTE that can take UPDATE or DELETE statements as well
at org.hibernate.query.sqm.mutation.internal.cte.CteInsertStrategy.<init>(CteInsertStrategy.java:123)
at org.hibernate.query.sqm.mutation.internal.cte.CteInsertStrategy.<init>(CteInsertStrategy.java:107)
at org.hibernate.dialect.PostgreSQLDialect.getFallbackSqmInsertStrategy(PostgreSQLDialect.java:704)
...

What's wrong and how can I fix the issue?

MyEntity.java

import jakarta.persistence.*;

@Entity
@Table(name = "my_entity")
public class MyEntity {

    private Long id;

    @Id
    @SequenceGenerator(name = "id_sequence", sequenceName = "my_id_sequence")
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "id_sequence")
    public Long getId() {
        return this.id;
    }
    public void setId(Long id) {
        this.id = id;
    }

}

MyTest.java

import static org.junit.Assert.assertNotNull;

import org.hibernate.*;
import org.hibernate.cfg.*;
import org.junit.*;

public class MyTest {

    private static Configuration configuration;
    private static SessionFactory sessionFactory;

    @BeforeClass
    public static void setUpBeforeClass() throws Exception {
        configuration = new Configuration().configure();
        sessionFactory = configuration.buildSessionFactory();
    }

    @AfterClass
    public static void tearDownAfterClass() throws Exception {
        sessionFactory.close();
    }

    private Session session;

    @Before
    public void setUp() throws Exception {
        session = sessionFactory.openSession();
    }

    @After
    public void tearDown() throws Exception {
        session.close();
    }

    @Test
    public void test() {
        Transaction transaction = session.beginTransaction();
        MyEntity entity = new MyEntity();
        session.persist(entity);
        assertNotNull(entity.getId());
        transaction.commit();
    }

}

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration SYSTEM "classpath://org/hibernate/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>  
    <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/mydb</property>
    <property name="hbm2ddl.auto">update</property>
    <property name="hibernate.connection.username">postgres</property>
    <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
    <property name="hibernate.c3p0.min_size">1</property>
    <property name="hibernate.c3p0.max_size">30</property>
    <property name="hibernate.c3p0.timeout">120</property>
    <property name="hibernate.c3p0.max_statements">100</property>
    <mapping class="haba713.MyEntity" />
  </session-factory>
</hibernate-configuration>

build.gradle

plugins {
    id 'java-library'
}

repositories {
    mavenCentral()
}

ext {
    hibernateVersion = '6.0.1.Final'
}

dependencies {
    implementation 'org.postgresql:postgresql:42.3.5'
    implementation 'org.hibernate.orm:hibernate-c3p0:' + hibernateVersion
    implementation 'org.hibernate.orm:hibernate-core:' + hibernateVersion
    testImplementation 'junit:junit:4.13.2'
}

See the full source code here.

Answer 1

The use_jdbc_metadata_defaults configuration property must be true for Hibernate to detect the correct version of the PostgreSQL dialect.

Removing this line

<property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>

from hibernate.cfg.xml resolves the issue.

(Thanks to Christian at Hibernate Zulip channel for sorting this out.)