[discourse] Postgresql migration error from v15 to v16

[markphelps]

Originally posted by KirillZy on Discourse

Hi everyone.

Please help with update Flipt

Planning update Flipt 1.47.0 to 1.57.0

In dev env get migration error:

current migration {“current_version”: 15, “expected_version”: 16}

Error: running migrator running migrations:

migration failed: constraint “rule_segments_namespace_key_segment_key_fkey” of relation “rule_segments” does not exist (column 0) in line 1:

ALTER TABLE rule_segments DROP CONSTRAINT rule_segments_namespace_key_segment_key_fkey;

ALTER TABLE rule_segments ADD CONSTRAINT rule_segments_namespace_key_segment_key_fkey FOREIGN KEY (namespace_key, segment_key) REFERENCES segments (namespace_key, key) ON DELETE RESTRICT;

ALTER TABLE rollout_segment_references DROP CONSTRAINT rollout_segment_references_namespace_key_segment_key_fkey;

ALTER TABLE rollout_segment_references

ADD CONSTRAINT rollout_segment_references_namespace_key_segment_key_fkey FOREIGN KEY (namespace_key, segment_key) REFERENCES segments (namespace_key, key) ON DELETE RESTRICT;

(details: ERROR: constraint “rule_segments_namespace_key_segment_key_fkey” of relation “rule_segments” does not exist (SQLSTATE 42704))

In DB with schema version 15 I have:

Table “public.rule_segments”

Foreign-key constraints:

“rule_segments_namespace_key_fkey” FOREIGN KEY (namespace_key, segment_key) REFERENCES segments(namespace_key, key) ON DELETE CASCADE

“rule_segments_rule_id_fkey” FOREIGN KEY (rule_id) REFERENCES rules(id) ON DELETE CASCADE

Table “public.rollout_segment_references”

Foreign-key constraints:

“rollout_segment_references_namespace_key_fkey” FOREIGN KEY (namespace_key, segment_key) REFERENCES segments(namespace_key, key) ON DELETE CASCADE

“rollout_segment_references_rollout_segment_id_fkey” FOREIGN KEY (rollout_segment_id) REFERENCES rollout_segments(id) ON DELETE CASCADE

Maybe there are some ideas?

[dosubot[bot]]

The migration error happens because the migration script expects a foreign key constraint named rule_segments_namespace_key_segment_key_fkey to exist, but in your current schema (v15), the constraint is named rule_segments_namespace_key_fkey and uses ON DELETE CASCADE instead of ON DELETE RESTRICT. The migration tries to drop and recreate this constraint with a new name and behavior, but fails since the expected constraint name doesn't match what's in your DB.

This mismatch likely comes from changes in how constraints were named and managed in earlier migrations. To resolve this, you can manually drop the existing constraint (rule_segments_namespace_key_fkey) and then re-run the migration so it can create the new one as expected. The same applies to the rollout_segment_references table.

Before making these changes, ensure you have a backup of your database. Then, run:

ALTER TABLE rule_segments DROP CONSTRAINT IF EXISTS rule_segments_namespace_key_fkey;
ALTER TABLE rollout_segment_references DROP CONSTRAINT IF EXISTS rollout_segment_references_namespace_key_fkey;

After that, re-run the migration process. This should allow the migration to proceed and create the new constraints with the correct names and ON DELETE RESTRICT behavior. For more background on this change and its implications, see the discussion and migration scripts here: example, migration script.

If you have existing data that would violate the new ON DELETE RESTRICT constraint (i.e., segments referenced in these tables), you may need to clean up those references before the migration will succeed.

To reply, just mention @dosu.

---

^{How did I do? Good | Irrelevant | Incorrect | Verbose | Hallucination | Report 🐛 | Other}