Copilot commented on code in PR #6521: URL: https://github.com/apache/hive/pull/6521#discussion_r3376939819
########## standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql: ########## @@ -5,6 +5,46 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD (MRL_CAT_NAME VARCHAR2(128) DEFAUL CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE, MH_TABLE, MH_WRITEID); +ALTER TABLE TXN_COMPONENTS ADD (TC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE COMPLETED_TXN_COMPONENTS ADD (CTC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE COMPACTION_METRICS_CACHE ADD (CMC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE WRITE_SET ADD (WS_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); + +DROP INDEX TBL_TO_TXN_ID_IDX; +ALTER TABLE TXN_TO_WRITE_ID ADD PRIMARY KEY (T2W_DATABASE, T2W_TABLE, T2W_TXNID); + +DROP INDEX NEXT_WRITE_ID_IDX; +ALTER TABLE NEXT_WRITE_ID ADD PRIMARY KEY (NWI_DATABASE, NWI_TABLE); + +ALTER TABLE MIN_HISTORY_WRITE_ID ADD (MH_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); Review Comment: Same issue as above: adding MH_ID as an IDENTITY PRIMARY KEY via ALTER TABLE will not populate existing MIN_HISTORY_WRITE_ID rows on Oracle, so the PK/NOT NULL constraint can fail during upgrade on non-empty metastores. Recommend migrating MIN_HISTORY_WRITE_ID with a TMP table + INSERT ... SELECT (omitting MH_ID) or another approach that guarantees non-null unique values for existing rows before adding the PK. ########## standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql: ########## @@ -5,6 +5,46 @@ ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD (MRL_CAT_NAME VARCHAR2(128) DEFAUL CREATE INDEX MIN_HISTORY_WRITE_ID_IDX ON MIN_HISTORY_WRITE_ID (MH_DATABASE, MH_TABLE, MH_WRITEID); +ALTER TABLE TXN_COMPONENTS ADD (TC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE COMPLETED_TXN_COMPONENTS ADD (CTC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE COMPACTION_METRICS_CACHE ADD (CMC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); +ALTER TABLE WRITE_SET ADD (WS_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY); Review Comment: On Oracle, adding an IDENTITY column does not backfill values for existing rows. Since this statement also declares the new column as PRIMARY KEY (implicitly NOT NULL), the upgrade will fail with ORA-01449/ORA-02296 if TXN_COMPONENTS / COMPLETED_TXN_COMPONENTS / COMPACTION_METRICS_CACHE / WRITE_SET already contain rows (which is expected on real metastores). Consider using the same migration pattern already used later in this script (create TMP_* table with identity PK, INSERT ... SELECT without the id column to generate ids, then swap tables), or add the column nullable, populate it for existing rows, then add the PK constraint and a default/sequence for future inserts. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
