Flyway - auto increment id not working with test data in PostgreSQL-CodePudding

Before I added Flyway to my project, I could run POST request and the new user was created successfully with ID = 1, next one ID = 2 etc.

Then I added Flyway to create tables and insert some test data by V1_init.sql:

create table "user"(
  id int8 not null,
  username varchar(255),
);
insert into "user" values (1, 'user1');
insert into "user" values (2, 'user2');
insert into "user" values (3, 'user3');

Table is created. Users are inserted.

Trying to run POST request -> error 500

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "organisation_pkey" Key (id)=(1) already exists.

So my app should add new user with ID=4 but it looks like it can't recognize that there are 3 users already added.

I'm using GenericEntity:

@Getter
@Setter
@MappedSuperclass
public abstract class GenericEntity<ID extends Serializable> implements Serializable {

    @Id
    @GeneratedValue
    protected ID id;
}

application.properties:

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/my-app
spring.datasource.username=user
spring.datasource.password=user
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.format_sql=true

I tried to use all strategies @GeneratedValue, changing spring.jpa.hibernate.ddl-auto, adding users in init.sql without id (not working)

but still no positive effects. Any ideas what could be wrong?

CodePudding user response：

You seem to have only a half understanding of what you're doing...

I tried to use all strategies @GeneratedValue

You don't need to randomly try strategies, you need to pick the one that matches your current database implementation.

changing spring.jpa.hibernate.ddl-auto

This is dangerous but I'd recommend setting that to none, given that you are using flyway.

adding users in init.sql without id (not working)

This will only work if you instruct postgresql how it's supposed to generate ids (which is easiest through a sequence).

First off, JPA's @GeneratedValue is capable of ensuring that values are generated when it is responsible for creating rows. It does not and can not know about your flyway scripts where you bypass JPA to insert rows manually.

Secondly, JPA's @GeneratedValue is configurable through a strategy. The strategy you choose will influence how keys are generated. There are only a few options: TABLE, SEQUENCE, IDENTITY and AUTO. Since you did not explicitly specify a strategy, you are currently using the default, which is AUTO. This is not recommended because it is not explicit, and now it's hard to say what your code is doing.

Under the TABLE and SEQUENCE strategies, JPA will do an interaction with the database in order to generate an ID value. In those cases, JPA is responsible for generating the value, though it will rely on the database to do so. Unsurprisingly, the former will use a table (this is rare, btw) and the latter will use a sequence (common). With IDENTITY, the responsibility is delegated to the database entirely and JPA will not attempt to generate a key. This is great for databases that have their own auto-increment mechanism.

Postgres does not really have an auto-increment system but it has some nice syntactic sugar that nearly makes it work like an auto-increment system: the serial "datatype". If you specify the datatype of a column as "serial", it will in fact be translated to datatype int, but postgresql will also create a sequence and tie the default value of the id column to the sequence's next value generator.

Since you're not using this, JPA must either be using a sequence or a table generator. (Even if you'd set the strategy to IDENTITY, you'd need to change your flyway script to use the serial datatype, too). Since your DDL setting is set to "update", Hibernate will have generated a table or sequence behind your back. You should check your database with something like pgAdmin to verify which it is, but I'd put my money on a sequence. Because you haven't specified a @SequenceGenerator, a default will be used which, AFAIK, will start from 1.

When JPA tries to insert a new row, it will call its sequence to generate an ID value (as you instructed it to do). It will get the next value of the sequence, which will be 1, which will conflict with the ids you manually entered in flyway.

My recommended solution would be to:

redefine your postgresql data type from int8 to "serial" (which is actually int a sequence default value that ties the id field to the sequence)
explicitly set the generator strategy to IDENTITY on the JPA side
update your flyway scripts to insert users without explicit id mapping (this will ensure that the test data advance the sequence, so that when JPA uses that same sequence later, it will not generate a conflicting id)

I'd say there are alternative solutions, but other than using the TABLE strategy or generating keys in memory (both things which you should avoid), there isn't really a viable alternative because it will boil down to using a sequence anyway. I suppose it's possible to manually specify the sequence, forego the default value on the id field, call the sequence manually in your insert statements, and map the sequence explicitly in JPA... but I don't see why you'd make things hard on yourself.