Migrations: jobs 0014 aborts on the very case collisions (Q95 beside q95) it was written to fold #570

New issue

Closed

opened 2026-10-01 10:47:44 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-01 10:47:44 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the data migrations and running jobs 0014 forward with MigrationExecutor on SQLite over seeded rows.

Checked again on 2026-10-01 against main at 0a5cad587: still present.

jobs/migrations/0014_remove_companyidentifier_one_company_per_identifier_per_owner_and_more.py exists to fold stored identifiers to their scheme's case and to report, rather than refuse, any pair that collides once folded (#211). It cannot do either for the rows it was written for. It saves each folded value before it checks for a duplicate, while the old case-sensitive constraint is still in place, so the first collision raises IntegrityError and the migration rolls back. Other identifiers are not folded or de-duplicated at all, yet the new constraint compares them case-insensitively, so two of them that differ only in case make AddConstraint fail. Either way an instance holding such rows cannot migrate past jobs 0014.

Where: jobs/migrations/0014_remove_companyidentifier_one_company_per_identifier_per_owner_and_more.py: fold_and_report saves at line 43 (row.save(update_fields=["value"])) and only builds the duplicate key at line 47; lines 45-46 skip every other row; the operations list runs RunPython(fold_and_report, ...) at line 70, before RemoveConstraint("one_company_per_identifier_per_owner") at lines 71-74, and adds the new unique_other_identifier_per_company on Lower("label"), Lower("value") at lines 89-99.
What happens:
1. One account has Wikidata Q95 on one company and q95 on another, which the case-sensitive constraint from 0005_company_identifiers.py (owner, scheme, value, where scheme is not other) allowed. Whichever row comes second by pk, folding makes it equal to the first while that constraint still exists, and line 43 raises IntegrityError: UNIQUE constraint failed: jobs_companyidentifier.owner_id, jobs_companyidentifier.scheme, jobs_companyidentifier.value. The report-and-remove branch at lines 48-56 is never reached.
2. One company carries two Other identifiers, VAT/pt123 and vat/PT123, which the old unique_other_identifier_per_company (company, scheme, label, value) allowed. The data step skips both, and the AddConstraint at lines 89-99 fails with IntegrityError: UNIQUE constraint failed: index 'unique_other_identifier_per_company'.
Both were reproduced by migrating jobs to 0013, inserting the rows through the historical models and migrating to 0014. A single lowercase q95 with no partner folds to Q95 correctly.
Why it matters: the upgrade stops, and stays stopped, for exactly the accounts #211 was about: rows saved before a scheme folded its case, and Other values typed in two cases. The transaction rolls back, so no data is lost, but the operator is left on the older release until somebody edits the table by hand. #211 is closed as if this had been handled. Instances already past 0014 are unaffected.
Fix: move both RemoveConstraint operations ahead of the RunPython, so the data step runs with no case-sensitive unique constraint in place, and compute the duplicate key before writing: fold, look the key up in seen, delete and report a duplicate, and save only a row that survives. Give Other rows the same treatment keyed on (row.company_id, row.label.casefold(), row.value.casefold()), keeping the value as typed and deleting (and naming on the console) only a second identical-but-for-case row on the same company, so the expression constraint can always be created. Editing the migration is safe: an instance past 0014 never runs it again.

Done when

A new tests/test_migration_identifier_case.py migrates jobs to 0013_company_kind with MigrationExecutor, inserts Q95 and q95 for one owner on two companies and two Other identifiers differing only in case on one company, migrates to 0014, and asserts that the migration completes, one Wikidata row Q95 remains, and one of the two Other rows remains. It fails today with IntegrityError.

