On Fri, 26 Feb 2021 at 17:36, Simon Riggs <simon.ri...@enterprisedb.com> wrote: > On Mon, Jan 18, 2021 at 11:19 PM japin <japi...@hotmail.com> wrote: >> >> >> 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"? > > indisvalid already has defined meaning related to creating indexes > concurrently, so I was forced to create another column with a similar > name. >
The doc of indisvalid says [1]: If true, the index is currently valid for queries. False means the index is possibly incomplete: it must still be modified by INSERT/UPDATE operations, but it cannot safely be used for queries. If it is unique, the uniqueness property is not guaranteed true either. So I think we can use it instead of create a new column. Does induniquevalid have any other special meaning? > 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'm sorry, I mixed the indexes and constraints. [1] - https://www.postgresql.org/docs/devel/catalog-pg-index.html -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.