This is an automated email from the ASF dual-hosted git repository.
jinsongzhou pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/amoro.git
The following commit(s) were added to refs/heads/master by this push:
new c2c9f53b7 [AMORO-3267] Fix postgres upgrade script bugs (#3268)
c2c9f53b7 is described below
commit c2c9f53b7a644f7d1057d906f9f729f0d8f1ac4e
Author: veli.yang <[email protected]>
AuthorDate: Thu Oct 17 15:18:52 2024 +0800
[AMORO-3267] Fix postgres upgrade script bugs (#3268)
bugfix:pg upgrade sql script
---
.../resources/postgres/upgrade-0.7.0-to-0.7.1.sql | 34 +++++++++++-----------
amoro-ams/src/main/resources/postgres/upgrade.sql | 8 ++---
2 files changed, 21 insertions(+), 21 deletions(-)
diff --git a/amoro-ams/src/main/resources/postgres/upgrade-0.7.0-to-0.7.1.sql
b/amoro-ams/src/main/resources/postgres/upgrade-0.7.0-to-0.7.1.sql
index 96ef43216..e837c0599 100644
--- a/amoro-ams/src/main/resources/postgres/upgrade-0.7.0-to-0.7.1.sql
+++ b/amoro-ams/src/main/resources/postgres/upgrade-0.7.0-to-0.7.1.sql
@@ -13,27 +13,27 @@
-- See the License for the specific language governing permissions and
-- limitations under the License.
-RENAME TABLE table_runtime TO table_runtime_backup;
-CREATE TABLE table_runtime LIKE table_runtime_backup;
+ALTER TABLE table_runtime RENAME TO table_runtime_backup;
+CREATE TABLE table_runtime (LIKE table_runtime_backup INCLUDING ALL)
-ALTER TABLE table_runtime CHANGE COLUMN optimizing_status
optimizing_status_code INT DEFAULT 7;
+ALTER TABLE table_runtime ALTER COLUMN optimizing_status
optimizing_status_code INT DEFAULT 7;
CREATE INDEX idx_optimizer_status_and_time ON
table_runtime(optimizing_status_code, optimizing_status_start_time DESC);
INSERT INTO table_runtime(
- `table_id`,`catalog_name`, `db_name`, `table_name`,
`current_snapshot_id`,`current_change_snapshotId`, `last_optimized_snapshotId`,
- `last_optimized_change_snapshotId`, `last_major_optimizing_time`,
`last_minor_optimizing_time`, `last_full_optimizing_time`,
- `optimizing_status_code`, `optimizing_status_start_time`,
`optimizing_process_id`, `optimizer_group`, `table_config`,
- `optimizing_config`, `pending_input`)
-SELECT `table_id`,`catalog_name`, `db_name`, `table_name`,
`current_snapshot_id`,`current_change_snapshotId`, `last_optimized_snapshotId`,
- `last_optimized_change_snapshotId`, `last_major_optimizing_time`,
`last_minor_optimizing_time`, `last_full_optimizing_time`,
+ table_id,catalog_name, db_name, table_name,
current_snapshot_id,current_change_snapshotId, last_optimized_snapshotId,
+ last_optimized_change_snapshotId, last_major_optimizing_time,
last_minor_optimizing_time, last_full_optimizing_time,
+ optimizing_status_code, optimizing_status_start_time,
optimizing_process_id, optimizer_group, table_config,
+ optimizing_config, pending_input)
+SELECT table_id,catalog_name, db_name, table_name,
current_snapshot_id,current_change_snapshotId, last_optimized_snapshotId,
+ last_optimized_change_snapshotId, last_major_optimizing_time,
last_minor_optimizing_time, last_full_optimizing_time,
CASE
- WHEN `optimizing_status` = 'IDLE' THEN 700
- WHEN `optimizing_status` = 'PENDING' THEN 600
- WHEN `optimizing_status` = 'PLANNING' THEN 500
- WHEN `optimizing_status` = 'COMMITTING' THEN 400
- WHEN `optimizing_status` = 'MINOR_OPTIMIZING' THEN 300
- WHEN `optimizing_status` = 'MAJOR_OPTIMIZING' THEN 200
- WHEN `optimizing_status` = 'FULL_OPTIMIZING' THEN 100
+ WHEN optimizing_status = 'IDLE' THEN 700
+ WHEN optimizing_status = 'PENDING' THEN 600
+ WHEN optimizing_status = 'PLANNING' THEN 500
+ WHEN optimizing_status = 'COMMITTING' THEN 400
+ WHEN optimizing_status = 'MINOR_OPTIMIZING' THEN 300
+ WHEN optimizing_status = 'MAJOR_OPTIMIZING' THEN 200
+ WHEN optimizing_status = 'FULL_OPTIMIZING' THEN 100
END,
- `optimizing_status_start_time`, `optimizing_process_id`,
`optimizer_group`, `table_config`, `optimizing_config`, `pending_input`
+ optimizing_status_start_time, optimizing_process_id, optimizer_group,
table_config, optimizing_config, pending_input
FROM table_runtime_backup;
\ No newline at end of file
diff --git a/amoro-ams/src/main/resources/postgres/upgrade.sql
b/amoro-ams/src/main/resources/postgres/upgrade.sql
index 0267468c2..ce3a399a6 100644
--- a/amoro-ams/src/main/resources/postgres/upgrade.sql
+++ b/amoro-ams/src/main/resources/postgres/upgrade.sql
@@ -17,11 +17,11 @@
-- We will confirm the corresponding version of these upgrade scripts when
releasing.
-- NEW SCHEMA CHANGE FOR CAS BASE BLOCKER
-TRUNCATE TABLE `table_blocker`;
-ALTER TABLE `table_blocker` DROP INDEX `table_index`;
-ALTER TABLE `table_blocker` ADD COLUMN `prev_blocker_id` bigint(20) NOT NULL
DEFAULT -1;
+TRUNCATE TABLE table_blocker;
+ALTER TABLE table_blocker DROP INDEX table_index;
+ALTER TABLE table_blocker ADD COLUMN prev_blocker_id bigint(20) NOT NULL
DEFAULT -1;
COMMENT ON COLUMN table_blocker.prev_blocker_id IS 'prev blocker id when
created';
-ALTER TABLE `table_blocker` ADD UNIQUE KEY `uq_prev`
(`catalog_name`,`db_name`,`table_name`, `prev_blocker_id`);
+ALTER TABLE table_blocker ADD UNIQUE KEY uq_prev (catalog_name, db_name,
table_name, prev_blocker_id);
-- ADD COLUMN table_summary FOR TABLE_RUNTIME
ALTER TABLE table_runtime ADD COLUMN table_summary TEXT;
