Re: New feature "... ALTER CONSTRAINT ... VERIFY USING INDEX"

On 8 January 2016 at 13:13, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> wrote:

> On 1/8/16, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > On 8 January 2016 at 12:49, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com>
> > wrote:
> >
> >
> >> In Postgres9.1 a new feature was implemented [1] for adding PK and
> >> UNIQUE constraints using indexes created concurrently, but constraints
> >> NOT NULL and CHECK still require full seqscan of a table. New CHECK
> >> constraint allows "NOT VALID" option but VALIDATE CONSTRAINT still
> >> does seqscan (with RowExclusiveLock, but for big and constantly
> >> updatable table it is still awful).
> >>
> >> It is possible to find wrong rows in a table without seqscan if there
> >> is an index with a predicate allows to find such rows. There is no
> >> sense what columns it has since it is enough to check whether
> >> index_getnext for it returns NULL (table is OK) or any tuple (table
> >> has wrong rows).
> >>
> >
> > You avoid a full seqscan by creating an index which also does a full seq
> > scan.
> >
> > How does this help? The lock and scan times are the same.
>
> I avoid not a full seqscan, but a time when table is under
> ExclusiveLock: index can be build concurrently without locking table.

That is exactly what ADD ...NOT VALID and VALIDATE already does, as of 9.4.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services