Re: creating CHECK constraints as NOT VALID

On Tue, May 31, 2011 at 11:35:01AM -0500, Kevin Grittner wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> > This patch allows you to initially declare a CHECK constraint as
> > NOT VALID, similar to what we already allow for foreign keys.
> > That is, you create the constraint without scanning the table and
> < after it is committed, it is enforced for new rows; later, all
> > rows are checked by running ALTER TABLE VALIDATE CONSTRAINT, which
> > doesn't need AccessExclusive thus allowing for better concurrency.
>
> I think it's a valuable feature, not just in terms of timing and
> concurrency, but in terms of someone starting with less-than-perfect
> data who wants to prevent further degradation while cleaning up the
> existing problems. This feature is present in other databases I've
> used.

Yup, the ER triage approach to data integrity: "Stop the major bleeding,
we'll go back and make it a pretty scar later"

Follows from one of the practical maxims of databases: "The data is
always dirty" Being able to have the constraints enforced at least for
new data allows you to at least fence the bad data, and have a shot at
fixing it all. Right now, you may be forced into running with
constraints effectively 'off', depending on the app to get new data
right, while attempting to catch up. And the app probably put the bad
data in there in the first place. One of the thankless, important but
seemingly never urgent tasks.

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE