Re: Permute underscore separated components of columns before fuzzy matching

From: Arne Roland <arne(dot)roland(at)malkut(dot)net>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: youzhick(at)gmail(dot)com, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: Permute underscore separated components of columns before fuzzy matching
Date: 2023-12-31 16:29:36
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers


Mikhail Gribkov <youzhick(at)gmail(dot)com> writes:

> > Honestly I'm not entirely sure fixing only two switched words is
worth the
> > effort, but the declared goal is clearly achieved.
> > I think the patch is good to go, although you need to fix code
> I took a brief look at this.  I concur that we shouldn't need to be
> hugely concerned about the speed of this code path.  However, we *do*
> need to be concerned about its maintainability, and I think the patch
> falls down badly there: it adds a chunk of very opaque and essentially
> undocumented code, that people will need to reverse-engineer anytime
> they are studying this function.  That could be alleviated perhaps
> with more work on comments, but I have to wonder whether it's worth
> carrying this logic at all.  It's a rather strange behavior to add,
> and I wonder if many users will want it.

I encounter this problem all the time. I don't know, whether my clients
are representative. But I see the problem, when the developers show me
their code base all the time.
It's an issue for column names and table names alike. I personally spent
hours watching developers trying various permutations.
They rarely request this feature. Usually they are to embarrassed for
not knowing their object names to request anything in that state.
But I want the database, which I support, to be gentle and helpful to
the user under these circumstances.

Regarding complexity: I think the permutation matrix is the thing to
easily get wrong. I had a one off bug writing it down initially.
I tried to explain the conceptual approach better with a longer comment
than before.

                 * Only consider mirroring permutations, since the
three simple rotations are already
                 * (or will be for a later underscore_current) covered
                 * The entries of the permutation matrix tell us, where
we should copy the tree segments to.
                 * The zeroth dimension iterates over the permutations,
while the first dimension iterates
                 * over the three segments are permuted to.
                 * Considering the string A_B_C the three segments are:
                 * - before the initial underscore sections (A)
                 * - between the underscore sections (B)
                 * - after the later underscore sections (C)

If anything is still unclear, I'd appreciate feedback about what might
be still unclear/confusing about this.
I can't promise to be helpful, if something breaks. But I have
practically forgotten how I did it, and I found it easy to extend it
like described below. It would have been embarrassing otherwise. Yet
this gives me hope, it should be possible to enable others the same way.
I certainly want the code simple without need to reverse-engineer
anything. Please let me know, if there are difficult to understand bits
left around.

> One thing that struck me is that no care is being taken for adjacent
> underscores (that is, "foo__bar" and similar cases).  It seems
> unlikely that treating the zero-length substring between the
> underscores as a word to permute is helpful; moreover, it adds
> an edge case that the string-moving logic could easily get wrong.
> I wonder if the code should treat any number of consecutive
> underscores as a single separator.  (Somewhat related: I think it
> will behave oddly when the first or last character is '_', since the
> outer loop ignores those positions.)

I wasn't sure how there could be any potential future bug with copying
zero-length strings, i.e. doing nothing. And I still don't see that.

There is one point I agree with: Doing this seems rarely helpful. I
changed the code, so it treats sections delimited by an arbitrary amount
of underscores.
So it never permutes with zero length strings within. I also added
functionality to skip the zero length cases if we should encounter them
at the end of the string.
So afaict there should be no zero length swaps left. Please let me know
whether this is more to your liking.

I also replaced the hard limit of underscores with more nuanced limits
of permutations to try before giving up.

> > And it would be much more convenient to work with your patch if
every next
> > version file will have a unique name (maybe something like "_v2", "_v3"
> > etc. suffixes)
> Please.  It's very confusing when there are multiple identically-named
> patches in a thread.

Sorry, I started with this, because I confused cf bot in the past about
whether the patches should be applied on top of each other or not.

For me the cf-bot logic is a bit opaque there. But you are right,
confusing patch readers is definitely worse. I'll try to do that. I hope
the attached format is better.

One question about pgindent: I struggled a bit with getting the right
version of bsd_indent. I found versions labeled 2.2.1 and 2.1.1, but
apparently we work with 2.1.2. Where can I get that?


Attachment Content-Type Size
0001-fuzzy_underscore_permutation_v3.patch text/x-patch 9.8 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Melanie Plageman 2023-12-31 18:28:16 Confine vacuum skip logic to lazy_scan_skip
Previous Message Tom Lane 2023-12-31 15:58:34 Re: pg_stat_statements: more test coverage