Re: citext LIKE search bug

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Morris de Oryx <morrisdeoryx(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: citext LIKE search bug
Date: 2019-09-19 04:19:34
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

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

> 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

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


In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Morris de Oryx 2019-09-19 04:30:15 Re: citext LIKE search bug
Previous Message Tom Lane 2019-09-19 03:37:19 Re: BUG #16013: Unexpected results from bit field query