Re: creating CHECK constraints as NOT VALID

On Sat, Jun 18, 2011 at 2:57 AM, Jaime Casanova <jaime(at)2ndquadrant(dot)com> wrote:
>> looks good to me... at least it compiles, and function as i would expect...
>> tomorrow i will read the code more carefully and look at the docs, but
>> probably this is just fine to be commited...
>
> I think that this paragraph is confusing, but not being an natural
> english speaker i will let others give their opinion here:
> !       If NOT VALID is not specified,
> !       the command will only succeed if all columns using the
> !       domain satisfy the new constraint.
> !       The constraint is going to be enforced on new data inserted into columns
> !       using the domain in all cases, regardless of <literal>NOT VALID</>.
> !       <literal>NOT VALID</> is only accepted for <literal>CHECK</>
> constraints.

I agree. That's pretty contorted. How about something like this:

When a new constraint is added to a domain, all columns using that
domain will be checked against the newly added constraint. These
checks can be suppressed by adding the new constraint using the NOT
VALID option; the constraint can later be made valid using ALTER
DOMAIN .. VALIDATE CONSTRAINT. Newly inserted or updated rows are
always checked against all constraints, even those marked NOT VALID.

In lieu of:

<command>ALTER DOMAIN</command> conforms to the <acronym>SQL</acronym>
standard,
! except for the <literal>OWNER</>, <literal>SET SCHEMA</> and
! <literal>VALIDATE CONSTRAINT</> variants,
! as well as the <literal>NOT VALID</> clause of the <literal>ADD
CONSTRAINT</> variant,
which are <productname>PostgreSQL</productname> extensions.
</para>

I suggest: ALTER DOMAIN conforms to the SQL standard, except for the
OWNER, SET SCHEMA, and VALIDATE CONSTRAINT variants, which are
PostgreSQL extensions. The NOT VALID clause of the ADD CONSTRAINT
variant is also a PostgreSQL extension.

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