From: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: New feature "... ALTER CONSTRAINT ... VERIFY USING INDEX" |
Date: | 2016-01-08 14:14:55 |
Message-ID: | CAKOSWNnZXs-hRM9zZdHQhYKhzP+v7_O_gzaA+eHy4RkCsGJy=Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 1/8/16, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> 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
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2016-01-08 14:31:04 | Re: Improving replay of XLOG_BTREE_VACUUM records |
Previous Message | Alvaro Herrera | 2016-01-08 13:36:42 | Re: Improving replay of XLOG_BTREE_VACUUM records |