CODEWITHSUNDEEP
javapostgresqlspring-bootspring-data-jpaliquibase
rvit34
rvit34

Reputation: 2116

Why liquibase does always use its own sequence?

I am using liquibase as a migration tool for my current project.

The problem is that I am trying to use custom sequence for auto generation ids for entities but something wrong happens.

I've defined an entity table and custom sequence as a similar below:

<databaseChangeLog>
<changeSet id="create_orders_table" author="I am">
    <createTable tableName="ORDERS">
        <column name="id"  type="SERIAL" valueComputed="SEQ_ORDERS.NEXTVAL" valueSequenceCurrent="SEQ_ORDERS.CURRENT"  valueSequenceNext="SEQ_ORDERS.NEXTVAL" defaultValueSequenceNext="SEQ_ORDERS.NEXTVAL">
            <constraints primaryKey="true" unique="true"/>
        </column>
        <column name="number" type="VARCHAR(64)"/>
        ...
    </createTable>
</changeSet>
<changeSet id="add_sequence" author="I am">
    <createSequence sequenceName="SEQ_ORDERS" cycle="false" minValue="1" maxValue="9223372036854775807" startValue="1" incrementBy="1"/>
</changeSet>
</databaseChangeLog>

But when this migration is applied I see the following table structure in postgresql:

CREATE TABLE public.orders
(
id integer NOT NULL DEFAULT nextval('orders_id_seq'::regclass),
"number" character varying(64),
 ...
CONSTRAINT pk_orders PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);

First unpredictable thing for me here is why liquibase wrote its own orders_id_seq instead of my SEQ_ORDERS?

Next I wrote a typical JPA code and it's test using Spring:

@Entity(name = "ORDERS")
public class Order {

  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ORDERS_ID_GEN")
  @SequenceGenerator(name = "ORDERS_ID_GEN", sequenceName = "SEQ_ORDERS")
  private long id;

  private String number; 
  //... getters,setters and other stuff
}

public interface OrderRepository extends JpaRepository<Order,Long> {}

Test

@RunWith(SpringRunner.class)
@DataJpaTest
@AutoConfigureTestDatabase(replace = AutoConfigureTestDatabase.Replace.NONE)
@Rollback(value = false)
public class OrderRepositoryTest {

    @Autowired
    private TestEntityManager testEntityManager;

    @Autowired
    private OrderRepository orderRepository;


    @Test
    public void testThatIdsOfOrdersGenerateCorrectly(){


        Order orderOne = new Order();
        orderOne.setNumber("order-n-01");

        Order orderTwo = new Order();
        orderTwo.setNumber("order-n-02");

        Order orderThree = new Order();
        orderThree.setNumber("order-n-03");

        testEntityManager.persist(orderOne);
        testEntityManager.persist(orderTwo);
        testEntityManager.persist(orderThree);

        Assert.assertThat(orderRepository.findOne(new Long(1)),is(orderOne));
        Assert.assertThat(orderRepository.findOne(new Long(2)),is(orderTwo));
        Assert.assertThat(orderRepository.findOne(new Long(3)),is(orderThree));

    }

}

In test logs I see:

Hibernate: select nextval ('seq_orders')
...
java.lang.AssertionError: 
Expected: is <Order(id=50, number=order-n-01, ...>
   but: was null

After Test is finished In DB I saw three orders with id=50,51,52

First time I thought that orders_id_seq increments value on 50 each time. I was very surpised when I saw unmodified orders_id_seq after test (current value=1) but my SEQ_ORDERS was incremented on 1.

When I run the test once again I got next three orders with ids 100,101,102

Could someone explain me what the hell is going on here? And how to make liquibase&postgresql do right things?

I am using: PostgreSQL 9.6(org.postgresql.9.4.1212 JDBC Driver), Liquibase 3.5.3, Spring Boot 1.5.2

Spring Boot Test Config:

spring.jpa.hibernate.ddl-auto=none
spring.jpa.generate-ddl=false
spring.jpa.database=postgresql

spring.datasource.initialize=false
spring.datasource.username=postgres
spring.datasource.password=********
spring.datasource.url=jdbc:postgresql://localhost:5432/ORDERS_TEST
spring.datasource.driver-class-name=org.postgresql.Driver

liquibase.change-log=classpath:/db/changelog/changelog-master.xml

Upvotes: 2

Views: 10288

Answers (1)

user330315
user330315

Reputation:

If you specify serial as the data type you should not specify any default value, because Postgres will already do that for you. The sequence and orders_id_seq and the default value is created by Postgres automatically when you use serial it is not created by Liquibase.

So if you want to use your own sequence (for whatever reason), specify the column as integer and specify a defaultValueComputed using nextval(). You also need to create the sequence before you create the table.

Also the syntax for getting the next value is not sequence.nextval but nextval('sequence') in Postgres:

<databaseChangeLog>
<changeSet id="add_sequence" author="I am">
    <createSequence sequenceName="SEQ_ORDERS" cycle="false" minValue="1" maxValue="9223372036854775807" startValue="1" incrementBy="1"/>
</changeSet>

<changeSet id="create_orders_table" author="I am">
    <createTable tableName="ORDERS">
        <column name="id"  type="integer" defaultValueComputed="nextval('seq_orders')>
            <constraints primaryKey="true" unique="true"/>
        </column>
        <column name="number" type="VARCHAR(64)"/>
        ...
    </createTable>
</changeSet>
</databaseChangeLog>

Upvotes: 5

Related Questions