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/