Re: interval_ops shall stop using btequalimage (deduplication)

On Wed, Oct 11, 2023 at 01:00:44PM -0700, Peter Geoghegan wrote:
> On Wed, Oct 11, 2023 at 11:38 AM Noah Misch <noah(at)leadboat(dot)com> wrote:
> > Interesting. So, >99% of interval-type indexes, even ones WITH
> > (deduplicate_items=off), will get amcheck failures. The <1% of exceptions
> > might include indexes having allequalimage=off due to an additional column,
> > e.g. a two-column (interval, numeric) index. If interval indexes are common
> > enough and "pg_amcheck --heapallindexed" failures from $SUBJECT are relatively
> > rare, that could argue for giving amcheck a special case. Specifically,
> > downgrade its "metapage incorrectly indicates that deduplication is safe" from
> > ERROR to WARNING for interval_ops only.
>
> I am not aware of any user actually running "deduplicate_items = off"
> in production, for any index. It was added purely as a defensive thing
> -- not because I anticipated any real need to disable deduplication.
> Deduplication was optimized for being enabled by default.

Sure. Low-importance background information: deduplicate_items=off got on my
radar while I was wondering if ALTER INDEX ... SET (deduplicate_items=off)
would clear allequalimage. If it had, we could have advised people to use
ALTER INDEX, then rebuild only those indexes still failing "pg_amcheck
--heapallindexed". ALTER INDEX doesn't do that, ruling out that idea.

> > Without that special case (i.e. with
> > the v1 patch), the release notes should probably resemble, "After updating,
> > run REINDEX on all indexes having an interval-type column."
>
> +1
>
> > There's little
> > point in recommending pg_amcheck if >99% will fail. I'm inclined to bet that
> > interval-type indexes are rare, so I lean against adding the amcheck special
> > case. It's not a strong preference. Other opinions?

> exactly one case like that post-fix (interval_ops is at least the only
> affected core code opfamily), so why not point that out directly with
> a HINT? A HINT could go a long way towards putting the problem in
> context, without really adding a special case, and without any real
> question of users being misled.

Works for me. Added.