This is an automated email from the ASF dual-hosted git repository.
yuqi1129 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/gravitino.git
The following commit(s) were added to refs/heads/main by this push:
new e6a3a05571 [#12516] improvement(scripts): Add IF NOT EXISTS to CREATE
INDEX in upgrade SQL scripts for idempotency (#12968)
e6a3a05571 is described below
commit e6a3a055715dbf782ad9519f849429414ea1f0c6
Author: li jie <[email protected]>
AuthorDate: Thu Oct 29 16:28:37 2026 +0800
[#12516] improvement(scripts): Add IF NOT EXISTS to CREATE INDEX in upgrade
SQL scripts for idempotency (#12968)
### What changes were proposed in this pull request?
PostgreSQL: add `IF NOT EXISTS` to the 5 `CREATE INDEX` statements in
`scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql`. After this
change, every statement in the script is guarded by `IF NOT EXISTS` or
is naturally idempotent (`COMMENT ON`), so the whole upgrade script can
be safely re-run after a partial or completed run.
Add `testUpgradeToOneTwoIsIdempotent` to `TestSQLScripts` to verify the
PostgreSQL 1.1.0-to-1.2.0 upgrade script can be re-run as a whole, and
that the created indexes exist with the expected columns and uniqueness.
The test is skipped for non-PostgreSQL backends; retrying other upgrade
scripts is out of scope.
### Why are the changes needed?
A failed upgrade can leave committed changes behind. Re-running the
1.1.0-to-1.2.0 PostgreSQL upgrade script then fails on the
already-created indexes, preventing recovery through a normal retry.
Per review, this PR is now PostgreSQL-only:
- MySQL changes were dropped: MySQL 8.0 does not support `CREATE INDEX
IF NOT EXISTS`, and guarding only index creation does not make the whole
MySQL upgrade retryable (`ADD COLUMN`, `DROP INDEX`, `RENAME INDEX`
would still fail). Full MySQL upgrade-script idempotency will be tracked
in a separate issue.
- The 1.3.0-to-2.0.0 scripts are already retryable thanks to #13689.
Fixes #12516
### Does this PR introduce _any_ user-facing change?
No API or configuration changes. Operators can now re-run the PostgreSQL
1.1.0-to-1.2.0 upgrade script after a partial or completed run.
### How was this patch tested?
- Full `TestSQLScripts` suite passed locally (embedded H2 mode; the new
test skips non-PostgreSQL backends).
- The PostgreSQL path is exercised by CI with Docker: `./gradlew
:core:test --tests org.apache.gravitino.storage.TestSQLScripts -PskipITs
-PskipDockerTests=false`
Co-authored-by: lijie <[email protected]>
---
.../apache/gravitino/storage/TestSQLScripts.java | 35 ++++++++++++++++++++++
.../upgrade-1.1.0-to-1.2.0-postgresql.sql | 10 +++----
2 files changed, 40 insertions(+), 5 deletions(-)
diff --git
a/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
b/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
index 45158c6eb8..a79089aa8b 100644
--- a/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
+++ b/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
@@ -134,6 +134,41 @@ public class TestSQLScripts extends TestJDBCBackend {
}
}
+ /**
+ * Verifies the PostgreSQL 1.1.0-to-1.2.0 upgrade script can be re-run as a
whole after a
+ * completed upgrade: every statement is guarded by IF NOT EXISTS or is
naturally idempotent
+ * (COMMENT ON). The scope is limited to this script; other upgrade scripts
are not claimed to be
+ * retryable. The created indexes must exist with the expected columns and
uniqueness.
+ */
+ @TestTemplate
+ public void testUpgradeToOneTwoIsIdempotent() throws SQLException,
IOException {
+ if (!"postgresql".equals(backendType)) {
+ return;
+ }
+ Path scriptDir = upgradeScriptDirectory();
+ File upgrade =
scriptDir.resolve("upgrade-1.1.0-to-1.2.0-postgresql.sql").toFile();
+ dropAllTables();
+ executeScript(scriptDir.resolve("schema-1.1.0-postgresql.sql").toFile());
+ executeScript(upgrade);
+ // A retry after a completed upgrade must succeed; no statement may fail
again.
+ executeScript(upgrade);
+
+ // Index entries are formatted as "table:index name:ordinal
position:column:non-unique".
+ List<String> indexes = readSchemaIndexes(Set.of("function_meta",
"function_version_info"));
+ List<String> expectedIndexes =
+ List.of(
+ "function_meta:idx_function_meta_catalog_id:1:catalog_id:true",
+ "function_meta:idx_function_meta_metalake_id:1:metalake_id:true",
+
"function_version_info:idx_function_version_catalog_id:1:catalog_id:true",
+
"function_version_info:idx_function_version_metalake_id:1:metalake_id:true",
+
"function_version_info:idx_function_version_schema_id:1:schema_id:true");
+ for (String expectedIndex : expectedIndexes) {
+ Assertions.assertTrue(
+ indexes.contains(expectedIndex),
+ "Expected index entry missing after the re-run: " + expectedIndex);
+ }
+ }
+
/**
* Verifies every MySQL upgrade script from 0.5.0 onward can be re-run as a
whole after a
* completed upgrade: guarded DDL (ADD COLUMN, DROP INDEX, CREATE INDEX,
...) must skip instead of
diff --git a/scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql
b/scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql
index e75d75735a..4ce84aaf77 100644
--- a/scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql
+++ b/scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql
@@ -32,8 +32,8 @@ CREATE TABLE IF NOT EXISTS function_meta (
PRIMARY KEY (function_id),
UNIQUE (schema_id, function_name, deleted_at)
);
-CREATE INDEX idx_function_meta_metalake_id ON function_meta (metalake_id);
-CREATE INDEX idx_function_meta_catalog_id ON function_meta (catalog_id);
+CREATE INDEX IF NOT EXISTS idx_function_meta_metalake_id ON function_meta
(metalake_id);
+CREATE INDEX IF NOT EXISTS idx_function_meta_catalog_id ON function_meta
(catalog_id);
COMMENT ON TABLE function_meta IS 'function metadata';
COMMENT ON COLUMN function_meta.function_id IS 'function id';
@@ -61,9 +61,9 @@ CREATE TABLE IF NOT EXISTS function_version_info (
PRIMARY KEY (id),
UNIQUE (function_id, version, deleted_at)
);
-CREATE INDEX idx_function_version_metalake_id ON function_version_info
(metalake_id);
-CREATE INDEX idx_function_version_catalog_id ON function_version_info
(catalog_id);
-CREATE INDEX idx_function_version_schema_id ON function_version_info
(schema_id);
+CREATE INDEX IF NOT EXISTS idx_function_version_metalake_id ON
function_version_info (metalake_id);
+CREATE INDEX IF NOT EXISTS idx_function_version_catalog_id ON
function_version_info (catalog_id);
+CREATE INDEX IF NOT EXISTS idx_function_version_schema_id ON
function_version_info (schema_id);
COMMENT ON TABLE function_version_info IS 'function version info';
COMMENT ON COLUMN function_version_info.id IS 'auto increment id';
