TPC base with two partial indexes or exclusion constraints over the same parts: no name passes on PostgreSQL or SQLite #167

Found by a pre-release sweep of main @ eff80b4 (lens: new-check-vs-invariant); reproduced in a scratch test. Holds the tag: a model 5.4.1 accepted has no input that passes on 5.5.0, and the fix decides a default-name answer the snapshot then fixes.

Problem

On a TPC base type with derived tables, two declarations over the same parts with different filters can't be named in any way that passes. This affects coexisting partial indexes, and exclusion constraints such as "no overlap among active rows" plus "no overlap among revoked rows". It hits PostgreSQL and SQLite, where index names are unique per schema or database.

• Both named: refused at migrations add by TpcDeclaration.RepeatedName, reached from ValidateUniqueIndexNames (CustomMigrationsModelDiffer.cs:1001-1003) and, for constraints, ValidateNamesAcrossKinds (NpgsqlComplexIndexMigrationsModelDiffer.cs:473). The message says "… each time under the same explicit name … Leave the name out".
• Both unnamed, as that message advises: refused at the declaration by ComplexIndexStorage.AddOrReplace (ComplexIndexStorage.cs:30-37) and NpgsqlExclusionConstraintExtensions.Store (:104). The message says "Two indexes over the same parts (Code) with different filters must both have explicit index names".
• One named and one not: refused at the declaration as well, because the check is d.IndexName is null || definition.IndexName is null.

The only way out is a schema per derived table, and that works on PostgreSQL only. SQL Server passes, since its index names are unique per table.

This is the case #145's fix left open. #145 shortened the default names of copies and kept telling explicit names to leave the name out, but coexisting partials can't leave it out.

Model

modelBuilder.Entity<Booking>(b =>
{
    b.UseTpcMappingStrategy();
    b.ToTable("bookings");
    b.HasComplexIndex(x => x.Code, isUnique: true, filter: "{RoomId} > 0",  indexName: "ix_code_active");
    b.HasComplexIndex(x => x.Code, isUnique: true, filter: "{RoomId} <= 0", indexName: "ix_code_archived");
    // and likewise:
    // b.HasExclusionConstraint(x => x.RoomId, x => x.During, filter: "{Code} <> 'revoked'", name: "ex_active");
    // b.HasExclusionConstraint(x => x.RoomId, x => x.During, filter: "{Code} = 'revoked'",  name: "ex_revoked");
});
modelBuilder.Entity<SpecialBooking>().ToTable("special_bookings");
modelBuilder.Entity<VipBooking>().ToTable("vip_bookings");

Results on eff80b4, from the TpcHierarchyTests contexts:

At v5.4.1 the named form diffs cleanly: both indexes and both constraints are created on bookings only, a scratch test there confirmed it. A consumer with this model can't run the first migrations add after upgrading.

Change

Give the copies of an explicitly named declaration a name per table. One way: keep the explicit name on the declaring type's table and give each derived copy the explicit name plus its table, shortened as #145 shortens default names. Another: let coexisting partials be unnamed on a TPC type and make their default names distinct per filter. Whichever form becomes the answer, RepeatedName's advice has to name it, and the declaration-time rule has to accept it. The answer becomes part of the snapshot, so it is cheapest to decide before 5.5.0 ships.

Test

For the model above, Diff(provider, null, model) gives 6 creates on SQLite and PostgreSQL and 6 EXCLUDE statements, each under a name that is distinct per table. Add a guard that the advised form is accepted at the declaration. verify-the-guard applies to both.