Re: ERROR: check constraint - PostgreSQL 9.2

Hello Vitaly

> -----Original Message-----
> From: Vitaly Burovoy [mailto:vitaly.burovoy@gmail.com]
> Sent: Montag, 25. Januar 2016 14:25
> To: Christophe Pettus <xof@thebuild.com>; clavadetscher@swisspug.org
> Cc: Postgres General <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] ERROR: check constraint - PostgreSQL 9.2
>
> 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.

Oops. Honestly I did not think of that. I will keep that in mind in the future.
Thank you for the hint.

Charles

>
> [1]http://www.postgresql.org/docs/9.2/static/sql-altertable.html
> --
> Best regards,
> Vitaly Burovoy