Re: case_preservation_and_insensitivity = on

On Thu, Feb 16, 2017 at 11:16 PM, Joel Jacobson <joel(at)trustly(dot)com> wrote:
>> The short answer is that nobody can see a way to modify the identifier
>> case-folding rules that isn't going to add more pain than it subtracts.
>> And much of the added pain will be felt by people who aren't getting
>> any benefit, who will therefore be vociferously against the whole thing.
>
> I've read the discussion and have an idea:
>
> When case preservation by default is on, then simply enforce
> UNIQUE(LOWER(object_name)), to prevent ambiguity.

That (1) breaks backward compatibility, because people might have
objects with names identical except for case in existing databases and
(2) requires an expression index on a system catalog, which is not
supported. You could work around problem #2 with enough work, I
guess, by storing two copies of the name of "name" column, one with
the original casing and a second that has been downcased for indexing
purposes.

I don't really understand what the GUC does in this scenario.
Changing a GUC won't change the data that's already in your system
catalogs, so it would have to change the interpretation of
newly-arriving queries against that data. But once you've already
decided to have a hard-and-fast rule that the names must be unique
after lower-casing, there's no obvious benefit to rejecting queries
that mention the same name with different case.

As compared with any proposal that actually changes the case-folding
behavior, a new mode that is case-preserving but case-insensitive
would break less stuff. People who never use case to differentiate
between different objects probably won't notice the difference, except
that sometimes they might accidentally type something in the wrong
case and it would work instead of failing. Tools that are designed on
the existing fold-to-lowercase behavior would keep working if they
don't actually query the system catalogs for information; those that
do might need adjustment.

It still sounds pretty painful, though.

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