Moving a table into the default schema drops and re-creates every complex index and constraint on it (PostgreSQL: fails with 42P01) #185

Found by a sweep of release/5.x @ d28c938 for base operations the differs do not account for (the shape of #182); reproduced in scratch MigrationHarness.SqlServerDiff / NpgsqlDiff tests and applied to PostgreSQL 18 (Testcontainers).

Problem

Moving a table into the default schema drops and re-creates every complex index, exclusion constraint, temporal constraint and temporal foreign key on it. A move between two named schemas, or from the default schema into a named one, is handled correctly.

EF Core's RenameTableOperation always carries the target schema in NewSchema, and null there means the default schema. EF Core's SQL Server generator reads it that way (ALTER SCHEMA <SCHEMA_NAME()> TRANSFER). The differs read null as "unchanged", so a source on north.bookings is compared under north.bookings against a target on bookings, and nothing matches:

• CustomMigrationsModelDiffer.cs:260: rename.NewSchema ?? rename.Schema (complex indexes)
• CustomMigrationsModelDiffer.cs:139: the same in SourceColumnRenames, so the A column rename drops and re-creates every complex index and exclusion constraint on the column #182 fix misses column renames on such a table
• NpgsqlComplexIndexMigrationsModelDiffer.cs:1078 (BuildRenamedTables): exclusion and temporal constraints, temporal foreign keys

SqliteRebuiltTables (CustomMigrationsModelDiffer.cs:469) already reads rename.NewSchema as-is.

Model: bookings with ToTable("bookings", "north") → ToTable("bookings"), with no HasDefaultSchema. It has a unique property-level HasComplexIndex on Contact.Email and a composite ix_bookings_code_email.

SQL Server, rendered with the target model:

DROP INDEX [IX_bookings_Contact_Email] ON [north].[bookings];
DROP INDEX [ix_bookings_code_email] ON [north].[bookings];
DECLARE @defaultSchema nvarchar(max) = QUOTENAME(SCHEMA_NAME());
EXEC(N'ALTER SCHEMA ' + @defaultSchema + N' TRANSFER [north].[bookings];');
CREATE UNIQUE INDEX [IX_bookings_Contact_Email] ON [bookings] ([Contact_Email]) WHERE [Contact_Email] IS NOT NULL;
CREATE INDEX [ix_bookings_code_email] ON [bookings] ([Code], [Contact_Email]);

ALTER SCHEMA … TRANSFER keeps the table's indexes, so both rebuilds are needless: an offline index build on a schema move.

PostgreSQL fails outright. Npgsql 10 renders nothing for a RenameTableOperation whose NewSchema is null (NpgsqlMigrationsSqlGenerator.cs L890 at v10.0.0), so the table stays in north. Without this package that is Npgsql's own silent no-op. With it, the drops run and the creates name a table that is not there:

ALTER TABLE "north"."scratch_moved" DROP CONSTRAINT IF EXISTS "ex_moved_room";
DROP INDEX north.ix_moved_code_email;
CREATE INDEX ix_moved_code_email ON scratch_moved ("Code", "Contact_Email");
-- 42P01: relation "scratch_moved" does not exist

With a temporal constraint on the table it also issues DropUniqueConstraint and ADD CONSTRAINT … WITHOUT OVERLAPS, for the same reason.

Change

Read NewSchema as EF Core writes it: the target schema, null for the default. In all three places, use (rename.NewName ?? rename.Name, rename.NewSchema). A pair that matches after normalization produces no operation, and on PostgreSQL the migration then does what Npgsql does without the package.

This changes what an existing model scaffolds, so it ships in a minor with a ### Changed answers row and a docs/upgrading.md entry.

Tests

• SqlServerDiff and NpgsqlDiff tests for the model above: only EnsureSchema/RenameTable, with no index or constraint operations. Include an exclusion constraint, a temporal constraint and a temporal foreign key whose principal moves.
• A column renamed on the same table in the same migration: still only the RenameColumnOperation.
• The moves that already work (north → south, default → north), so the fix does not turn them around.