Re: Adding a nullable DOMAIN column w/ CHECK - Mailing list pgsql-hackers

From Noah Misch
Subject Re: Adding a nullable DOMAIN column w/ CHECK
Date
Msg-id 20140907192334.GI1066341@tornado.leadboat.com
Whole thread Raw
In response to Re: Adding a nullable DOMAIN column w/ CHECK  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Adding a nullable DOMAIN column w/ CHECK
List pgsql-hackers
On Sun, Sep 07, 2014 at 01:06:04PM -0400, Tom Lane wrote:
> Noah Misch <noah@leadboat.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



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Built-in binning functions
Next
From: Tom Lane
Date:
Subject: Re: Adding a nullable DOMAIN column w/ CHECK