Repository navigation
Native temporal keys over ValueRanges columns on the 11 line: implement INpgsqlRangeTypeMapping, document WithoutOverlaps and WithPeriod #150
Description
Scope widened 2026-10-19
The measurement (comment below) showed that Npgsql 11 refuses this package's columns as they are, because its validator checks the type mapping's class or interface, not the store type name.
On Npgsql rc.2, implementing INpgsqlRangeTypeMapping on the mappings makes the native API accept them. That is now part of this issue, so it is an enhancement, not only documentation.
Change
- Floors:
Npgsql.EntityFrameworkCore.PostgreSQL(and its NodaTime plugin) move to 11.0.0-rc.2, and EF Core Design in the test projects to rc.2. The interface doesn't exist on rc.1.1. This takes the floor bullet of Publish 11.0.0-preview.1 on the .NET 11 GA SDK and Npgsql's provider rc.3 #332. ValueRangeTypeMapping,ValueRangeSetTypeMappingand the twoYearMonthmappings implementINpgsqlRangeTypeMappingexplicitly.CreateCurrentValueComparerreturnsnull, because every range type andRangeSetis alreadyIComparable<T>in PostgreSQL's order.docs/efcore.mdgets a section on native temporal keys on the 11 line:HasKey(…).WithoutOverlaps(),WithPeriod()foreign keys,SetPostgresVersion(18, 0),btree_gist, and theDbUpdateExceptionshapes.
Done when
Measured on PostgreSQL 18 in the integration suite:
- a
DateRangekey withWITHOUT OVERLAPS, aRangeSet(multirange) key and aTimeRangekey over the customtimerange - the rejected overlap (23P01) and the accepted adjacent pair
- a
PERIODforeign key and its violation (23503) - the PostgreSQL 18 requirement (
SetPostgresVersion) andbtree_gist - migrations scaffolding the clause
- a test that removes the interface and shows the refusal returns
claims.jsonholds the promises
Original issue
The second half of CaffeinatedCoder/EFCore.ComplexIndexes#97, split out because it belongs to the 11 line only. Npgsql EF 11 adds HasKey(…).WithoutOverlaps() and HasOne(…).WithMany(…).HasForeignKey(…).WithPeriod() (release notes 11.0, "Support for PostgreSQL 18 temporal constraints"), documented with NpgsqlRange<T>. The provider may check the key column's store type for a range; this package's mappings carry the store type name, so it should pass. It must be measured.
On the 10.x line the same section points to EFCore.ComplexIndexes.PostgreSQL's HasTemporalConstraint (#97). On 11 the native API is preferred, and ComplexIndexes' successor SchemaKit remains for exclusion constraints and GiST indexes over range columns (CaffeinatedCoder/CodoMetis.EFCore.SchemaKit#1).
Activity
CaffeinatedCoder commentedon Oct 19, 2026
Added 2026-10-19: this measurement now decides CaffeinatedCoder/CodoMetis.EFCore.SchemaKit#3. If Npgsql 11's
HasWithoutOverlaps/HasPeriodrefuses a period that isn't anNpgsqlRange<T>, then SchemaKit (the EF Core 11 successor of EFCore.ComplexIndexes) keepsHasTemporalConstraintfor ValueRanges' types. Otherwise it leaves temporal constraints to the platform, and #333 moves claims E48–E52 to the tests written here. Measure it early; it's on the critical path of both repositories.CaffeinatedCoder commentedon Oct 19, 2026
Measured 2026-10-19 on PostgreSQL 18 (
postgres:18-alpine)Short answer: Npgsql 11 refuses ValueRanges columns as they are today, on rc.1.1 and on rc.2. On rc.2 a one-line change in ValueRanges makes the native API accept all three types, the multirange included.
What Npgsql checks
The native API is
HasKey(…).WithoutOverlaps()andHasOne(…).WithMany(…).HasForeignKey(…).WithPeriod(). The issue'sHasWithoutOverlaps/HasPerioddon't exist.NpgsqlModelValidatorchecks the last key property's type mapping, not the store type name:typeMapping is not NpgsqlRangeTypeMapping, which accepts Npgsql's own range mappings only.typeMapping is not INpgsqlRangeTypeMapping, an interface introduced for NodaTime's ranges (Support NodaTime ranges in temporal constraints npgsql/efcore.pg#3975). It has one member,IComparer? CreateCurrentValueComparer(Type), which supplies a key comparer for types that aren'tIComparable.The check also requires
SetPostgresVersion(18, 0). Without it the model throwsWITHOUT OVERLAPS … requires PostgreSQL 18.0 or later.Results
INpgsqlRangeTypeMappingon the ValueRanges mappingsNpgsqlRange<DateOnly>(Npgsql's own, baseline)PRIMARY KEY ("RoomId", "Period" WITHOUT OVERLAPS); overlap → 23P01DateRange(daterange)InvalidOperationException: WITHOUT OVERLAPS … requires the last column to be a PostgreSQL range type …, but property 'Period' has type 'DateRange'DbUpdateException/ 23P01 onPK_Slots; rows read backTimeRange(customtimerange)btree_gist); same resultsRangeSet<DateRange, DateOnly>(datemultirange)NpgsqlRange<DateOnly>[](Npgsql's own multirange)PERIODFK overDateRangePERIOD on foreign key … requires the last column to be a PostgreSQL range typeFOREIGN KEY ("RoomId", PERIOD "Period") REFERENCES "Principals" ("RoomId", PERIOD "Period"); a period covered by two adjacent principals is accepted; an uncovered one → 23503PostgreSQL 18 itself accepts all of it. Hand-written
PRIMARY KEY (room, days WITHOUT OVERLAPS)overdatemultirangeand overtimerangerejects overlaps with 23P01.A table created that way, with rows written through EF using an ordinary key over
DateRange, rejects the overlap too (23P01). That is the workaround on rc.1.1.The change that makes it work
ValueRangeTypeMappingandValueRangeSetTypeMappingimplementINpgsqlRangeTypeMappingexplicitly and returnnullfromCreateCurrentValueComparer. Every range type andRangeSetis alreadyIComparable<T>in PostgreSQL's order, so EF's default comparer applies and Npgsql's convention adds nothing.The interface is only read in three places on rc.2: the validator and the two conventions that set that comparer. So the change alters no query translation. It needs the Npgsql floor at rc.2 (#332).
Consequence for CaffeinatedCoder/CodoMetis.EFCore.SchemaKit#3
With this change in ValueRanges 11, Npgsql's native temporal keys cover ValueRanges'
DateRange,TimeRangeandRangeSet(multirange) columns, and theirPERIODforeign keys. SchemaKit doesn't need to keepHasTemporalConstraintfor ValueRanges' sake.It still needs it for an
NpgsqlRange<T>[]multirange period, which Npgsql refuses. Whether that is reason enough is CaffeinatedCoder/CodoMetis.EFCore.SchemaKit#3's call.[-]Document native temporal constraints over ValueRanges columns on the 11 line: HasWithoutOverlaps and HasPeriod[/-][+]Native temporal keys over ValueRanges columns on the 11 line: implement INpgsqlRangeTypeMapping, document WithoutOverlaps and WithPeriod[/+]