MemberJunction / MemberJunction/MJ

mj migrate convert: four gaps found producing the v5.50 PG counterparts

Open
#3,345 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.