Horton

Re: creating CHECK constraints as NOT VALID

David Fetter <david@fetter.org>

View thread
From: David Fetter <david@fetter.org>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2011-06-01T01:42:08Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix pg_get_constraintdef to cope with NOT VALID constraints

On Tue, May 31, 2011 at 12:04:07PM -0400, Alvaro Herrera 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.
> 
> The trickiest bit here was realizing that unlike FKs, check constraints
> do inherit, and so needed special treatment for recursion.  Other than
> that I think this was pretty straightforward.
> 
> I intend to attempt to apply this to NOT NULL constraints as well, once
> the patch to add them to pg_constraint is in.
> 
> Thoughts?
> 
> This patch courtesy of Enova Financial.

Great stuff!

A colleague brought up an interesting idea that I think is worth
exploring for all NOT VALID constraints, to wit, is there some way
(via SQL) to find which rows violate which constraints?  I'm picturing
some kind of function that could be aggregated into some structure for
each violating row...

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate