Re: REINDEX checking of index constraints

Historically, REINDEX would always revalidate any uniqueness enforced by the
index. An EDB customer reported that this is not happening, and indeed I
broke it way back in commit 8ceb24568054232696dddc1166a8563bc78c900a.
Specifically, REINDEX TABLE and REINDEX DATABASE no longer revalidate
constraints, but REINDEX INDEX still does so. As a consequence, REINDEX INDEX
is the only form of REINDEX that fixes a failed CREATE INDEX CONCURRENTLY.

Attached patch just restores the old behavior. Would it be worth preserving
the ability to fix an index consistency problem with a REINDEX independent
from related heap consistency problems such as duplicate keys?

Thanks,
nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com

Noah,

> Attached patch just restores the old behavior. Would it be worth preserving
> the ability to fix an index consistency problem with a REINDEX independent
> from related heap consistency problems such as duplicate keys?

I would love to have two versions of REINDEX, one which validated and
one which didn't. Maybe a ( validate off ) type check?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 07/21/2013 11:30 AM, Josh Berkus wrote:
> Noah,
>
>> Attached patch just restores the old behavior. Would it be worth preserving
>> the ability to fix an index consistency problem with a REINDEX independent
>> from related heap consistency problems such as duplicate keys?
>
> I would love to have two versions of REINDEX, one which validated and
> one which didn't. Maybe a ( validate off ) type check?

Cancel this. I just did some tests, and there amount of time required
for the validation (at least, in simple two-column table test) is < 10%
of the time required to reindex in general. At that difference, we
don't need two options.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 07/21/2013 11:30 AM, Josh Berkus wrote:
> Noah,
>
>> Attached patch just restores the old behavior. Would it be worth preserving
>> the ability to fix an index consistency problem with a REINDEX independent
>> from related heap consistency problems such as duplicate keys?
>
> I would love to have two versions of REINDEX, one which validated and
> one which didn't. Maybe a ( validate off ) type check?

Cancel this. I just did some tests, and there amount of time required
for the validation (at least, in simple two-column table test) is < 10%
of the time required to reindex in general. At that difference, we
don't need two options.

Unless you're asking if we want a command to check the index validity
without rebuilding it? That might be more valuable ...

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Sun, Jul 21, 2013 at 11:30:54AM -0700, Josh Berkus wrote:
> Noah,
>
> > Attached patch just restores the old behavior. Would it be worth preserving
> > the ability to fix an index consistency problem with a REINDEX independent
> > from related heap consistency problems such as duplicate keys?
>
> I would love to have two versions of REINDEX, one which validated and
> one which didn't. Maybe a ( validate off ) type check?
>
+1 There are reasons to reindex that do not involve its validity and it would
be great to not need to visit the heap for that.

Regards,
Ken

On Sun, Jul 21, 2013 at 01:47:00PM -0700, Josh Berkus wrote:
> On 07/21/2013 11:30 AM, Josh Berkus wrote:
> >> Attached patch just restores the old behavior. Would it be worth preserving
> >> the ability to fix an index consistency problem with a REINDEX independent
> >> from related heap consistency problems such as duplicate keys?
> >
> > I would love to have two versions of REINDEX, one which validated and
> > one which didn't. Maybe a ( validate off ) type check?
>
> Cancel this. I just did some tests, and there amount of time required
> for the validation (at least, in simple two-column table test) is < 10%
> of the time required to reindex in general. At that difference, we
> don't need two options.
>
> Unless you're asking if we want a command to check the index validity
> without rebuilding it? That might be more valuable ...

I meant to ask whether, instead of reverting the accidental behavior change,
we should do something like leave the behavior and change the documentation
instead. I personally vote "no", but that alternative seemed credible enough
to justify mentioning it. Something more radical, like a new UI, would be a
separate patch.

Thanks,
nm

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com

Noah Misch wrote:

> I meant to ask whether, instead of reverting the accidental behavior change,
> we should do something like leave the behavior and change the documentation
> instead. I personally vote "no", but that alternative seemed credible enough
> to justify mentioning it. Something more radical, like a new UI, would be a
> separate patch.

separate patch++. I agree some use cases probably justify new UI.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services