Re: NOT VALID for Unique Indexes
> On 26 Feb 2021, at 10:36, Simon Riggs 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/
Re: NOT VALID for Unique Indexes
On Fri, 26 Feb 2021 at 17:36, Simon Riggs wrote: > On Mon, Jan 18, 2021 at 11:19 PM japin wrote: >> >> >> On Fri, 15 Jan 2021 at 00:22, 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. >> > >> > 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.
Re: NOT VALID for Unique Indexes
On Mon, Jan 18, 2021 at 11:19 PM japin wrote: > > > On Fri, 15 Jan 2021 at 00:22, 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. > > > > 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. 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 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. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: NOT VALID for Unique Indexes
On Mon, Jan 18, 2021 at 12:34 AM David Fetter wrote: > > On Thu, Jan 14, 2021 at 04:22:17PM +, 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. That is what NOT VALID allows, but it can't be relied on for optimization. -- Simon Riggshttp://www.EnterpriseDB.com/
Re: NOT VALID for Unique Indexes
On Fri, 15 Jan 2021 at 00:22, 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. > > 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.
Re: NOT VALID for Unique Indexes
On Thu, Jan 14, 2021 at 04:22:17PM +, 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 http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
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 Riggshttp://www.EnterpriseDB.com/ alter_index_set_unique_not_valid.v4.patch Description: Binary data
