时间戳

首页 > 解决方案 > liquibase 无法在分区表上创建索引和唯一约束

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

问题描述

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.DatabaseException：错误：PARTITION 位置或附近的语法错误：93 [ SQL 失败：(0) ALTER TABLE public.account_balance ADD UNIQUE (balance_date, account_id)PARTITION BY RANGE (balance_date);]\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1794)\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)\tat org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)\

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

liquibase.exception.MigrationFailedException：更改集 db/changelog/changes/1.create-account-balance-table.yml::1::roran 迁移失败：原因：liquibase.exception.DatabaseException：错误：在或附近出现语法错误PARTITION位置：[失败的SQL：（0）在public.account_balance_p0（account_id）PARTITION BY RANGE（balance_date）上创建索引account_id_idx；CREATE TABLE public.account_balance_p0 PARTITION OF account_balance FOR VALUES FROM ('2020-10-01 00:00:00') TO ('2020-11-01 00:00:00');]\tat liquibase.changelog.ChangeSet。执行(ChangeSet.java:646)\tat liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)\tat liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)\tat liquibase.Liquibase.update( Liquibase.java:202)\tat

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

CREATE INDEX account_id_idx ON public.account_balance(account_id);

我在 liquibase 中使用 yaml 进行分区表的方式有什么错误吗？

我正在使用带有 spring boot 2.3.3.RELEASE 的 postgresql 11 版本和 liquibase

标签： postgresqldockerliquibase

解决方案

您的modifySql类型ChangeSet也适用于您addUniqueConstraint和createIndex类型。生成sql的异常是由于添加PARTITION BY RANGE了IndexandUniqueConstraint创建。

只需将其分成两部分 - 一个带有表创建的 ChangeSet 和一个带有IndexandUniqueConstraint创建的 ChangeSet：

  - 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

推荐阅读