Re: NOT VALID for Unique Indexes

On Fri, 26 Feb 2021 at 17:36, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> On Mon, Jan 18, 2021 at 11:19 PM japin <japinli(at)hotmail(dot)com> wrote:
>>
>>
>> On Fri, 15 Jan 2021 at 00:22, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)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"?
>
> 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.