On 1/8/16, Simon Riggs <si...@2ndquadrant.com> wrote: > On 8 January 2016 at 13:13, Vitaly Burovoy <vitaly.buro...@gmail.com> > wrote: > >> On 1/8/16, Simon Riggs <si...@2ndquadrant.com> wrote: >> > On 8 January 2016 at 12:49, Vitaly Burovoy <vitaly.buro...@gmail.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 >
Hmm... It really does. I was confused by a line in ATExecValidateConstraint conrel = heap_open(ConstraintRelationId, RowExclusiveLock); but validateCheckConstraint doesn't do anything for applying the lock to a row. What about SET NOT NULL constraints? There is no VALIDATE CONSTRAINT for it. -- Best regards, Vitaly Burovoy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
