PostgreSQL: changing a generated column's expression silently drops every complex index and constraint on it #184

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

Problem

On PostgreSQL, changing a generated column's expression silently drops every complex index, exclusion constraint and temporal constraint that reads the column. The migration applies cleanly, the snapshot still lists them, and the next migrations add scaffolds nothing.

EF Core emits one AlterColumnOperation whose ComputedColumnSql (or IsStored) changed. PostgreSQL cannot alter a generation expression in place, so Npgsql's generator renders it as DROP COLUMN plus ADD COLUMN (NpgsqlMigrationsSqlGenerator.cs L382–425 at v10.0.0), and then re-creates EF Core's own indexes on the column from the model (RecreateIndexes). DROP COLUMN takes every index and table constraint that reads the column with it. Nothing re-creates this package's: the descriptors are unchanged, so the differs emit nothing.

• CustomMigrationsModelDiffer.GetDifferences re-creates an unchanged index only when RequiresIndexRecreation says so (CustomMigrationsModelDiffer.cs:300, InTheWay). The PostgreSQL satellite does not override it; only SQL Server does, for ALTER COLUMN.
• ApplyExclusionConstraints and ApplyTemporalConstraints (NpgsqlComplexIndexMigrationsModelDiffer.cs:896, :673) have no in-the-way rule at all.

A generated range column is the usual way to give an exclusion or temporal constraint a period over two timestamp columns, so the constraint this package exists for is the one most exposed.

Model (scratch_bookings): Search is HasComputedColumnSql("lower(\"Code\")", stored: true), During is HasComputedColumnSql("tstzrange(\"Start\", \"End\")", stored: true), with a native HasIndex on a second generated column for comparison, and

b.HasComplexCompositeIndex(x => new { x.Search, x.Contact.Email }, indexName: "ix_scratch_search_email");
b.HasExclusionConstraint(x => x.RoomId, x => x.During, name: "ex_scratch_room");
b.HasTemporalConstraint(x => x.Code, x => x.During, name: "tc_scratch_code");

Change the expressions to upper("Code") and tstzrange("Start", "End", '[]'). Rendered with the target model, as database update does:

ALTER TABLE scratch_bookings DROP COLUMN "Search";
ALTER TABLE scratch_bookings ADD "Search" text GENERATED ALWAYS AS (upper("Code")) STORED;
ALTER TABLE scratch_bookings DROP COLUMN "Native";
ALTER TABLE scratch_bookings ADD "Native" text GENERATED ALWAYS AS (upper("Code")) STORED;
CREATE INDEX ix_native ON scratch_bookings ("Native");
ALTER TABLE scratch_bookings DROP COLUMN "During";
ALTER TABLE scratch_bookings ADD "During" tstzrange GENERATED ALWAYS AS (tstzrange("Start", "End", '[]')) STORED NOT NULL;

On PostgreSQL 18, before: ix_native, ix_scratch_search_email, ex_scratch_room and tc_scratch_code. After: ix_native only. A second diff of the target against itself returns no operations.

The same root cause also covers (traced, not run): a temporal foreign key on the dependent table, dropped with the column like any table constraint; a temporal foreign key whose principal period is the generated column, where DROP COLUMN fails at apply instead (2BP01: other objects depend on it); a column turned into a generated one or back (ComputedColumnSql null on one side); and HasGeneratedTsVectorColumn with a changed configuration, which Npgsql renders through the same branch, under a complex GIN index.

SQL Server is not affected: its satellite already re-creates an index around every ALTER COLUMN on a column it reads. SQLite rebuilds the table and is covered by #95.

Change

Treat an AlterColumnOperation whose ComputedColumnSql or IsStored changed as in the way on PostgreSQL. The RequiresIndexRecreation override in the Npgsql differ covers complex indexes. Exclusion constraints, temporal constraints and temporal foreign keys need the same rule: drop before the base operations, add after. A temporal foreign key goes too when its principal constraint is rebuilt this way, as DependsOnRebuiltConstraint already does for renames.

This changes what an existing model scaffolds, so it ships in a minor with a ### Changed answers row and a docs/upgrading.md entry. A database that already ran such a migration has lost the objects. The first migrations add after upgrading will not bring them back either, since the snapshot still lists them, so the upgrading entry should say how to find and re-create them.

Tests

• A MigrationHarness.NpgsqlDiff test for the model above: each complex index, exclusion and temporal constraint on the column is dropped before the AlterColumnOperation and created after it; one on another column is untouched.
• The same for a column becoming generated, and for a temporal foreign key on either side.
• A PostgresIntegrationTests case that applies the change with the target model and reads the index and constraints back from pg_indexes and pg_constraint.