Re: CHECK Constraint Deferrable

On 10/2/23 21:25, Tom Lane wrote:
> Himanshu Upadhyaya <upadhyaya(dot)himanshu(at)gmail(dot)com> writes:
>> V3 patch attached.
>
> Sorry for not weighing in on this before, but ... is this a feature
> we want at all?

For standards conformance, I vote yes.

> We are very clear in the existing docs that CHECK
> conditions must be immutable [1], and that's not something we can
> easily relax because if they are not then it's unclear when we need
> to recheck them to ensure they stay satisfied.

That is what the *user* documentation says, but we both know it isn't true.

Here is a short conversation you and I had about five years ago where
you defended the non-immutability of CHECK constraints:
https://www.postgresql.org/message-id/flat/12539.1544107316%40sss.pgh.pa.us

> But here we have a
> feature whose only possible use is with constraints that *aren't*
> immutable; else we might as well just check them immediately.

I disagree with this. The whole point of deferring constraints is to be
able to do some cleanup before the consistency is checked.

> So that gives rise to a bunch of subtle questions about exactly what
> properties a user-written constraint would need to have to guarantee
> sane semantics given this implementation. Can we define what those
> properties are, or what the ensuing semantic guarantees are exactly?
> Can we explain those things clearly enough that the average user would
> have a shot at writing a valid deferred constraint?

A trivial example is CHECK (c IS NOT NULL) which, according to the
standard, is the only way to check for such a condition. The NOT NULL
syntax is explicitly translated to that by 11.4 <column definition> SR
17.a. We implement it a bit differently, but that does not negate the
usefulness of being able to defer it. In fact, all of the work Álvaro
is currently doing is mainly (or even fully) to be able to defer such a
constraint.

> Is a deferred
> constraint having those properties likely to be actually useful?

I believe the answer is yes.
--
Vik Fearing