Re: Proposal: Add a UNIQUE NOT ENFORCED constraint

On 07.01.26 14:21, Matthias van de Meent wrote:
>> In many cases, the idiomatic/generally best way to write a query
>> requires a uniqueness check (a SELECT WHERE ... = ANY()/IN or really
>> any semi-join when optimized to an inner join, UNION, etc), meaning a
>> Unique/HashAggregate node will be added, increasing overhead unless
>> there is an explicit unique constraint. An unenforced unique
>> constraint would allow developers to use their knowledge of the
>> data/previous validation procedures to eliminate the extra node.
> Unenforced constraints should be considered invalid (how would we
> prove they're valid?), and because the planner is not allowed to rely
> on invalid constraints, why would the planner be allowed to remove
> these nodes for unenforced-and-therefore-invalid unique constraints?
> 
> It's quite likely that the query output would be incorrect when we
> expected the data to be unique for unenforced constraints, while the
> data in the table isn't actually unique for that unenforced unique
> constraint; and knowingly giving incorrect results is not something I
> can agree to.

Some other SQL implementations have optional implementation-specific 
modes on top of NOT ENFORCED that mean, the constraint is not enforced, 
but you can assume it holds for query planning purposes.  This is 
probably what the thinking here was, but that is not how PostgreSQL 
currently works, so additional work would need to be done to add this 
additional mode.

There are probably also multiple levels to this.  It's one thing to use 
an unenforced-but-probably-true constraint for things like selectivity 
estimation, where you just get a worse plan if it's not quite true, but 
it's another to assume something is unique when it might not be.  So 
there is some research to be done here.