Re: citext LIKE search bug

Morris de Oryx <morrisdeoryx(at)gmail(dot)com> writes:
> I'm using citext fields, and am working through how to build indexes that
> the planner recognizes. I found this paragraph in the PG 11 release notes:

> "Allow creation of indexes that can be used by LIKE comparisons on citext
> columns (Alexey Chernyshov)
> To do this, the index must be created using the citext_pattern_ops operator
> class."
> https://www.postgresql.org/docs/11/release-11.html

> As far as I can tell, this doesn't work, or at least not in my case with an
> expression index.

Hm. I found the original submission in the archives [1], and Alexey
explicitly *didn't* claim that that patch was sufficient to enable LIKE
index optimizations on citext. Indeed it obviously isn't, since it just
created some weirdly-named operators without connecting them up to the
LIKE plumbing in any way. So this seems like a case of the REL11 release
notes author (probably Bruce) not reading too closely, which doesn't seem
like entirely his fault since the commit message was totally content-free
about what the point of the patch was [2]. But anyway, right now it seems
to me that citext_pattern_ops has exactly zero value, which makes me
wonder why we committed it in advance of some use-case getting filled in.
It's not such a large patch that it had to get in to reduce its maintenance
overhead.

While I'm looking at it, I notice that the patch failed to honor the
scalarltsel-vs-scalarlesel, scalargtsel-vs-scalargesel distinction
that had been created not too long before. Sigh.

Anyway it seems like the only near-term to-do item here is to correct
the v11 release notes to not claim that citext_pattern_ops does anything
useful. Maybe we should just remove the entry altogether.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/d868ae6c-501c-a17c-c01b-f531d646172d%40postgrespro.ru
[2] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f24649976