On Thu, Feb 16, 2017 at 11:16 PM, Joel Jacobson <j...@trustly.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
