dengzhhu653 commented on code in PR #6521: URL: https://github.com/apache/hive/pull/6521#discussion_r3629415639
########## standalone-metastore/metastore-server/src/main/sql/oracle/upgrade-4.2.0-to-4.3.0.oracle.sql: ########## @@ -3,8 +3,123 @@ SELECT 'Upgrading MetaStore schema from 4.2.0 to 4.3.0' AS Status from dual; ALTER TABLE HIVE_LOCKS ADD (HL_CATALOG VARCHAR2(128) DEFAULT 'hive' NOT NULL); ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD (MRL_CAT_NAME VARCHAR2(128) DEFAULT 'hive' NOT NULL); +-- Add surrogate primary keys for HA database replication. Oracle IDENTITY columns +-- do not backfill existing rows when added via ALTER TABLE, so rebuild each +-- populated table via a TMP_* copy/swap (same pattern as counter tables below). +-- Plan a maintenance window: each swap locks the table and scales with row count. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID NUMBER(19) GENERATED BY DEFAULT AS IDENTITY NOT NULL, + TC_TXNID NUMBER(19) NOT NULL, + TC_DATABASE VARCHAR2(128) NOT NULL, + TC_TABLE VARCHAR2(256), + TC_PARTITION VARCHAR2(767) NULL, + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID NUMBER(19), + CONSTRAINT TMP_TXN_COMPONENTS_PK PRIMARY KEY (TC_ID) +) ROWDEPENDENCIES; +INSERT INTO TMP_TXN_COMPONENTS (TC_TXNID, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID) + SELECT TC_TXNID, TC_DATABASE, TC_TABLE, TC_PARTITION, TC_OPERATION_TYPE, TC_WRITEID FROM TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS; +ALTER TABLE TMP_TXN_COMPONENTS RENAME TO TXN_COMPONENTS; +ALTER TABLE TXN_COMPONENTS ADD CONSTRAINT TXN_COMPONENTS_FK1 FOREIGN KEY (TC_TXNID) REFERENCES TXNS (TXN_ID); +CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID); Review Comment: sounds it's a valid point, please consider this -- 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]
