Re: SQL:2011 application time

On 7/23/24 09:08, Paul Jungwirth wrote:
> One tempting alternative though is to let exclusion constraints do the not-empty check, instead of
> putting it in the executor. It would be an extra check we do only when the constraint has
> pg_constraint.conperiod. Then we don't need to add & maintain pg_class.relwithoutoverlaps, and we don't
> need a relcache change, and we don't need so much extra code to check existing rows when you add the
> constraint. It doesn't use the existing available exclusion constraint functionality, but if we're
> willing to extend the executor to know about WITHOUT OVERLAPS, I guess we could teach exclusion
> constraints about it instead. Doing the check there does seem to have better locality with the feature.
> So I think I will try that out as well.

Here is a patch moving the not-empty check into check_exclusion_or_unique_constraint. That is a more
logical place for it than ExecConstraints, since WITHOUT OVERLAPS is part of the index constraint
(not a CHECK constraint). At that point we've already looked up all the information we need. So
there is no extra cost for non-temporal tables, and no need to change pg_class or add to the
relcache. Also putting it there means we don't need any extra code to enforce non-empties when we
build the index or do anything else with it.

I think this is the nicest solution we can expect. It is even cleaner than the &&& ideas. So
hopefully this gets us back to where we were when we decided to commit PKs & FKs to v17.

As before, I've left the nonempty check as a separate patch to make reviewing easier, but when
committing I would squash it with the PK patch.

Rebased to 05faf06e9c.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com