Empty rollback issue with sql script

General Discussion
1

I have the following changelog -

--liquibase formatted sql

--changeset erfan:1
CREATE TABLE IF NOT EXISTS product (
    id          BIGINT NOT NULL,
    name        VARCHAR(40) NOT NULL,
    quantity    INT,
    CONSTRAINT pk_product PRIMARY KEY (id)
);

CREATE SEQUENCE product_seq INCREMENT BY 1;
--rollback DROP TABLE product;
--rollback DROP SEQUENCE product_seq;


--changeset erfan:2
ALTER TABLE IF EXISTS product
    ADD COLUMN created TIMESTAMP,
    ADD COLUMN updated TIMESTAMP,
    ADD COLUMN version INT;
--rollback none

While performing rollback with gradle ./gradlew rollbackCount -PliquibaseCommandValue=1 causes the following error

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near “empty”

--rollback empty also gives the same error.

Changing the rollback statement to --rollback SELECT 'N\A' according to this discussion, the error resolves but it deletes the corresponding update entry from the databasechangelog table while keeping the product table same.

For this, when I re-run ./gradlew update command, it fails because the columns already exist. Since the record of changeset erfan:2 is removed from the databasechangelog table, the update command tries to execute the changeset erfan:2 again.

I am using

  • liquibase 4.18.0
  • Spring boot 2.7.7
  • gradle 7.6
  • liquibase gradle plugin 2.0.4
2

“empty” rollback in sql format is:

--rollback not required

But I highly recommend not using that. You should always code the rollback sql statements required to reverse your changeset.

3

--rollback not required – still deletes corresponding changeset row from databasechangelog table.

4

Yes, it has to remove the changeset record from the databasechangelog table since you are rolling-back. The “–rollback not required” just indicates that you don’t need to perform any rollback sql for your changeset sql. Assuming you are using Liquibase Community, there is no way to skip changeset when you rollback.

1 Like
5

I see. So technically there is NO EMPTY rollback feature in community version.
Since empty rollback operation will cause trouble in successive update operation, It has practically no use in such scenarios.

6

I think we have to agree on what “empty rollback” actually means. For Liquibase it means:

  • Liquibase rollbacks-back the change in the databasechangelog table.

  • Database does not need to rollback the change.

I also just noticed that what you had above is now valid empty rollback (added in 4.18):

--rollback empty

7

I think, it would have been ideal/better if empty rollback doesn’t change anything on the databasechangelog table as well.