apache / apache/gravitino

[Improvement] Add IF NOT EXISTS to CREATE INDEX in upgrade SQL scripts for idempotency

Open
#12,516 2 comments 0 reactions 0 assignees View on GitHub
improvement
Dominant language
Java
Stars
3.2k
Forks
935
Avg merge
1d 17h
Merged PRs (30d)
339

Description

## What would you like to be improved?

Several upgrade SQL scripts contain `CREATE INDEX` statements without `IF NOT EXISTS`, which causes errors when the migration is re-run (e.g., during retry scenarios or when running upgrade scripts in sequence from an arbitrary starting version).

Affected files:

upgrade-0.8.0-to-0.9.0-postgresql.sql

```
ALTER TABLE fileset_version_info ADD COLUMN storage_location_name VARCHAR(256) NOT NULL DEFAULT 'unknown';
ALTER TABLE fileset_version_info DROP CONSTRAINT fileset_version_info_fileset_id_version_deleted_at_key;
ALTER TABLE fileset_version_info ADD CONSTRAINT uk_fid_ver_sto_del UNIQUE (...);
ALTER TABLE fileset_version_info ALTER COLUMN storage_location_name DROP DEFAULT;

```

- `scripts/postgresql/upgrade-1.1.0-to-1.2.0-postgresql.sql` (5 occurrences):
- `CREATE INDEX idx_function_meta_metalake_id`
- `CREATE INDEX idx_function_meta_catalog_id`
- `CREATE INDEX idx_function_version_metalake_id`
- `CREATE INDEX idx_function_version_catalog_id`
- `CREATE INDEX idx_function_version_schema_id`
- `scripts/mysql/upgrade-1.2.0-to-1.3.0-mysql.sql` (3 occurrences):
- `CREATE INDEX idx_user_meta_name_del_upd`
- `CREATE INDEX idx_owner_meta_del_upd_obj`
- `CREATE INDEX idx_group_meta_name_del_upd`
- `scripts/mysql/upgrade-1.3.0-to-2.0.0-mysql.sql` (1 occurrence):
- `CREATE INDEX idx_tid_value`

When these scripts are executed more than once, PostgreSQL raises `ERROR: relation "..." already exists` and MySQL raises `Duplicate key name`. This blocks automated upgrade tooling that applies all scripts sequentially for safety.

## How should we improve?

For PostgreSQL: add `IF NOT EXISTS` to all affected `CREATE INDEX` statements.

For MySQL: MySQL 8.0.x does not support `CREATE INDEX IF NOT EXISTS`. The recommended approach is to use `DROP INDEX IF EXISTS ... ON ` before the `CREATE INDEX`, or conditionally skip via a stored procedure.

Contributor guide

Open the contributing guide

Research direction

Review the listed PostgreSQL and MySQL upgrade scripts, starting with the affected CREATE INDEX statements and the repository's existing migration conventions. Confirm the supported MySQL approach before editing, then verify that each affected script can be rerun without duplicate-index errors while preserving normal upgrade behavior.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.