Re: CHECK Constraint Deferrable

On Mon, Oct 9, 2023 at 1:27 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Tue, Oct 3, 2023 at 10:05 AM David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >> The real-world use case, at least for me, is when using an ORM. For
> large object-graphs ORMs have a tendency to INSERT first with NULLs then
> UPDATE the “NOT NULLs” later.
> >>
> >> “Rewrite the ORM” is not an option for most of us…
> >
> > Between this and Vik comment it sounds like we should probably require a
> patch in this area to solve both the not null and check constraint deferral
> omissions then, not just one of them (alternatively, let’s solve the not
> null one first).
>
> I have a couple of problems with this comment:
>
> 1. I don't know which of Vik's comments you're talking about here, but
> it seems to me that Vik is generally in favor of this feature, so I'm
> a bit surprised to hear that one of his comments led you to think that
> it should be burdened with additional requirements.
>

Specifically, Vik commented that the standard requires implementing NOT
NULL as a check constraint and thus needs to allow for deferrable check
constraints in order for not null checks to be deferrable. I agree fully
that deferring a not null check makes for an excellent use case. But we've
also decided to make NOT NULL its own thing, contrary to the standard.
Thus my understanding for why this behavior is standard mandated is that it
is to allow for deferrable not null constraints and thus our goal should be
to make our not null constraints deferrable.

The only other example case of wanting a deferrable check constraint
involved the usage of a function that we expressly prohibit as a check
constraint. The argument, which I weakly support, is that if our adding
deferrable check constraints increases the frequency of such functions
being created and used by our users, then we should continue to prohibit
such deferrability and require those users to properly implement triggers
which can then be deferred. With a deferrable not null constraint any
other reasonable check constraints can simply evaluate to null during the
period where they should be deferred - because their column inputs are
deferred nulls - and then we be fully evaluated when the inputs ultimately
end up non-null.

2. I don't think it's a good idea for the same patch to try to solve
> two problems unless they are so closely related that solving one
> without solving the other is not sensible.

A NOT NULL constraint apparently is just a special case of a check
constraint which seems closely related enough to match your definition.

But I guess you are right, I was trying to say no to this patch, and yes to
the not null deferral idea, without being so explicit and final about it.

While the coders are welcome to work on whatever they wish, the effort
spent on this just doesn't seem that valuable compared to what is already
in the queue being worked on needing reviews and commits. I can live with a
gap in our standards conformance here since I haven't observed any uses
cases that are impossible to accomplish except by adding this specific
feature which would only cover NOT NULL constraints if the syntactical form
for creating them were not used (which I suppose if we fail to provide NOT
NULL DEFERRABLE that would argue for at least giving this work-around...)

David J.