[Bug]: The migration fails on CockroachDB (>= 22.1)

[dshkuratov]

Bug Description

-- Add foreign key constraint on namespace_key column referencing key column of namespaces table
ALTER TABLE rules ADD FOREIGN KEY (namespace_key) REFERENCES namespaces(key) ON DELETE CASCADE;
ALTER TABLE rules ADD FOREIGN KEY (namespace_key, flag_key) REFERENCES flags(namespace_key, key) ON DELETE CASCADE;
ALTER TABLE rules ADD FOREIGN KEY (namespace_key, segment_key) REFERENCES segments(namespace_key, key) ON DELETE CASCADE; (details: ERROR: unimplemented: cannot perform a primary key change on flags with other schema changes on flags in the same transaction (SQLSTATE 0A000))

The migration fails on all CockroachDB versions later than latest-v21.2.

Version Info

    _________       __
   / ____/ (_)___  / /_
  / /_  / / / __ \/ __/
 / __/ / / / /_/ / /_
/_/   /_/_/ .___/\__/
         /_/

Version: v1.47.1
Commit: 96956c7c69852cc77e8b16a1b103bdf7a2c7f2b3
Build Date: 2024-08-02T13:35:36Z
Go Version: go1.22.5
OS/Arch: linux/amd64

Search

• [X] I searched for other open and closed issues before opening this

Steps to Reproduce

cd examples/database/cockroachdb/

change docker-compose.yml image: cockroachdb/cockroach:latest-v21.2 to image: cockroachdb/cockroach:latest-v23.2

run docker compose up

Expected Behavior

the migration completes successfully

Additional Context

No response

[GeorgeMac]

Thanks for raising this @dshkuratov 🙏

This seems to be relevant: https://www.cockroachlabs.com/docs/stable/known-limitations#no-online-schema-changes-if-primary-key-change-in-progress (for those debugging this issue).