Re: Constraint merge and not valid status

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Well, on second thought, I'm no longer sure that this approach makes
> sense. I mean, it's obviously wrong for constraint-merging to change
> the validity marking on a constraint, but that does not necessarily
> imply that we shouldn't merge the constraints, does it? I see the
> downthread discussion saying that it's a problem if the parent's
> constraint is marked valid while the child's constraint isn't, but I
> don't quite understand why that situation would cause trouble. In
> other words, I see that the current situation is not good, but I'm not
> sure I understand what's going on here well enough to be confident
> that any of the proposed fixes are correct.

The point I think is that a valid CHECK constraint on a parent table
should imply that all rows fetched by "SELECT * FROM table" will pass
the check. Therefore, a situation with valid parent constraint and
not-valid child constraint is bad because it might allow some rows
fetched by an inheritance scan to not pass the check. Quite aside from
any user-level expectations, this could break planner optimizations.

I'd be satisfied with the upthread proposal "throw error if the child has
a matching not-valid constraint". Allowing the merge if both child
and new parent constraint are not-valid is all right as an extension,
but it seems like a feature with a mighty narrow use case, and I would
not go far out of our way to support it. Causing the command to not
merge but instead create a new duplicate child constraint seems like a
seriously bad idea (though I'm not sure that anyone was advocating for
that).

regards, tom lane