Related to #211 (closed), whose data migration this is.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the data migrations and running jobs 0014 forward with `MigrationExecutor` on SQLite over seeded rows. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. `jobs/migrations/0014_remove_companyidentifier_one_company_per_identifier_per_owner_and_more.py` exists to fold stored identifiers to their scheme's case and to report, rather than refuse, any pair that collides once folded (#211). It cannot do either for the rows it was written for. It saves each folded value before it checks for a duplicate, while the old case-sensitive constraint is still in place, so the first collision raises `IntegrityError` and the migration rolls back. *Other* identifiers are not folded or de-duplicated at all, yet the new constraint compares them case-insensitively, so two of them that differ only in case make `AddConstraint` fail. Either way an instance holding such rows cannot migrate past jobs 0014. - **Where:** `jobs/migrations/0014_remove_companyidentifier_one_company_per_identifier_per_owner_and_more.py`: `fold_and_report` saves at line 43 (`row.save(update_fields=["value"])`) and only builds the duplicate key at line 47; lines 45-46 skip every `other` row; the operations list runs `RunPython(fold_and_report, ...)` at line 70, before `RemoveConstraint("one_company_per_identifier_per_owner")` at lines 71-74, and adds the new `unique_other_identifier_per_company` on `Lower("label")`, `Lower("value")` at lines 89-99. - **What happens:** 1. One account has Wikidata `Q95` on one company and `q95` on another, which the case-sensitive constraint from `0005_company_identifiers.py` (`owner`, `scheme`, `value`, where scheme is not `other`) allowed. Whichever row comes second by `pk`, folding makes it equal to the first while that constraint still exists, and line 43 raises `IntegrityError: UNIQUE constraint failed: jobs_companyidentifier.owner_id, jobs_companyidentifier.scheme, jobs_companyidentifier.value`. The report-and-remove branch at lines 48-56 is never reached. 2. One company carries two *Other* identifiers, `VAT`/`pt123` and `vat`/`PT123`, which the old `unique_other_identifier_per_company` (`company`, `scheme`, `label`, `value`) allowed. The data step skips both, and the `AddConstraint` at lines 89-99 fails with `IntegrityError: UNIQUE constraint failed: index 'unique_other_identifier_per_company'`. Both were reproduced by migrating `jobs` to 0013, inserting the rows through the historical models and migrating to 0014. A single lowercase `q95` with no partner folds to `Q95` correctly. - **Why it matters:** the upgrade stops, and stays stopped, for exactly the accounts #211 was about: rows saved before a scheme folded its case, and *Other* values typed in two cases. The transaction rolls back, so no data is lost, but the operator is left on the older release until somebody edits the table by hand. #211 is closed as if this had been handled. Instances already past 0014 are unaffected. - **Fix:** move both `RemoveConstraint` operations ahead of the `RunPython`, so the data step runs with no case-sensitive unique constraint in place, and compute the duplicate key before writing: fold, look the key up in `seen`, delete and report a duplicate, and save only a row that survives. Give *Other* rows the same treatment keyed on `(row.company_id, row.label.casefold(), row.value.casefold())`, keeping the value as typed and deleting (and naming on the console) only a second identical-but-for-case row on the same company, so the expression constraint can always be created. Editing the migration is safe: an instance past 0014 never runs it again. ## Done when - A new `tests/test_migration_identifier_case.py` migrates `jobs` to `0013_company_kind` with `MigrationExecutor`, inserts `Q95` and `q95` for one owner on two companies and two *Other* identifiers differing only in case on one company, migrates to 0014, and asserts that the migration completes, one Wikidata row `Q95` remains, and one of the two *Other* rows remains. It fails today with `IntegrityError`. Related to #211 (closed), whose data migration this is.

tiagoagueda added this to the 0.5.0 milestone

2026-10-01 10:47:44 +00:00

tiagoagueda added the

tier

bug

labels

2026-10-01 10:47:44 +00:00

tiagoagueda referenced this issue from a commit

2026-10-03 02:03:19 +00:00

Let an instance define identifier schemes of its own, in JSON, in a dialog

tiagoagueda referenced this issue from a commit

2026-10-04 10:04:56 +00:00