Re: Does converting an indexed varchar to text rewrite its index? Docs say so, tests say no.

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
> On 1/23/20 1:28 PM, Mike Lissner wrote:
>> OK, so then the docs *are* wrong? They say that:
>>> any indexes on the affected columns must still be rebuilt.
>> But that doesn't happen? Sorry to be persistent. I'm just a bit confused
>> here.

> My guess is that it is because in Postgres varchar is just text with an
> optional length restriction.

More directly, it's because varchar piggybacks on text's operators and
index opclass. If we've decided that we don't need to rewrite the table,
and if indexcmds.c's CheckIndexCompatible() says that the old and new
index definitions are 100% compatible, then we skip rebuilding the index
contents as well. But you need the same opclasses to be compatible.

Commit 367bc426a, which added that behavior, seems to have been a lot
lazier about updating the user-facing docs than it should've been.
I can agree with the position that all the weird little cases in
CheckIndexCompatible() are a bit much to be documenting, but not
changing the ALTER TABLE reference page at all seems inadequate.

regards, tom lane