Обсуждение: So do we really *need* those substring() ops in tab-completion queries?

This evening's argument about DO completion caused me to look a bit
closer at tab-complete.c than I ever had before.  I am now wondering
exactly why we bother with all the logic like

"  WHERE substring(pg_catalog.quote_ident(rolname),1,%d)='%s'"

in the SQL queries that it issues.  It appears to me that this code is:

1. Wrong.  The value substituted for %d is strlen() of the partial word
it wants to match; but the backend is going to count substring's
argument in characters not bytes.  So it looks to me like this code
fails entirely when dealing with names containing multibyte characters.
I'm not in a very good position to confirm that right now, but perhaps
someone can try it.

2. Unnecessary.  The loop at the bottom of _complete_from_query applies
pg_strncasecmp anyway to filter out query results that don't match the
supplied partial word.  (BTW, why is this pg_strncasecmp and not just
strncmp?  The SQL-level filter will have got rid of non-exact matches,
so that's useless, and possibly wrong depending on locale issues.)

3. Inefficient.  It seems likely to me that filtering on the prefix on
the backend side isn't going to be more efficient than doing it on the
client side, except maybe in the schema-name cases.  If the conditions
were phrased in a way that made them indexable, they might be worth the
trouble --- but they aren't.  In the worst case where we're asked for
completions from a zero-length string, the backend-side substring ops
are certainly pure overhead.

Comments?
        regards, tom lane

On Sun, Jan 3, 2010 at 1:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>  If the conditions
> were phrased in a way that made them indexable, they might be worth the
> trouble --- but they aren't.

Wow, that is weird, especially since it's *easier* to write them
properly using LIKE anyways.

--
greg

Greg Stark <gsstark@mit.edu> writes:
> On Sun, Jan 3, 2010 at 1:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> �If the conditions
>> were phrased in a way that made them indexable, they might be worth the
>> trouble --- but they aren't.

> Wow, that is weird, especially since it's *easier* to write them
> properly using LIKE anyways.

Really?  You'd have to worry about escaping _ and % ...
        regards, tom lane

On Sat, Jan 02, 2010 at 08:21:35PM -0500, Tom Lane wrote:
> 3. Inefficient.  It seems likely to me that filtering on the prefix on
> the backend side isn't going to be more efficient than doing it on the
> client side, except maybe in the schema-name cases.  If the conditions
> were phrased in a way that made them indexable, they might be worth the
> trouble --- but they aren't.  In the worst case where we're asked for
> completions from a zero-length string, the backend-side substring ops
> are certainly pure overhead.

I would have thought cases where you have a million
tables/roles/users/triggers that it would be more efficient to avoid
transferring all those names over the wire if you're going to filter
99% anyway.

This does require the test on the server side to be such that it never
filters too many rows, so it does have to work. But there is merit to
doing some simple filtering if it works.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.