Re: BUG #18765: Inconsistent behaviour and errors with LIKE

Peter Eisentraut <peter(at)eisentraut(dot)org> writes:
> I have also come across this issue recently, and I think we should
> actually fix it. It makes sense to verify that the pattern is
> syntactically correct before trying to use it, instead of just using it
> incrementally and then erroring when you happen to hit the problematic bits.

I'm concerned about the performance cost of adding an extra scan of
the pattern --- a scan that has exactly zero benefit in all normal
use cases. Admittedly typical patterns aren't very big, but still
the cost/benefit ratio is bad for most people.

I wonder if we could buy back whatever it'd cost us by treating
this initial scan as a "compilation" of the LIKE pattern into
some form that would make the actual string search cheaper.
My gut says that building a representation that contains tokens
like "any character", "zero or more any characters", or "literal
string of N bytes" could save some cycles while searching.
In ILIKE, we could perhaps also case-fold the pattern at this
stage.

regards, tom lane