From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        Anmol Mohanty <anmol.mohanty@salesforce.com>,
        pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18765: Inconsistent behaviour and errors with LIKE
In-reply-to: <83c66908-ff3e-4aa1-8d90-1647d8dc8baa@eisentraut.org>
References: <18765-6c26d2047e6f5143@postgresql.org>
 <CAKFQuwaoWrRuM544zDqnTg2wnB8fkQgOe0qAHrvHmDQgAyLGcw@mail.gmail.com>
 <CAPKoE6y+5vk5S4fVGgkFsO1pPDiGo=LLJd0o36ChQYzyZhVpMQ@mail.gmail.com>
 <CAKFQuwYPO3gfsHekjt4Y1B9QgrY-psXDr3VgMXTtZpU1YYBwOA@mail.gmail.com>
 <83c66908-ff3e-4aa1-8d90-1647d8dc8baa@eisentraut.org>
Comments: In-reply-to Peter Eisentraut <peter@eisentraut.org>
	message dated "Wed, 08 Jan 2025 21:52:29 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1693903.1736370752.1@sss.pgh.pa.us>
Date: Wed, 08 Jan 2025 16:12:32 -0500
Message-ID: <1693904.1736370752@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1693904.1736370752%40sss.pgh.pa.us>
Precedence: bulk

Peter Eisentraut <peter@eisentraut.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