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
Attachments:
reindex-constraints-v1.patchtext/plain; charset=us-asciiDownload+45-29
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM008fbc370f6b9bb156cd4162e9fedc2cef660bc9071b08a81d11b64d569d21e18e8eef3b69a87d2bd5f0e619f0e90d67@asav-3.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM17764dc2454bcf975425ce7e45e64bf5010ca4339e379f43bb03e41953b42b9a761d70dbe39656bc1f973553eb2df82d@asav-1.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Import Notes
Reply to msg id not found: WM17764dc2454bcf975425ce7e45e64bf5010ca4339e379f43bb03e41953b42b9a761d70dbe39656bc1f973553eb2df82d@asav-1.01.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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers