Re: citext LIKE search bug

Thanks for the answer, "doc bug" works for me.

For what it's worth, the citext_pattern_ops option seems to provide
case-blind = searches, which text_pattern_ops does not. Unless I got
confused when I tested out the various permutations. If that's true, then
citext_pattern_ops has value for me.

On Thu, Sep 19, 2019 at 2:19 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>