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

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.

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