Re: NOT VALID for Unique Indexes

On Fri, 15 Jan 2021 at 00:22, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> As you may be aware the NOT VALID qualifier currently only applies to
> CHECK and FK constraints, but not yet to unique indexes. I have had
> customer requests to change that.
>
> It's a reasonably common requirement to be able to change an index
> to/from a unique index, i.e. Unique -> NonUnique or NonUnique to
> Unique. Previously, it was easy enough to do that using a catalog
> update, but with security concerns and the fact that the optimizer
> uses the uniqueness to optimize queries means that there is a gap in
> our support. We obviously need to scan the index to see if it actually
> can be marked as unique.
>
> In terms of locking we need to exclude writes while we add uniqueness,
> so scanning the index to check it is unique would cause problems. So
> we need to do the same thing as we do with other constraint types: add
> the constraint NOT VALID in one transaction and then later validate it
> in a separate transaction (if ever).
>
> I present a WIP patch to show it's a small patch to change Uniqueness
> for an index, with docs and tests.
>
> ALTER INDEX SET [NOT] UNIQUE [NOT VALID]
> ALTER INDEX VALIDATE UNIQUE
>
> It doesn't do the index validation scan (yet), but I wanted to check
> acceptability, syntax and requirements before I do that.
>
> I can also add similar syntax for UNIQUE and PK constraints.
>
> Thoughts please?

Great! I have some questions.

1. In the patch, you add a new attribute named "induniquevalid" in pg_index,
however, there is a "indisvalid" in pg_index, can we use "indisvalid"?

2. The foreign key and CHECK constraints are valid by using
ALTER TABLE .. ADD table_constraint [ NOT VALID ]
ALTER TABLE .. VALIDATE CONSTRAINT constraint_name

Should we implement unique index valid/not valid same as foreign key and
CHECK constraints?

3. If we use the syntax to valid/not valid the unique, should we support
other constraints, such as foreign key and CHECK constraints?

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.