From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter@eisentraut.org>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Anmol Mohanty <anmol.mohanty@salesforce.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18765: Inconsistent behaviour and errors with LIKE
Date: Wed, 08 Jan 2025 16:12:32 -0500
Message-ID: <1693904.1736370752@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, peter@eisentraut.org, david.g.johnston@gmail.com, anmol.mohanty@salesforce.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18765: Inconsistent behaviour and errors with LIKE
In-Reply-To: <1693904.1736370752@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox