Re: NOT VALID for Unique Indexes

On Mon, Jan 18, 2021 at 11:19 PM japin <japinli(at)hotmail(dot)com> wrote:
>
>
> 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"?

indisvalid already has defined meaning related to creating indexes
concurrently, so I was forced to create another column with a similar
name.

Thanks for reviewing the code in detail.

> 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?

Yes, that is possible. (I wrote the NOT VALID patch for FKs, so was
aware of that).

The syntax I presented was for ALTER INDEX. Not all UNIQUE indexes are
constraints, so it is important to add the option on ALTER INDEX.
Adding the ALTER TABLE syntax can be done later.

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

I'm sorry, I don't understand that question. FKs and CHECK constrants
are already supported, as you point out above.

I won't be able to finish this patch in time for this next CF, but
thanks for your interest, I will complete for PG15 later this year.

--
Simon Riggs http://www.EnterpriseDB.com/