A raw DELETE of a campaign fails on quest_objectives (two cascade paths) #943

New issue

Closed

opened 2026-09-29 19:46:18 +00:00 by claude-bot · 1 comment

claude-bot commented

2026-09-29 19:46:18 +00:00

Collaborator

Found while extending the demo seed (lanes 8 and 10).

The failure: DELETE FROM campaigns WHERE id = … fails with:

insert or update on table "quest_objectives" violates foreign key constraint "quest_objectives_lore_entry_id_fkey"

It happens when an objective has a done_session_id. The row is reached by two cascade paths in the same statement:

CASCADE, through quest_details / lore_entries;
SET NULL, through sessions (done_session_id).

The SET NULL update then references a lore entry that is already being deleted.

Impact: the app's own purge_campaign deletes through the ORM in order, and passes. Only a raw SQL delete hits this: an operator's manual cleanup, or a future bulk purge. The demo seed works around it (engine._delete_lane8_first).

Fix options:

make quest_objectives_lore_entry_id_fkey DEFERRABLE INITIALLY DEFERRED; or
make done_session_id CASCADE-safe, by ordering the deletes or clearing it in a BEFORE DELETE trigger.

A migration is needed either way. Low priority, not a v4.5.0 blocker.

Found while extending the demo seed (lanes 8 and 10). **The failure:** `DELETE FROM campaigns WHERE id = …` fails with: > insert or update on table "quest_objectives" violates foreign key constraint "quest_objectives_lore_entry_id_fkey" It happens when an objective has a `done_session_id`. The row is reached by two cascade paths in the same statement: - CASCADE, through `quest_details` / `lore_entries`; - SET NULL, through `sessions` (`done_session_id`). The SET NULL update then references a lore entry that is already being deleted. **Impact:** the app's own `purge_campaign` deletes through the ORM in order, and passes. Only a raw SQL delete hits this: an operator's manual cleanup, or a future bulk purge. The demo seed works around it (`engine._delete_lane8_first`). **Fix options:** - make `quest_objectives_lore_entry_id_fkey` `DEFERRABLE INITIALLY DEFERRED`; or - make `done_session_id` CASCADE-safe, by ordering the deletes or clearing it in a BEFORE DELETE trigger. A migration is needed either way. Low priority, not a v4.5.0 blocker.

claude-bot referenced this issue

2026-09-29 22:07:01 +00:00

Demo seed: the wiki's lane 8 pages and lane 10's bot tests (#734, #736) #946

claude-bot referenced this issue from a pull request that will close it,

2026-09-30 06:40:52 +00:00

A raw campaign DELETE works: quest_objectives' FK is deferred (#943) #957

claude-bot referenced this issue from a commit

2026-09-30 07:02:04 +00:00

fix(webapp): defer quest_objectives lore_entry FK so raw campaign delete works (#943)

claude-bot referenced this issue from a commit

2026-09-30 07:02:04 +00:00

Merge PR #957: a raw campaign DELETE works — quest_objectives' FK is deferred (#943)

claude-bot closed this issue

2026-09-30 07:02:04 +00:00

claude-bot commented

2026-09-30 07:02:05 +00:00

Author

Collaborator

Fixed in #957 (merged 2026-09-30, green CI).

The cause: the FK is quest_objectives.lore_entry_id → quest_details.lore_entry_id. Migration a9430d1e2f3b makes it DEFERRABLE INITIALLY DEFERRED, with the same column, target and ON DELETE CASCADE, and downgrade restores it. Deferring that one constraint was enough.
The seed: its _delete_lane8_first workaround is removed, and delete_demo's single DELETE FROM campaigns now covers everything, the GM copy included.
The test: a regression test reproduces the raw delete, and it fails on the old constraint.

Fixed in #957 (merged 2026-09-30, green CI). - **The cause:** the FK is `quest_objectives.lore_entry_id → quest_details.lore_entry_id`. Migration `a9430d1e2f3b` makes it `DEFERRABLE INITIALLY DEFERRED`, with the same column, target and `ON DELETE CASCADE`, and downgrade restores it. Deferring that one constraint was enough. - **The seed:** its `_delete_lane8_first` workaround is removed, and `delete_demo`'s single `DELETE FROM campaigns` now covers everything, the GM copy included. - **The test:** a regression test reproduces the raw delete, and it fails on the old constraint.