pgsql: Enable CHECK constraints to be declared NOT VALID

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Enable CHECK constraints to be declared NOT VALID
Date: 2011-06-30 15:58:09
Message-ID: E1QcJd3-0001HT-91@gemulon.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers pgsql-hackers

Enable CHECK constraints to be declared NOT VALID

This means that they can initially be added to a large existing table
without checking its initial contents, but new tuples must comply to
them; a separate pass invoked by ALTER TABLE / VALIDATE can verify
existing data and ensure it complies with the constraint, at which point
it is marked validated and becomes a normal part of the table ecosystem.

An non-validated CHECK constraint is ignored in the planner for
constraint_exclusion purposes; when validated, cached plans are
recomputed so that partitioning starts working right away.

This patch also enables domains to have unvalidated CHECK constraints
attached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOT
VALID, which can later be validated with ALTER DOMAIN / VALIDATE
CONSTRAINT.

Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the various
reviews, and Robert Hass for documentation wording improvement
suggestions.

This patch was sponsored by Enova Financial.

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/897795240cfaaed724af2f53ed2c50c9862f951f

Modified Files
--------------
doc/src/sgml/catalogs.sgml | 2 +-
doc/src/sgml/ref/alter_domain.sgml | 45 +++++-
doc/src/sgml/ref/alter_table.sgml | 12 +-
src/backend/access/common/tupdesc.c | 1 +
src/backend/catalog/heap.c | 13 +-
src/backend/commands/tablecmds.c | 236 ++++++++++++++++++++++++----
src/backend/commands/typecmds.c | 140 +++++++++++++++--
src/backend/optimizer/util/plancat.c | 11 +-
src/backend/parser/gram.y | 12 ++-
src/backend/tcop/utility.c | 4 +
src/backend/utils/cache/relcache.c | 1 +
src/include/access/tupdesc.h | 1 +
src/include/catalog/heap.h | 1 +
src/include/commands/typecmds.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/test/regress/expected/alter_table.out | 101 ++++++++++++
src/test/regress/expected/domain.out | 11 ++
src/test/regress/sql/alter_table.sql | 46 ++++++
src/test/regress/sql/domain.sql | 10 ++
19 files changed, 581 insertions(+), 70 deletions(-)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2011-06-30 16:00:34 Re: creating CHECK constraints as NOT VALID
Previous Message Noah Misch 2011-06-30 15:55:19 Re: Avoid index rebuilds for no-rewrite ALTER TABLE ALTER TYPE

Browse pgsql-committers by date

  From Date Subject
Next Message Alvaro Herrera 2011-06-30 19:09:02 Re: [COMMITTERS] pgsql: Enable CHECK constraints to be declared NOT VALID
Previous Message Robert Haas 2011-06-30 11:50:20 Re: [COMMITTERS] pgsql: Make the visibility map crash-safe.