danhuawang opened a new issue, #12519:
URL: https://github.com/apache/gravitino/issues/12519
## What would you like to be improved?
Currently, the Gravitino database schema has no mechanism to record which
schema version is currently applied. The upgrade scripts (e.g.
`upgrade-1.1.0-to-1.2.0-postgresql.sql`) are executed by detecting whether the
`metalake_meta` table exists to distinguish a fresh install from an upgrade,
then running **all** upgrade scripts whose target version is `<= current app
version` — regardless of whether they have already been applied.
This leads to two concrete problems:
1. **Non-idempotent upgrade scripts fail silently.** Scripts like
`upgrade-0.8.0-to-0.9.0` contain statements such as `ALTER TABLE ... ADD
COLUMN` and `DROP CONSTRAINT` / `ADD CONSTRAINT` without `IF NOT EXISTS` / `IF
EXISTS` guards. Re-running them on an already-upgraded database causes errors.
To avoid blocking the upgrade, the Helm job currently swallows these errors
with a `WARNING` and continues — meaning a genuine failure (wrong permissions,
disk full, etc.) is indistinguishable from an expected "already applied" skip.
2. **Multi-hop upgrade is fragile.** A database at version 1.2.0 upgrading
to 2.0.0 will re-execute `upgrade-0.x.0-to-*.sql` scripts that were applied
long ago, relying entirely on SQL-level idempotency that is not consistently
guaranteed across all scripts.
## How should we improve?
Introduce a `schema_version` table to persist the current schema version in
the database itself:
```sql
CREATE TABLE IF NOT EXISTS schema_version (
version VARCHAR(32) NOT NULL,
upgraded_at BIGINT NOT NULL,
PRIMARY KEY (version)
);
```
Each schema file and upgrade script should `INSERT` the applicable version
into this table on completion. The upgrade runner (Helm Job / init-container)
should:
1. Read the current version from `schema_version` (if the table exists).
2. Only execute upgrade scripts whose source version `>` current DB version
and target version `<=` app version.
3. `INSERT` the new version after each script succeeds.
4. Fail fast (`exit 1`) on any upgrade script error rather than silently
continuing, since with precise version tracking there is no ambiguity about
whether an error is "expected".
This approach eliminates the reliance on SQL idempotency as a safety net,
makes multi-hop upgrades explicit and auditable, and allows operators to
immediately see the applied schema version by querying the database.
--
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]
