Re: [HACKERS] case_preservation_and_insensitivity = on

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.  psql's lexer is already smart enough that you could teach it
>> rules like "smash any unquoted identifier to lower case" (probably it
>> would fold keywords too, but that seems OK).  That's probably not much
>> help for custom applications, which aren't likely to be going through
>> psql scripts; but the fact that such behavior is in reach at all on the
>> client side seems encouraging.
>
> This sounds like a really good solution to me,
> since there is actually nothing missing on the PostgreSQL server-side,
> it's merely a matter of inconvenience on the client-side.

It doesn't sound like a good solution to me, because there can be SQL
code inside stored procedures that clients never see.  In fact, a
function or procedure can assemble an SQL query text using arbitrary
Turing-complete logic and then execute it.  In fact, you don't even
really need a function or procedure; the client could send a DO block
that does this directly.  We don't run into this problem with
autocommit because a function or procedure has to run entirely within
a single transaction, so I don't think that is really the same thing.

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, 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,
and it's probably also expensive.

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.  Doing something on the client side
is just a kludge.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company