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? -- Simon Riggs http://www.EnterpriseDB.com/
alter_index_set_unique_not_valid.v4.patch
Description: Binary data