On Fri, 15 Jan 2021 at 00:22, Simon Riggs <simon.ri...@enterprisedb.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.