javapostgresqlspring-bootspring-data-jpadatabase-partitioning

Problem involving partitioned table + Spring Boot: GenerationTarget encountered exception accepting command : Error executing DDL "create table


I'm working with partitioned tables using Postgres and Spring Boot. I managed to create the partition, and in the tests I performed, the GET and POST methods are working as intended.

However, I need help resolving the following error that occurs when I start the application:

GenerationTarget encountered exception accepting command : Error executing DDL "create table example (date_cd date not null, id int4 not null, name varchar(255), primary key (date_cd, id))" via JDBC Statement org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table example (date_cd date not null, id int4 not null, name varchar(255), primary key (date_cd, id))" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]

The step-by-step of what I did before the error appeared:

I created the main Model, which will reference the partitioned table:

@Entity
@Table(name = "example")
@IdClass(ExampleId.class)
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Example {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "example_id_seq")
    @SequenceGenerator(name = "example_id_seq", sequenceName = "example_id_seq", initialValue = 1, allocationSize = 1)
    private int id;

    @Id
    private LocalDate date_cd;

    private String name;

}

I created the composite ID class.

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class ExampleId implements Serializable {

    private static final long serialVersionUID = 1L;

    private int id;

    private LocalDate date_cd;

}

And then I started the application, everything ran normally.

After that I paused the application and partitioned the 'example' table following the Postgres documentation: https://www.postgresql.org/docs/current/ddl-partitioning.html Thread: 5.11.2.1. Example

And my table looks like this:

CREATE TABLE IF NOT EXISTS public.example
(
    date_cd date NOT NULL,
    id integer NOT NULL,
    name character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT example_pkey PRIMARY KEY (date_cd, id)
) PARTITION BY RANGE (date_cd);)

So I started the application again, and the following error was printed in the Spring Boot log:

WARN 20616 --- [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl: 
GenerationTarget encountered exception accepting command : Error executing DDL "create table example 
(date_cd date not null, id int4 not null, name varchar(255), primary key (date_cd, id))" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table example 
(date_cd date not null, id int4 not null, name varchar(255), primary key (date_cd , id))" via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:562)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final] at
org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:507)
~[hibernate-core-5.4.32.Final.jar:5.4.32.Final]

My application.properties looks like this:

spring.datasource.url=jdbc:postgresql://localhost:5432/example
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.defer-datasource-initialization=true
spring.datasource.driver-class-name=org.postgresql.Driver

I know that changing 'spring.jpa.hibernate.ddl-auto' to 'none' would solve the problem, however it's not the solution I want as I don't want to manually manage all the database changes.


Solution

  • I managed to solve it by updating Spring Boot to the latest version and adding the following dependency in pom.xml:

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>6.2.3.Final</version>
    </dependency>