From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: creating CHECK constraints as NOT VALID |
Date: | 2011-06-01 01:42:08 |
Message-ID: | 20110601014208.GC13296@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Kupershmidt | 2011-06-01 01:48:51 | Re: Any idea for serializing INSERTING SERIAL column? |
Previous Message | Alvaro Herrera | 2011-06-01 01:29:52 | Re: creating CHECK constraints as NOT VALID |