Re: TODO Item: Consider allowing control of upper/lower case folding of unquoted, identifiers

Russell Smith <mr-russ(at)pws(dot)com(dot)au> writes:
> The options I thought of were:
> ...
> 3. fully case sensitive even for unquoted identifiers (not spec
> compliant at all, but nevertheless possibly attractive especially for
> people migrating from MS SQLServer, where it is an option, IIRC).

Actually, I think most of the complainers wish that we'd duplicate
mysql's behavior ... although some experimentation suggests that that
behavior is impossibly inconsistent. It looks to me like, at least for
myisam tables, table names are fully case-sensitive and column names are
fully not (but are stored and reported in the originally entered
casing). Function names also seem case-insensitive. I'm afraid to
check whether other table handlers might behave differently still :-(

> Supporting all 3 of these behaviours at initdb time is not too invasive
> or complicated from my initial investigation.

You are deliberately ignoring all the hard problems.

The issue here is not whether we can make the parser fold identifiers
in different ways. The issue is how we keep everything from breaking
afterwards. The problems mostly are faced by clients --- psql's \d
commands, pg_dump, etc. Consider as an example pg_dump's quote_ident
function, which has to decide if an identifier requires double-quoting
or not. If it doesn't know what case-folding rule will be used to read
the identifier, how can it make that decision?

Restricting the case folding choice to be frozen at initdb would
eliminate some of these problems, but hardly all of them.

IMHO this area bears a whole lot of similarity to the
backslashes-in-string-literals problem. We are moving extremely slowly
towards spec compliance in that area, but we all know that when we throw
the switch by making standard_conforming_strings default to ON, we
are going to hear howls of anguish from everywhere. Exposing apps to
different possible case-folding rules is going to be at least as painful.

regards, tom lane