Re: [HACKERS] case_preservation_and_insensitivity = on

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Feb 20, 2017 at 3:06 PM, Joel Jacobson <joel@trustly.com> wrote:
>> On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> The versions of autocommit that have actually stood the test of time were
>>> implemented on the client side (in psql and JDBC, and I think ODBC as
>>> well), where the scope of affected code was lots smaller.  I wonder
>>> whether there's any hope of providing something useful for case-folding
>>> in that way.

> It doesn't sound like a good solution to me, because there can be SQL
> code inside stored procedures that clients never see. ...
> If you only care about rewriting queries that come directly from a
> client and you don't care about DO blocks, then you could probably
> make this work,

I think these are straw-man arguments, really.  Consider the actual use
case for such a feature: it's for porting some application that was not
written against Postgres to begin with.  The odds that such an app uses DO
blocks are exactly zero.  It may well make use of server-side functions,
but those are going to require significant porting effort in any case.
So I think a case-folding function that could be interposed in front of
libpq or JDBC or $favorite_driver could well go a long way towards solving
that sort of problem.  It wouldn't be 100%, sure, but if you hold your
breath and wait for a 100% solution to appear on the server side, you're
going to be quite blue.

> but it still requires that the client parse the query
> using a lexer and parser that are very similar to the quite
> complicated ones on the server side.  That might be hard to get right,

Not if you look into src/fe_utils.  There's a reason why we sweated so
hard to break out psql's lexer, and it wasn't that pgbench needed it.

> I think that solving this problem on the server side is likely to be a
> huge amount of really unrewarding work that might get rejected anyway
> after tons of effort, but if you did happen to succeed in solving it,
> it would be a good clean solution.

The problem is not whether hacking the server's case folding could be done
cleanly (although I agree that touching it at all would be a lot of work).
The problem is all the client-side breakage you're going to cause for
every other bit of software that will have no choice but to learn to
cope with it.

Basically, the difficulty with requests like this is that they demand
externalizing someone's case-folding conversion costs onto the rest of
the Postgres-using universe.  I don't believe there's a way to make that
problem disappear if you insist on doing it server-side.  But maybe a
properly defined client-side library could help some people without
creating costs for other people.
        regards, tom lane