Flyway 4升级至9.x版本脚本执行报错及PostgreSQL兼容求助

问题概述 #

将Flyway从4.x版本升级到9.x后，执行包含PL/SQL包的迁移脚本时出现**「Unable to decrease block depth below throwing」**错误。修改脚本会触发Flyway校验和不匹配问题，尽管该问题在Flyway 6.4.1版本标注为已修复，但9.x版本中仍存在。同时升级到9.15.1和9.16版本时，即使已安装Gson依赖，仍出现Gson相关错误。

环境约束 #

• 必须使用Flyway 9.x版本以支持PostgreSQL 15
• 降级至Flyway 5.2及以下版本无上述错误，但这些版本不支持PostgreSQL 15

相关PL/SQL代码 #

CREATE OR REPLACE PACKAGE EPCIS_MAINTENANCE_PAK
AS
PROCEDURE CAPTURE_SQL
   (P_RUN_FROM             VARCHAR2,
    P_SQL_STMT             VARCHAR2,
    P_RUN_TIME             TIMESTAMP,
    P_ERROR_MSG            VARCHAR2);

PROCEDURE MAINTENANCE_LOG
   (P_TASK_NAME            VARCHAR2,
    P_TASK_STARTED         TIMESTAMP,
    P_TASK_ENDED           TIMESTAMP,
    P_RESULTS              VARCHAR2,
    P_DESCRIPTION          VARCHAR2);

PROCEDURE PARTITION_MANAGEMENT;


END EPCIS_MAINTENANCE_PAK;

CREATE OR REPLACE PACKAGE BODY EPCIS_MAINTENANCE_PAK
AS

--
-- PROCEDURE: CAPTURE_SQL
-- THIS PROCEDURE ALLOWS OTHER MAINTENANCE PROCEDURES TO LOG THE FINAL SQL THEY
-- IS TO BE RUN USING DYNAMIC SQL
--

PROCEDURE CAPTURE_SQL
   (P_RUN_FROM             VARCHAR2,
    P_SQL_STMT             VARCHAR2,
    P_RUN_TIME             TIMESTAMP,
    P_ERROR_MSG            VARCHAR2)
IS
BEGIN
    INSERT INTO CAPTURED_SQL
         (RUN_FROM, SQL_STMT, RUN_TIME, ERROR_MSG)
    VALUES
         (P_RUN_FROM, P_SQL_STMT, P_RUN_TIME, P_ERROR_MSG);
    EXCEPTION
    WHEN OTHERS THEN NULL;
END CAPTURE_SQL;

--
-- PROCEDURE: MAINTENANCE_LOG
-- THIS PROCEDURE SIMPLY ALLOWS OTHER MAINTENANCE JOBS TO LOG THEIR SUCCESS OR
-- FAILURE
--

