fix(db): retire all legacy cad_buildings indexes — hotfix2 (#168) #174

Merged

lekss361 merged 1 commit from fix/cadastre-schema-all-pkey-rename into main

2026-05-15 11:09:41 +00:00

lekss361 commented

2026-05-15 11:05:27 +00:00

(Migrated from github.com)

Summary

Hotfix2 для #168 — first hotfix (#173) fixed только pkey, deploy упал на следующем index cad_buildings_geom_gist
Same root cause: PG не auto-renames indexes на table rename → ALL 6 backing indexes остались на cad_buildings_old_apr26

Error from latest deploy

ERROR: relation "cad_buildings_geom_gist" already exists
CONTEXT: ALTER INDEX cad_buildings_new_geom_gist RENAME TO cad_buildings_geom_gist

Fix

Добавлены 5 дополнительных ALTER INDEX IF EXISTS RENAME TO _old_apr26_* строк (всего 6 retires перед 6 renames):

cad_buildings_pkey (уже было в #173)
cad_buildings_geom_gist (new)
cad_buildings_quarter_idx (new)
cad_buildings_objdoc_idx (new)
cad_buildings_complex_idx (new)
cad_buildings_purpose_idx (new)

IF EXISTS makes it idempotent.

State on prod

Migration 92 transaction rolled back 4× now → NOT в _schema_migrations.
После merge → next deploy re-runs migration 92 fresh с full fix.

Closes hotfix2 of #168.

## Summary - **Hotfix2** для #168 — first hotfix (#173) fixed только pkey, deploy упал на следующем index `cad_buildings_geom_gist` - Same root cause: PG не auto-renames indexes на table rename → ALL 6 backing indexes остались на `cad_buildings_old_apr26` ## Error from latest deploy ``` ERROR: relation "cad_buildings_geom_gist" already exists CONTEXT: ALTER INDEX cad_buildings_new_geom_gist RENAME TO cad_buildings_geom_gist ``` ## Fix Добавлены 5 дополнительных `ALTER INDEX IF EXISTS RENAME TO _old_apr26_*` строк (всего 6 retires перед 6 renames): - `cad_buildings_pkey` (уже было в #173) - `cad_buildings_geom_gist` (new) - `cad_buildings_quarter_idx` (new) - `cad_buildings_objdoc_idx` (new) - `cad_buildings_complex_idx` (new) - `cad_buildings_purpose_idx` (new) `IF EXISTS` makes it idempotent. ## State on prod Migration 92 transaction rolled back **4× now** → NOT в `_schema_migrations`. После merge → next deploy re-runs migration 92 fresh с full fix. Closes hotfix2 of #168.

lekss361 commented

2026-05-15 11:09:25 +00:00

(Migrated from github.com)

Final review — CI ✅, fix exhaustive by construction

Cross-checked агентом — fix complete, не reactive guesswork.

Symmetry verification

✅ 6 retired legacy names = exact 1:1 mirror of 6 CREATE INDEX statements (lines 316-327 в migration) = 6 _new_* → unsuffixed renames (lines 513-520):

Retired (this PR)	CREATE (migration)	RENAME (K.3)
`cad_buildings_pkey` (#173)	`cad_buildings_new_pkey`	→ `cad_buildings_pkey`
`cad_buildings_geom_gist`	`cad_buildings_new_geom_gist`	→ `cad_buildings_geom_gist`
`cad_buildings_quarter_idx`	`cad_buildings_new_quarter_idx`	→ `cad_buildings_quarter_idx`
`cad_buildings_objdoc_idx`	`cad_buildings_new_objdoc_idx`	→ `cad_buildings_objdoc_idx`
`cad_buildings_complex_idx`	`cad_buildings_new_complex_idx`	→ `cad_buildings_complex_idx`
`cad_buildings_purpose_idx`	`cad_buildings_new_purpose_idx`	→ `cad_buildings_purpose_idx`

Schema self-consistent: pre-K.1 backup cad_buildings имел тот же index layout что новый cad_buildings_new. Fix exhaustive by construction, not enumeration guesswork.

Hotfix3 risk

Extremely unlikely — barring manual DBA-добавленные indexes outside migrations. IF EXISTS tolerates absence even в этом случае; и любой extra legacy index не может collide с 6 known _new_* targets.

Idempotency

✅ Все 6 renames ALTER INDEX IF EXISTS ... RENAME TO ..._old_apr26_* — matches pattern below
✅ Re-runnable on partial state

Prod state

✅ Migration 92 rolled back 4× → NOT в _schema_migrations → edit in-place корректно
✅ CI green (backend + frontend SUCCESS)
✅ mergeable=MERGEABLE
✅ Никаких manual ops не требуется

approve merge

## Final review — CI ✅, fix exhaustive by construction Cross-checked агентом — fix complete, не reactive guesswork. ### Symmetry verification ✅ **6 retired legacy names** = exact 1:1 mirror of **6 CREATE INDEX statements** (lines 316-327 в migration) = **6 `_new_*` → unsuffixed renames** (lines 513-520): | Retired (this PR) | CREATE (migration) | RENAME (K.3) | |---|---|---| | `cad_buildings_pkey` (#173) | `cad_buildings_new_pkey` | → `cad_buildings_pkey` | | `cad_buildings_geom_gist` | `cad_buildings_new_geom_gist` | → `cad_buildings_geom_gist` | | `cad_buildings_quarter_idx` | `cad_buildings_new_quarter_idx` | → `cad_buildings_quarter_idx` | | `cad_buildings_objdoc_idx` | `cad_buildings_new_objdoc_idx` | → `cad_buildings_objdoc_idx` | | `cad_buildings_complex_idx` | `cad_buildings_new_complex_idx` | → `cad_buildings_complex_idx` | | `cad_buildings_purpose_idx` | `cad_buildings_new_purpose_idx` | → `cad_buildings_purpose_idx` | Schema self-consistent: pre-K.1 backup `cad_buildings` имел тот же index layout что новый `cad_buildings_new`. Fix exhaustive **by construction**, not enumeration guesswork. ### Hotfix3 risk **Extremely unlikely** — barring manual DBA-добавленные indexes outside migrations. `IF EXISTS` tolerates absence even в этом случае; и любой extra legacy index не может collide с 6 known `_new_*` targets. ### Idempotency ✅ Все 6 renames `ALTER INDEX IF EXISTS ... RENAME TO ..._old_apr26_*` — matches pattern below ✅ Re-runnable on partial state ### Prod state ✅ Migration 92 rolled back 4× → **NOT в `_schema_migrations`** → edit in-place корректно ✅ CI green (backend + frontend SUCCESS) ✅ mergeable=MERGEABLE ✅ Никаких manual ops не требуется approve merge