Re: case_preservation_and_insensitivity = on

On Sun, Feb 19, 2017 at 5:58 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> 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

Yes, but if since the target for this new feature is probably new projects
(who need this feature to even consider PostgreSQL as a database,
like the original author of the other thread where they were using SQLAnywhere),
then maybe that's an acceptable tradeoff, since all the existing
database who try
to use the feature will just get an error if they try to switch on the feature
e.g. "error: objects exist with identical lowercase names".

> (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.

Yes, storing both the unique lowercase name together with the OriginalCase
was also my idea on how to implement it.

> I don't really understand what the GUC does in this scenario.

Changing the GUC (if you run into problems with tools) would simply
just change what names are returned from pg for all the object names,
i.e. it would then return the lowercase names instead of the OriginalCase
names, to make the tools happy.
This means any pg user who enabled the CasePreserving feature when
creating the database, would not have to redesign their entire database
if they later run into problems with tools, but can simply just switch off
the GUC.

> 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.

Exactly, that trade-off is necessary, otherwise such queries would be ambiguous.

I think a good general philosophy for the PostgreSQL project would be to
try to look at how to meed the needs for new users of new projects
in a way that don't impair things for existing users,
by accepting the new users might have to live with some trade-offs
for their new feature to be possible to implement,
such as in this case that the trade-off is to not be able to create
objects of different casing with the same lowercase names,
a tradeoff that I personally think would not be a problem for most projects,
since it seems unlikely you would both have a "users" table and a
"Users" table in the same database.