PROCEDURE MAINTENANCE_LOG
   (P_TASK_NAME            VARCHAR2,
    P_TASK_STARTED         TIMESTAMP,
    P_TASK_ENDED           TIMESTAMP,
    P_RESULTS              VARCHAR2,
    P_DESCRIPTION          VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

   INSERT INTO MAINTENANCE_LOG (TASK_NAME, TASK_STARTED, TASK_ENDED, RESULTS, DESCRIPTION)
   VALUES (P_TASK_NAME, P_TASK_STARTED, P_TASK_ENDED, P_RESULTS, P_DESCRIPTION);
   COMMIT;

EXCEPTION
   WHEN OTHERS THEN NULL;
END MAINTENANCE_LOG;

--
-- PROCEDURE: PARTITION_MANAGEMENT
-- THIS PARTITION_MANAGEMENT IS CALLED BY THE NIGHTLY JOB AND IT WILL MAINTAIN
-- THE PARTITION TABLES BY ADDING NEW PARTITIONS (BY SPLITTING THE MAXVALUE
-- PARTITION) SO THAT FUTURE PARTITIONS ARE CREATED AHEAD OF TIME BASED ON
-- SETTINGS IN THE PARTITION_MANAGEMENT TABLE
--
PROCEDURE PARTITION_MANAGEMENT
IS
   V_STARTED      TIMESTAMP := SYSTIMESTAMP;
   V_CNT          NUMBER;
   V_MAX          DATE;
   V_MAXVALPART   VARCHAR2(100);
   V_MAX_REQUIRED DATE;
   V_STMT         VARCHAR2(32767);
BEGIN

    DBMS_OUTPUT.PUT_LINE('PARTITION_MANAGEMENT - Begin');
   FOR R IN (SELECT *
               FROM PARTITION_MANAGEMENT
              WHERE PARTITIONED_YN = 'Y') LOOP

    DBMS_OUTPUT.PUT_LINE('PARTITION_MANAGEMENT.TABLE_NAME: '||R.TABLE_NAME);

      SELECT NVL(MAX(DECODE(INSTR(PARTITION_NAME, 'MAXVALUE'), 0,
                            TO_DATE(RPAD(SUBSTR(PARTITION_NAME,
                                    DECODE(R.PARTITION_SIZE_MDY, 'D', -8
                                                               , 'M', -6)),
                                         8, '01'), 'YYYYMMDD'))),
                 DECODE(R.PARTITION_SIZE_MDY, 'D', TRUNC(SYSDATE),
                                              'M', TRUNC(SYSDATE, 'MM'))),
             MAX(DECODE(INSTR(PARTITION_NAME, 'MAXVALUE'), 0, NULL, PARTITION_NAME))
        INTO V_MAX, V_MAXVALPART
        FROM USER_TAB_PARTITIONS
       WHERE TABLE_NAME = R.TABLE_NAME;

    DBMS_OUTPUT.PUT_LINE('V_MAX: '||TO_CHAR(V_MAX, 'YYYY/MM/DD HH24:MI:SS'));
    DBMS_OUTPUT.PUT_LINE('V_MAXVALPART: '||V_MAXVALPART);
    DBMS_OUTPUT.PUT_LINE('PARTITION_SIZE_MDY: '||R.PARTITION_SIZE_MDY);


      IF V_MAXVALPART IS NULL THEN
         EXIT;
      ELSIF R.PARTITION_SIZE_MDY = 'D' THEN
         V_MAX_REQUIRED := TRUNC(SYSDATE) + R.PARTITIONS_TO_PREBUILD;
      ELSIF R.PARTITION_SIZE_MDY = 'M' THEN
         V_MAX_REQUIRED := ADD_MONTHS(TRUNC(SYSDATE, 'MM'), R.PARTITIONS_TO_PREBUILD);
      END IF;
      --SPLITS
      DBMS_OUTPUT.PUT_LINE('V_MAX_REQUIRED: '||TO_CHAR(V_MAX_REQUIRED, 'YYYY/MM/DD HH24:MI:SS'));
      WHILE V_MAX < V_MAX_REQUIRED LOOP
        DBMS_OUTPUT.PUT_LINE('LOOP for '||TO_CHAR(V_MAX, 'YYYY/MM/DD HH24:MI:SS'));

         IF R.PARTITION_SIZE_MDY = 'D' THEN
            V_MAX := V_MAX + 1;
            V_STMT := 'ALTER TABLE ' || R.TABLE_NAME ||
                      ' SPLIT PARTITION ' || V_MAXVALPART ||
                      ' AT (TO_DATE(''' || TO_CHAR(V_MAX + 1, 'YYYYMMDD') ||
                      ''' , ''YYYYMMDD'')) INTO (PARTITION P_' || R.PARTITION_NAME_STRING || '_' ||
                      TO_CHAR(V_MAX, 'YYYYMMDD') || ', PARTITION ' || V_MAXVALPART || ')';

            CAPTURE_SQL
               (P_RUN_FROM  => 'PARTITION_MANAGEMENT',
                P_SQL_STMT  => V_STMT,
                P_RUN_TIME  => SYSTIMESTAMP,
                P_ERROR_MSG => 'N/A');

            EXECUTE IMMEDIATE V_STMT;
         ELSIF R.PARTITION_SIZE_MDY = 'M' THEN
            V_MAX := ADD_MONTHS(V_MAX, 1);
            V_STMT := 'ALTER TABLE ' || R.TABLE_NAME ||
                      ' SPLIT PARTITION ' || V_MAXVALPART ||
                      ' AT (TO_DATE(''' || TO_CHAR(ADD_MONTHS(V_MAX, 1), 'YYYYMM') ||
                      ''' , ''YYYYMM'')) INTO (PARTITION P_' || R.PARTITION_NAME_STRING || '_' ||
                      TO_CHAR(V_MAX, 'YYYYMM') || ', PARTITION ' || V_MAXVALPART || ')';

            CAPTURE_SQL
               (P_RUN_FROM  => 'PARTITION_MANAGEMENT',
                P_SQL_STMT  => V_STMT,
                P_RUN_TIME  => SYSTIMESTAMP,
                P_ERROR_MSG => 'N/A');

            EXECUTE IMMEDIATE V_STMT;
            V_CNT := V_CNT + 1;
         END IF;

         UPDATE PARTITION_MANAGEMENT
            SET LAST_DDL = SYSDATE
          WHERE TABLE_NAME = R.TABLE_NAME;
         COMMIT;
      END LOOP;--SPLITS
   END LOOP; --PARTITION_MANAGEMENT

   MAINTENANCE_LOG
      (P_TASK_NAME    => 'PARTITION_MANAGEMENT',
       P_TASK_STARTED => V_STARTED,
       P_TASK_ENDED   => SYSTIMESTAMP,
       P_RESULTS      => 'SUCCESSFUL',
       P_DESCRIPTION  => 'ADDED ' || V_CNT || 'PARTITIONS');
    DBMS_OUTPUT.PUT_LINE('PARTITION_MANAGEMENT - End');

EXCEPTION
   WHEN OTHERS THEN
      MAINTENANCE_LOG
         (P_TASK_NAME    => 'PARTITION_MANAGEMENT',
          P_TASK_STARTED => V_STARTED,
          P_TASK_ENDED   => SYSTIMESTAMP,
          P_RESULTS      => 'FAILED',
          P_DESCRIPTION  => SQLERRM);

END PARTITION_MANAGEMENT;

END EPCIS_MAINTENANCE_PAK;

-- SHOW ERRORS

call EPCIS_MAINTENANCE_PAK.PARTITION_MANAGEMENT();

解决建议 #

1. 拆分迁移脚本 #

将PL/SQL包定义与调用逻辑拆分为两个独立的迁移脚本：

• 第一个脚本仅包含CREATE OR REPLACE PACKAGE和CREATE OR REPLACE PACKAGE BODY代码
• 第二个脚本仅包含call EPCIS_MAINTENANCE_PAK.PARTITION_MANAGEMENT();调用语句
  Flyway对复杂嵌套PL/SQL块的解析存在兼容性问题，拆分后可降低解析复杂度，避免块深度相关错误。

2. 规避校验和冲突（临时方案） #

若无法修改历史脚本，可通过以下方式跳过校验和检查：

• 设置flyway.baselineOnMigrate=true，以当前数据库状态为基线，跳过历史迁移脚本的校验
• 调整flyway.ignoreMigrationPatterns配置，忽略特定脚本的校验逻辑
  注意：此方案会降低Flyway版本控制的完整性，仅作为临时应急手段。

3. 解决Gson版本冲突问题 #

Flyway 9.x内部依赖特定版本的Gson，手动添加的Gson可能导致版本冲突：

• 使用Flyway官方提供的完整发行包，避免手动引入Gson依赖
• 清理项目依赖中的重复Gson包，确保类路径中仅存在Flyway所需版本的Gson

4. 简化PL/SQL代码嵌套 #

适当拆分PARTITION_MANAGEMENT过程中的嵌套逻辑，减少代码块的层级深度，例如将循环内的部分逻辑提取为独立的内部过程，可能规避Flyway解析器的块深度计算错误。

内容的提问来源于stack exchange，提问作者Vivek Srivastava