Re: case_preservation_and_insensitivity = on

On Mon, Feb 20, 2017 at 3:06 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> On Mon, Feb 20, 2017 at 1:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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