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/
Attachments:
alter_index_set_unique_not_valid.v4.patchapplication/octet-stream; name=alter_index_set_unique_not_valid.v4.patchDownload+208-10
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 UNIQUEIt 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 Mon, Jan 18, 2021 at 12:34 AM David Fetter <david@fetter.org> wrote:
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.
That is what NOT VALID allows, but it can't be relied on for optimization.
--
Simon Riggs http://www.EnterpriseDB.com/
On Mon, Jan 18, 2021 at 11:19 PM japin <japinli@hotmail.com> wrote:
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 UNIQUEIt 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_nameShould 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 Riggs http://www.EnterpriseDB.com/
On Fri, 26 Feb 2021 at 17:36, Simon Riggs <simon.riggs@enterprisedb.com> wrote:
On Mon, Jan 18, 2021 at 11:19 PM japin <japinli@hotmail.com> wrote:
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 UNIQUEIt 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]https://www.postgresql.org/docs/devel/catalog-pg-index.html:
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_nameShould 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.
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/