Re: Adding a nullable DOMAIN column w/ CHECK

On Sun, Sep 07, 2014 at 01:06:04PM -0400, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > On Sat, Sep 06, 2014 at 02:01:32AM +0200, Marko Tiikkaja wrote:
> >> To do this optimization we do have to assume that CHECKs in
> >> DOMAINs are at least STABLE, but I don't see that as a problem;
> >> those should be IMMUTABLE anyway, I think.
>
> > The system has such assumptions already.
>
> What bothers me about this general approach is that the check condition is
> evaluated against a null whether or not there are any rows in the table.
> This means that side-effects of the check condition would happen even when
> they did not happen in the previous implementation. Maybe that's all
> right, but to say it's all right you must make a stronger form of the
> "check conditions are immutable" assumption than we make elsewhere,
> ie not just that its result won't change but that it has no visible
> evaluation side-effects. So I disagree with Noah's conclusion that we're
> already assuming this.

Our assumption that domain CHECK constraints are STABLE doesn't grant
unlimited freedom to evaluate them, indeed.

> As an example, if the check condition is such that it actually throws
> an error (not just returns false) for null input, the ALTER command
> would fail outright, whereas it would previously have succeeded as long
> as the table is empty. (BTW, should not the patch be checking for a false
> result?)
>
> This objection could be met by doing a precheck to verify that the table
> contains at least one live row. That's pretty ugly and personally I'm not
> sure it's necessary, but I think there's room to argue that it is.

Yes; I doubt one could justify failing on an empty table as though it had been
a one-row table. I see a couple ways we could avoid the I/O and complexity:

1) If contain_leaky_functions() approves every constraint expression, test the
constraints once, and we're done. Otherwise, proceed as we do today.

2) Test the constraints in a subtransaction. If the subtransaction commits,
we're done. Otherwise, proceed as we do today.

The more complexity you accept, the more cases you optimize; where best to
draw the line is not clear to me at this point.

Thanks,
nm