lablup / lablup/backend.ai

Fresh installs are missing three indexes that migrated installs have

Open
#12,905 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
670
Forks
183
Avg merge
15h 13m
Merged PRs (30d)
368

Description

## Objective

Make the schema produced by a fresh install match the schema produced by replaying migrations, for three indexes that only the migrations create.

## Background

The schema is defined in two places. A fresh install runs mgr schema oneshot, which builds tables from the SQLAlchemy model metadata and stamps head without executing a single migration. An existing install runs alembic upgrade head, which executes the migration files. Both must produce the same schema, but nothing checks that they do.

Three migrations create an index that the corresponding model never declares, so the index exists only on migrated databases:

- ix_role_invitations_invitee_user_id (ad7acfe8aa1c) — role_invitation/row.py declares only uq_role_invitations_active
- ix_vfolders_creator_id (b4e7f1a2c3d5) — vfolder/row.py declares creator_id with no index
- ix_prometheus_query_presets_category_id (a3b4c5d6e7f8) — prometheus_query_preset/row.py declares category_id with no index

Verified on a database built at head by oneshot: none of the three indexes is present, and no later migration drops them, so a migrated database has all three. role_invitations.invitee_user_id is the worst case — it carries a FK to users with ON DELETE CASCADE, so on a fresh install every user deletion scans role_invitations. The partial index uq_role_invitations_active cannot substitute: it is restricted to state != accepted, so it does not cover the rows a cascade must find.

The divergence also breaks downgrades: each of the three downgrades calls drop_index on an index that a fresh install never had, aborting with UndefinedObjectError. Whether a downgrade succeeds therefore depends on how the database was created. This is how the drift was found.

Out of scope: fk_kernels_image_id (cd067180a8b1) and fk_login_sessions_login_client_type_id (c7f2a8e31b04) hardcode constraint names that the metadata naming convention renders differently on a fresh install (fk_kernels_image_id_images and fk_login_sessions_login_client_type_id_login_client_types). Those constraints do exist and are enforced on both paths — only the names differ — so they are tracked separately.

## Acceptance Criteria

- The models declare all three indexes, so a fresh install creates them.
- A migration heals databases already created by oneshot, using an existence check so it is a no-op on migrated databases that already have the indexes.
- The three downgrades no longer fail on a fresh install.
- Verified against a local DB by comparing a oneshot-built schema before and after.
- pants fmt / lint / check pass.

JIRA Issue: BA-6913

Contributor guide

Open the contributing guide

Research direction

Start with role_invitation/row.py, vfolder/row.py, and prometheus_query_preset/row.py, then read migrations ad7acfe8aa1c, b4e7f1a2c3d5, and a3b4c5d6e7f8. Add the three model indexes and make each migration safely create a missing index, then verify fresh and migrated schemas, downgrade behavior, and run pants fmt, lint, and check.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlalchemy
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.