MemberJunction / MemberJunction/MJ
mj migrate convert: four gaps found producing the v5.50 PG counterparts
- Dominant language
- TSQL
- Stars
- 29
- Forks
- 6
- Avg merge
- 2d 1h
- Merged PRs (30d)
- 323
Description
Four distinct converter problems hit while producing the v5.50.0 PostgreSQL counterparts (PR #3342). Filing together since they share a workflow; split if useful.
## 1. Pre-baseline migrations are treated as in-scope and halt the bake chain
`convert --split` treats any SS migration lacking a `.pg.sql` as in-scope, including `V202602131500` and `V202602141421`, which **predate `B202602151200__v5.0__Baseline`** and are never applied anywhere. Verified: a PG deploy's `flyway_schema_history` starts at rank 1 = `202607091514 v5.46.x Baseline.pg`, with zero pre-baseline rows.
`/pg-migrate-experimental` already documents these as noise to ignore. The problem is that with `--bake-codegen` the converter processes in order and **halts at the first gap**, so these two block the entire chain before it reaches any v5.50 migration.
**Fix:** skip migrations at or below the highest `B*` baseline timestamp.
**Workaround:** convert per-file with `--file`.
## 2. `GRANT EXECUTE` transpiles to an invalid PostgreSQL form
`GRANT EXECUTE ON [${flyway:defaultSchema}].[spCreateList] TO [cdp_UI]` becomes `GRANT EXECUTE ON __mj.spCreateList`, which PostgreSQL folds to lowercase and resolves as a **relation**:
```
relation "__mj.spCreateList" does not exist
```
…even though the function exists as the quoted identifier `"spCreateList"`. PG needs `GRANT EXECUTE ON FUNCTION __mj."spCreateList"`, which is the form the committed ledger already uses for every CodeGen grant.
## 3. Dependent views are not dropped before `DROP COLUMN`
PostgreSQL holds a hard dependency from a view to the columns it selects; SQL Server does not for non-schema-bound views. So a transpiled `DROP COLUMN` fails:
```
ERROR: cannot drop column SummaryPromptRunID of table "ConversationDetail"
because other objects depend on it
DETAIL: view "vwConversationDetails" depends on column SummaryPromptRunID
```
The converter should emit `DROP VIEW IF EXISTS ... CASCADE` before a `DROP COLUMN` whose column is referenced by a base view, and rely on the inline bake to recreate it. **This one is not in the runbook's blind-spot list.**
Related: `IF EXISTS (SELECT 1 FROM sys.foreign_keys/sys.indexes) ...` guards are dropped from the body entirely and listed only in the header gap block — PG needs no guard (`DROP CONSTRAINT IF EXISTS`, `DROP INDEX IF EXISTS`).
## 4. `rebake` overwrites hand-authored counterparts, and refuses `.needs-hand`-derived files
Two problems:
- `mj migrate rebake` silently rewrote a hand-authored `.pg.sql` back to the broken generated form, which then failed the fresh-DB gate. Hand-authored counterparts should be detected and preserved, or at minimum reported.
- It reports `kept ... (transpile gap — committed preserved)` and skips any file that originated as `.needs-hand`, even after the gaps are resolved and the file applies cleanly. That leaves no supported way to attach a CodeGen bake to a hand-finished migration; the bake had to be captured manually from the catalog via `pg_get_viewdef`/`pg_get_functiondef`.
Also note `rebake`'s default `--baseline-floor` is the latest `B*`, which would re-bake **committed** counterparts and violate the immutable-ledger rule. A safer default or a hard refusal to touch committed files would help.
Contributor guide
Research direction
Start with /pg-migrate-experimental and the converter paths for --split, --bake-codegen, --file, and rebake. Reproduce the v5.50 workflow and fresh-DB gate, checking baseline filtering, GRANT FUNCTION output, dependent-view handling, and rebake behavior. Done means pre-baseline gaps no longer halt conversion, views and grants apply on PostgreSQL, and hand-authored or resolved .needs-hand counterparts are preserved and rebaked safely.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- cli, databases, tooling
- Issue type
- Bug
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 38/100