Thread: 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
Attachment
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