Thread: NOT VALID for Unique Indexes
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/
Attachment
On Thu, Jan 14, 2021 at 04:22:17PM +0000, Simon Riggs 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. This is a great feature! Not exactly on point with this, but in a pretty closely related context, is there some way we could give people the ability to declare at their peril that a constraint is valid without incurring the full scan that VALIDATE currently does? This is currently doable by fiddling directly with the catalog, which operation is broadly more dangerous and ill-advised. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, 15 Jan 2021 at 00:22, Simon Riggs <simon.riggs@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.
> On 26 Feb 2021, at 10:36, Simon Riggs <simon.riggs@enterprisedb.com> wrote: > 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. This patch no longer applies to HEAD, will there be an updated version for this CF? -- Daniel Gustafsson https://vmware.com/