On 1/24/16, Christophe Pettus <xof@thebuild.com> wrote:
>
> On Jan 24, 2016, at 9:01 PM, Charles Clavadetscher
> <clavadetscher@swisspug.org> wrote:
>
>> What is the point of having a check constraint that is not checked?
>
> Well, it *is* checked going into the future; it's just not checked at the
> time the constraint is added. Ultimately, you do want to fix the data, but
> this makes it a two-step process, and reduces the time the table is locked
> against access.
NOT VALID constraint checks new and updated rows, and gives an extra
time to fix current data and be sure there will be no new rows that
violates the check constraint during and after the fixing process.
N.B.: Prior 9.4 it does *NOT* reduce the time the table is locked
because VALIDATE CONSTRAINT requires ACCESS EXCLUSIVE[1] and uses
seqscan for check table's rows.
P.S. Lucas, If you have not received answers, you can find all of them
as a thread by the link:
http://www.postgresql.org/message-id/flat/CAE_gQfXTns1FR5Fx9wxpo1oZYwat639ua-gAqWZyNg201HCU=Q@mail.gmail.com
P.P.S.: Christophe, Charles! Please, use "Relpy to all" to be sure
the sender gets your answers even if he haven't subscribed to the
mailing list.
[1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html
--
Best regards,
Vitaly Burovoy