Re: FK constraints "NOT VALID" by default?

On Thu, Mar 17, 2011 at 5:32 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Mar 17, 2011 at 5:29 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>> Is this really intended?
>>
>> I sure hope not.
>
> That's a bug.  Not sure if it's a psql bug or a backend bug, but it's
> definitely a bug.

It's a backend bug. Prior to Simon's patch, there was an existing
skip_validation flag in the Constraint node that indicated whether or
not a validation pass was necessary - in a newly created table, for
example, we know that it's NOT necessary, because the table can't
contain any rows (and therefore there can't be any rows that violate
the constraint). The patch tries to make the very same flag indicate
whether the user wants the constraint to be added with the NOT VALID
attribute, which of course falls over because the Boolean only has two
values and there are three cases (validate it, don't validate it but
do mark it valid because the table is guaranteed to be empty, don't
validate it and mark it not valid).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company