Copilot commented on code in PR #6521: URL: https://github.com/apache/hive/pull/6521#discussion_r3411850832
########## standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql: ########## @@ -1,7 +1,94 @@ ALTER TABLE HIVE_LOCKS ADD COLUMN HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding IDENTITY primary +-- keys via ALTER TABLE rewrites large Derby tables and holds exclusive locks for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for non-trivial embedded metastores; duration scales with +-- row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint +); +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; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; Review Comment: Derby does not support renaming multiple tables in a single `RENAME TABLE ... , ...` statement. This will fail during upgrade; use two separate `RENAME TABLE` statements (one for the old->*_OLD rename and one for TMP_* -> original name). ########## standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql: ########## @@ -1,7 +1,94 @@ ALTER TABLE HIVE_LOCKS ADD COLUMN HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding IDENTITY primary +-- keys via ALTER TABLE rewrites large Derby tables and holds exclusive locks for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for non-trivial embedded metastores; duration scales with +-- row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint +); +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; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS_OLD; +CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID); + +CREATE TABLE TMP_COMPLETED_TXN_COMPONENTS ( + CTC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CTC_TXNID bigint NOT NULL, + CTC_DATABASE varchar(128) NOT NULL, + CTC_TABLE varchar(256), + CTC_PARTITION varchar(767), + CTC_TIMESTAMP timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, + CTC_WRITEID bigint, + CTC_UPDATE_DELETE char(1) NOT NULL +); +INSERT INTO TMP_COMPLETED_TXN_COMPONENTS (CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE) + SELECT CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE FROM COMPLETED_TXN_COMPONENTS; +RENAME TABLE COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS_OLD, TMP_COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS; Review Comment: Derby does not support renaming multiple tables in a single `RENAME TABLE ... , ...` statement. Split this into two separate rename statements to avoid upgrade failure. ########## standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql: ########## @@ -1,7 +1,94 @@ ALTER TABLE HIVE_LOCKS ADD COLUMN HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding IDENTITY primary +-- keys via ALTER TABLE rewrites large Derby tables and holds exclusive locks for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for non-trivial embedded metastores; duration scales with +-- row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint +); +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; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS_OLD; +CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID); + +CREATE TABLE TMP_COMPLETED_TXN_COMPONENTS ( + CTC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CTC_TXNID bigint NOT NULL, + CTC_DATABASE varchar(128) NOT NULL, + CTC_TABLE varchar(256), + CTC_PARTITION varchar(767), + CTC_TIMESTAMP timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, + CTC_WRITEID bigint, + CTC_UPDATE_DELETE char(1) NOT NULL +); +INSERT INTO TMP_COMPLETED_TXN_COMPONENTS (CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE) + SELECT CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE FROM COMPLETED_TXN_COMPONENTS; +RENAME TABLE COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS_OLD, TMP_COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS; +DROP TABLE COMPLETED_TXN_COMPONENTS_OLD; +CREATE INDEX COMPLETED_TXN_COMPONENTS_IDX ON COMPLETED_TXN_COMPONENTS (CTC_DATABASE, CTC_TABLE, CTC_PARTITION); + +CREATE TABLE TMP_COMPACTION_METRICS_CACHE ( + CMC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CMC_DATABASE varchar(128) NOT NULL, + CMC_TABLE varchar(256) NOT NULL, + CMC_PARTITION varchar(767), + CMC_METRIC_TYPE varchar(128) NOT NULL, + CMC_METRIC_VALUE integer NOT NULL, + CMC_VERSION integer NOT NULL +); +INSERT INTO TMP_COMPACTION_METRICS_CACHE (CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION) + SELECT CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION FROM COMPACTION_METRICS_CACHE; +RENAME TABLE COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE_OLD, TMP_COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE; +DROP TABLE COMPACTION_METRICS_CACHE_OLD; + +CREATE TABLE TMP_WRITE_SET ( + WS_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + WS_DATABASE varchar(128) NOT NULL, + WS_TABLE varchar(256) NOT NULL, + WS_PARTITION varchar(767), + WS_TXNID bigint NOT NULL, + WS_COMMIT_ID bigint NOT NULL, + WS_OPERATION_TYPE char(1) NOT NULL +); +INSERT INTO TMP_WRITE_SET (WS_DATABASE, WS_TABLE, WS_PARTITION, WS_TXNID, WS_COMMIT_ID, WS_OPERATION_TYPE) + SELECT WS_DATABASE, WS_TABLE, WS_PARTITION, WS_TXNID, WS_COMMIT_ID, WS_OPERATION_TYPE FROM WRITE_SET; +RENAME TABLE WRITE_SET TO WRITE_SET_OLD, TMP_WRITE_SET TO WRITE_SET; Review Comment: Derby does not support renaming multiple tables in a single `RENAME TABLE ... , ...` statement. Split into two `RENAME TABLE` statements to prevent upgrade failure. ########## standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql: ########## @@ -1,7 +1,94 @@ ALTER TABLE HIVE_LOCKS ADD COLUMN HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding IDENTITY primary +-- keys via ALTER TABLE rewrites large Derby tables and holds exclusive locks for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for non-trivial embedded metastores; duration scales with +-- row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint +); +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; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS_OLD; +CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID); + +CREATE TABLE TMP_COMPLETED_TXN_COMPONENTS ( + CTC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CTC_TXNID bigint NOT NULL, + CTC_DATABASE varchar(128) NOT NULL, + CTC_TABLE varchar(256), + CTC_PARTITION varchar(767), + CTC_TIMESTAMP timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, + CTC_WRITEID bigint, + CTC_UPDATE_DELETE char(1) NOT NULL +); +INSERT INTO TMP_COMPLETED_TXN_COMPONENTS (CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE) + SELECT CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE FROM COMPLETED_TXN_COMPONENTS; +RENAME TABLE COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS_OLD, TMP_COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS; +DROP TABLE COMPLETED_TXN_COMPONENTS_OLD; +CREATE INDEX COMPLETED_TXN_COMPONENTS_IDX ON COMPLETED_TXN_COMPONENTS (CTC_DATABASE, CTC_TABLE, CTC_PARTITION); + +CREATE TABLE TMP_COMPACTION_METRICS_CACHE ( + CMC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CMC_DATABASE varchar(128) NOT NULL, + CMC_TABLE varchar(256) NOT NULL, + CMC_PARTITION varchar(767), + CMC_METRIC_TYPE varchar(128) NOT NULL, + CMC_METRIC_VALUE integer NOT NULL, + CMC_VERSION integer NOT NULL +); +INSERT INTO TMP_COMPACTION_METRICS_CACHE (CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION) + SELECT CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION FROM COMPACTION_METRICS_CACHE; +RENAME TABLE COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE_OLD, TMP_COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE; Review Comment: Derby does not support renaming multiple tables in a single `RENAME TABLE ... , ...` statement. Split this into two statements to keep the upgrade script valid. ########## standalone-metastore/metastore-server/src/main/sql/derby/upgrade-4.2.0-to-4.3.0.derby.sql: ########## @@ -1,7 +1,94 @@ ALTER TABLE HIVE_LOCKS ADD COLUMN HL_CATALOG varchar(128) NOT NULL DEFAULT 'hive'; ALTER TABLE MATERIALIZATION_REBUILD_LOCKS ADD COLUMN MRL_CAT_NAME varchar(128) NOT NULL DEFAULT 'hive'; +-- Add surrogate primary keys for HA database replication. Adding IDENTITY primary +-- keys via ALTER TABLE rewrites large Derby tables and holds exclusive locks for +-- the duration. Rebuild each populated table via a TMP_* copy/swap instead. +-- Counter tables contain a single row and keep the one-step ALTER form. Plan a +-- maintenance window for non-trivial embedded metastores; duration scales with +-- row counts. + +CREATE TABLE TMP_TXN_COMPONENTS ( + TC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + TC_DATABASE varchar(128) NOT NULL, + TC_TABLE varchar(256), + TC_PARTITION varchar(767), + TC_OPERATION_TYPE char(1) NOT NULL, + TC_WRITEID bigint +); +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; +RENAME TABLE TXN_COMPONENTS TO TXN_COMPONENTS_OLD, TMP_TXN_COMPONENTS TO TXN_COMPONENTS; +DROP TABLE TXN_COMPONENTS_OLD; +CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID); + +CREATE TABLE TMP_COMPLETED_TXN_COMPONENTS ( + CTC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CTC_TXNID bigint NOT NULL, + CTC_DATABASE varchar(128) NOT NULL, + CTC_TABLE varchar(256), + CTC_PARTITION varchar(767), + CTC_TIMESTAMP timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, + CTC_WRITEID bigint, + CTC_UPDATE_DELETE char(1) NOT NULL +); +INSERT INTO TMP_COMPLETED_TXN_COMPONENTS (CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE) + SELECT CTC_TXNID, CTC_DATABASE, CTC_TABLE, CTC_PARTITION, CTC_TIMESTAMP, CTC_WRITEID, CTC_UPDATE_DELETE FROM COMPLETED_TXN_COMPONENTS; +RENAME TABLE COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS_OLD, TMP_COMPLETED_TXN_COMPONENTS TO COMPLETED_TXN_COMPONENTS; +DROP TABLE COMPLETED_TXN_COMPONENTS_OLD; +CREATE INDEX COMPLETED_TXN_COMPONENTS_IDX ON COMPLETED_TXN_COMPONENTS (CTC_DATABASE, CTC_TABLE, CTC_PARTITION); + +CREATE TABLE TMP_COMPACTION_METRICS_CACHE ( + CMC_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + CMC_DATABASE varchar(128) NOT NULL, + CMC_TABLE varchar(256) NOT NULL, + CMC_PARTITION varchar(767), + CMC_METRIC_TYPE varchar(128) NOT NULL, + CMC_METRIC_VALUE integer NOT NULL, + CMC_VERSION integer NOT NULL +); +INSERT INTO TMP_COMPACTION_METRICS_CACHE (CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION) + SELECT CMC_DATABASE, CMC_TABLE, CMC_PARTITION, CMC_METRIC_TYPE, CMC_METRIC_VALUE, CMC_VERSION FROM COMPACTION_METRICS_CACHE; +RENAME TABLE COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE_OLD, TMP_COMPACTION_METRICS_CACHE TO COMPACTION_METRICS_CACHE; +DROP TABLE COMPACTION_METRICS_CACHE_OLD; + +CREATE TABLE TMP_WRITE_SET ( + WS_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + WS_DATABASE varchar(128) NOT NULL, + WS_TABLE varchar(256) NOT NULL, + WS_PARTITION varchar(767), + WS_TXNID bigint NOT NULL, + WS_COMMIT_ID bigint NOT NULL, + WS_OPERATION_TYPE char(1) NOT NULL +); +INSERT INTO TMP_WRITE_SET (WS_DATABASE, WS_TABLE, WS_PARTITION, WS_TXNID, WS_COMMIT_ID, WS_OPERATION_TYPE) + SELECT WS_DATABASE, WS_TABLE, WS_PARTITION, WS_TXNID, WS_COMMIT_ID, WS_OPERATION_TYPE FROM WRITE_SET; +RENAME TABLE WRITE_SET TO WRITE_SET_OLD, TMP_WRITE_SET TO WRITE_SET; +DROP TABLE WRITE_SET_OLD; + +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); + +CREATE TABLE TMP_MIN_HISTORY_WRITE_ID ( + MH_ID bigint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + MH_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID), + MH_DATABASE varchar(128) NOT NULL, + MH_TABLE varchar(256) NOT NULL, + MH_WRITEID bigint NOT NULL +); +INSERT INTO TMP_MIN_HISTORY_WRITE_ID (MH_TXNID, MH_DATABASE, MH_TABLE, MH_WRITEID) + SELECT MH_TXNID, MH_DATABASE, MH_TABLE, MH_WRITEID FROM MIN_HISTORY_WRITE_ID; +RENAME TABLE MIN_HISTORY_WRITE_ID TO MIN_HISTORY_WRITE_ID_OLD, TMP_MIN_HISTORY_WRITE_ID TO MIN_HISTORY_WRITE_ID; Review Comment: Derby does not support renaming multiple tables in a single `RENAME TABLE ... , ...` statement. Split into two rename statements so the upgrade script runs. -- 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]
