liquibase无法在分区表上创建索引和唯一约束

Question

Liquibase文件如下：

数据库更改日志：

  - changeSet:
      id: 1
      author: roran
      changes:
        - createTable:
            tableName: account_balance
            columns:
              - column:
                  name: id
                  type: bigint
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: account_id
                  type: bigint
                  constraints:
                    nullable: false
              - column:
                  name: balance_date
                  type: date
                  constraints:
                    primaryKey: true
              - column:
                  name: create_date
                  type: timestamptz
              - column:
                  name: created_by
                  type: varchar(100)
              - column:
                  name: update_date
                  type: timestamptz
              - column:
                  name: updated_by
                  type: varchar(100)
        - modifySql:
            applyToRollback: false
            dbms: postgresql
            append:
              value: PARTITION BY RANGE (balance_date);
        - addUniqueConstraint:
            columnNames:  balance_date, account_id
            tableName:  account_balance
        - createIndex:
            columns:
              - column:
                  name:  account_id
            indexName:  account_id_idx
            tableName:  account_balance

我在启动spring boot应用程序时添加唯一约束时遇到以下错误

迁移更改集失败 /db/changelog/changes/1.create-account-balance-table.yml::1::roran：原因：liquibase.exception.数据库异常： ERROR：语法错误在或接近PARTITION位置： 93[失败SQL：（0）ALTER TABLEpublic.account_balanceADD UniQUE（balance_date，account_id）PARTITION BY RANGE（balance_date）；]\tatorg.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean（AbstractAutowireCapableBeanFactory.java:1794）\tatorg.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean（AbstractAutowireCapableBeanFactory.java:594）\tatorg.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean（AbstractAutowireCapableBeanFactory.java:516）\

同样，我在启动Spring启动应用程序时添加索引时出错。

液化。例外MigrationFailedException：更改集db/changelog/changes/1的迁移失败。创建账户余额表。罗兰：原因：液化。例外数据库异常：错误：分区位置处或附近的语法错误：[失败的SQL：（0）在public上创建索引account\u id\u idx。account\u balance\u p0（account\u id）按范围（balance\u date）分区；创建表public.account\u balance\u p0 account\u balance的分区，用于从（'2020-10-01 00:00'）到（'2020-11-01 00:00'）；]\tat液化酶。变更日志。变更集。在liquibase中执行（ChangeSet.java:646）。变更日志。来访者UpdateVisitor。访问（UpdateVisitor.java:53）\t liquibase。变更日志。变更计算器。在liquibase中运行（ChangeLogIterator.java:83）。液化。更新（Liquibase.java:202）\t

同样工作正常，如果我通过liquibase创建表，然后以编程方式创建索引或添加约束，如下所示

CREATE INDEX account_id_idx ON public.account_balance(account_id);

我在liquibase中为分区表使用yaml的方式是否有任何错误？

我正在使用postgresql 11版本和liquibase with spring boot 2.3.3。释放

Answer 1

您的ChangeSet的修改Sql类型也应用于您的addUniqueConstraint和createIndex类型。生成的sql的异常是由于将PARTITION BY RANGE添加到Index和UniqueConstraint创建中。

只需将其分为两部分-一个变更集与表创建，一个变更集与索引和唯一约束创建：

  - changeSet:
      id: 1
      author: roran
      changes:
        - createTable:
            tableName: account_balance
            columns:
              ...
        - modifySql:
            applyToRollback: false
            dbms: postgresql
            append:
              value: PARTITION BY RANGE (balance_date);

和

  - changeSet:
      id: 2
      author: roran
      changes:
        - addUniqueConstraint:
            columnNames:  balance_date, account_id
            tableName:  account_balance
        - createIndex:
            columns:
              column:
                name:  account_id
            indexName:  account_id_idx
            tableName:  account_balance