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.
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>