This is an automated email from the ASF dual-hosted git repository.
roryqi 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 808fcb5c5b [MINOR] fix(core): Index role relations by role_id (#14356)
808fcb5c5b is described below
commit 808fcb5c5b63e22293866115375e42dd0596c67f
Author: roryqi <[email protected]>
AuthorDate: Thu Oct 29 16:50:09 2026 +0800
[MINOR] fix(core): Index role relations by role_id (#14356)
### What changes were proposed in this pull request?
Add indexes leading with `role_id` in the 2.0.0 schemas and
1.3.0-to-2.0.0 upgrades:
- PostgreSQL: `user_role_rel` and `group_role_rel`.
- MySQL and H2: `group_role_rel`; `user_role_rel` already has the index.
Keep the MySQL upgrade retryable by checking for an existing index
leading with `role_id`. Extend `TestSQLScripts` to verify these indexes
after fresh installation and upgrade.
### Why are the changes needed?
Deleting a role soft-deletes both relation tables by `role_id`. Without
a leading index, these updates scan the relation tables and slow down
role deletion as assignments grow.
### Does this PR introduce _any_ user-facing change?
No API or configuration changes.
### How was this patch tested?
24 tests passed across H2, MySQL 8.0, and PostgreSQL 13. Formatting and
diff checks passed.
---
.../apache/gravitino/storage/TestSQLScripts.java | 37 ++++++++++++++++++++--
scripts/h2/schema-2.0.0-h2.sql | 3 +-
scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql | 3 ++
scripts/mysql/schema-2.0.0-mysql.sql | 3 +-
scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql | 9 ++++++
scripts/postgresql/schema-2.0.0-postgresql.sql | 2 ++
.../upgrade-1.3.0-to-2.0.0-postgresql.sql | 4 +++
7 files changed, 57 insertions(+), 4 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 a79089aa8b..008b5b75a2 100644
--- a/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
+++ b/core/src/test/java/org/apache/gravitino/storage/TestSQLScripts.java
@@ -23,6 +23,7 @@ import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
+import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@@ -32,6 +33,7 @@ import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
+import java.util.Locale;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
@@ -96,11 +98,14 @@ public class TestSQLScripts extends TestJDBCBackend {
}
}
- for (List<File> scripts : versionScrips.values()) {
+ for (Map.Entry<String, List<File>> versionScripts :
versionScrips.entrySet()) {
dropAllTables();
- for (File scriptFile : scripts) {
+ for (File scriptFile : versionScripts.getValue()) {
executeScript(scriptFile);
}
+ if ("2.0.0".equals(versionScripts.getKey())) {
+ assertRoleRelationsIndexedByRoleId("schema " +
versionScripts.getKey());
+ }
}
}
@@ -131,6 +136,9 @@ public class TestSQLScripts extends TestJDBCBackend {
dropAllTables();
executeScript(sourceSchema);
executeScript(upgradeScript);
+ if ("2.0.0".equals(upgradeMatcher.group(2))) {
+ assertRoleRelationsIndexedByRoleId(upgradeScript.getName());
+ }
}
}
@@ -558,6 +566,31 @@ public class TestSQLScripts extends TestJDBCBackend {
}
}
+ private void assertRoleRelationsIndexedByRoleId(String source) throws
SQLException {
+ try (SqlSession sqlSession =
+
SqlSessionFactoryHelper.getInstance().getSqlSessionFactory().openSession(true);
+ Connection connection = sqlSession.getConnection()) {
+ DatabaseMetaData metaData = connection.getMetaData();
+ for (String table : List.of("user_role_rel", "group_role_rel")) {
+ String tableName =
+ metaData.storesUpperCaseIdentifiers() ?
table.toUpperCase(Locale.ROOT) : table;
+ boolean indexed = false;
+ try (ResultSet indexes =
+ metaData.getIndexInfo(connection.getCatalog(), null, tableName,
false, false)) {
+ while (indexes.next()) {
+ if (indexes.getShort("ORDINAL_POSITION") == 1
+ &&
"role_id".equalsIgnoreCase(indexes.getString("COLUMN_NAME"))) {
+ indexed = true;
+ break;
+ }
+ }
+ }
+ Assertions.assertTrue(
+ indexed, "No index leading with role_id on " + table + " after " +
source);
+ }
+ }
+ }
+
private void assertSchemaMatchesFreshInstall(
Map<String, Set<String>> upgradedColumns, List<String> upgradedIndexes)
throws SQLException, IOException {
diff --git a/scripts/h2/schema-2.0.0-h2.sql b/scripts/h2/schema-2.0.0-h2.sql
index a3fda62695..1e1d2954f7 100644
--- a/scripts/h2/schema-2.0.0-h2.sql
+++ b/scripts/h2/schema-2.0.0-h2.sql
@@ -248,7 +248,8 @@ CREATE TABLE IF NOT EXISTS `group_role_rel` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'relation
deleted at',
PRIMARY KEY (`id`),
CONSTRAINT `uk_gi_ri_del` UNIQUE (`group_id`, `role_id`, `deleted_at`),
- KEY `idx_gid` (`group_id`)
+ KEY `idx_gid` (`group_id`),
+ KEY `group_role_rel_idx_role_id` (`role_id`)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `idp_user_meta` (
diff --git a/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
b/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
index 4a80bab24f..542d229459 100644
--- a/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
+++ b/scripts/h2/upgrade-1.3.0-to-2.0.0-h2.sql
@@ -226,3 +226,6 @@ ALTER TABLE `fileset_meta`
ALTER TABLE `policy_meta`
ADD COLUMN IF NOT EXISTS `occ_version` INT UNSIGNED NOT NULL DEFAULT 1
COMMENT 'policy optimistic concurrency version' AFTER `last_version`;
+
+-- Role deletion soft-deletes relation rows by role_id.
+CREATE INDEX IF NOT EXISTS `group_role_rel_idx_role_id` ON `group_role_rel`
(`role_id`);
diff --git a/scripts/mysql/schema-2.0.0-mysql.sql
b/scripts/mysql/schema-2.0.0-mysql.sql
index b8e79b9afa..f868bfe339 100644
--- a/scripts/mysql/schema-2.0.0-mysql.sql
+++ b/scripts/mysql/schema-2.0.0-mysql.sql
@@ -239,7 +239,8 @@ CREATE TABLE IF NOT EXISTS `group_role_rel` (
`deleted_at` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'relation
deleted at',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_gi_ri_del` (`group_id`, `role_id`, `deleted_at`),
- KEY `group_role_rel_idx_rid` (`group_id`)
+ KEY `group_role_rel_idx_rid` (`group_id`),
+ KEY `group_role_rel_idx_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT 'group
role relation';
CREATE TABLE IF NOT EXISTS `idp_user_meta` (
diff --git a/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
b/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
index 77ea3e487f..2a71058df3 100644
--- a/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
+++ b/scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql
@@ -685,3 +685,12 @@ SET @ddl = IF(EXISTS(SELECT 1 FROM
information_schema.columns
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+
+-- Role deletion soft-deletes relation rows by role_id.
+SET @ddl = IF(EXISTS(SELECT 1 FROM information_schema.statistics
+ WHERE table_schema = DATABASE() AND table_name = 'group_role_rel' AND
column_name = 'role_id' AND seq_in_index = 1),
+ 'SELECT 1',
+ 'ALTER TABLE `group_role_rel` ADD INDEX `group_role_rel_idx_role_id`
(`role_id`)');
+PREPARE stmt FROM @ddl;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
diff --git a/scripts/postgresql/schema-2.0.0-postgresql.sql
b/scripts/postgresql/schema-2.0.0-postgresql.sql
index 109b8bc456..788e4a15d6 100644
--- a/scripts/postgresql/schema-2.0.0-postgresql.sql
+++ b/scripts/postgresql/schema-2.0.0-postgresql.sql
@@ -374,6 +374,7 @@ CREATE TABLE IF NOT EXISTS user_role_rel (
);
CREATE INDEX IF NOT EXISTS user_role_rel_idx_user_id ON user_role_rel
(user_id);
+CREATE INDEX IF NOT EXISTS user_role_rel_idx_role_id ON user_role_rel
(role_id);
COMMENT ON TABLE user_role_rel IS 'user role relation metadata';
COMMENT ON COLUMN user_role_rel.id IS 'auto increment id';
@@ -422,6 +423,7 @@ CREATE TABLE IF NOT EXISTS group_role_rel (
);
CREATE INDEX IF NOT EXISTS group_role_rel_idx_group_id ON group_role_rel
(group_id);
+CREATE INDEX IF NOT EXISTS group_role_rel_idx_role_id ON group_role_rel
(role_id);
COMMENT ON TABLE group_role_rel IS 'relation between group and role';
COMMENT ON COLUMN group_role_rel.id IS 'auto increment id';
COMMENT ON COLUMN group_role_rel.group_id IS 'group id';
diff --git a/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
b/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
index b5a70c34ed..237902df9f 100644
--- a/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
+++ b/scripts/postgresql/upgrade-1.3.0-to-2.0.0-postgresql.sql
@@ -337,3 +337,7 @@ COMMENT ON COLUMN fileset_meta.occ_version IS 'fileset
optimistic concurrency ve
ALTER TABLE policy_meta ADD COLUMN IF NOT EXISTS occ_version INT NOT NULL
DEFAULT 1;
COMMENT ON COLUMN policy_meta.occ_version IS 'policy optimistic concurrency
version';
+
+-- Role deletion soft-deletes relation rows by role_id.
+CREATE INDEX IF NOT EXISTS user_role_rel_idx_role_id ON user_role_rel
(role_id);
+CREATE INDEX IF NOT EXISTS group_role_rel_idx_role_id ON group_role_rel
(role_id);
