On Mon, Jan 2, 2017 at 01:25:35PM -0500, Robert Haas wrote:
> > But, I can easily imagine a good number of people deciding they want
> > mixed case on the server, and so quoting their identifiers. And, then
> > deciding PostgreSQL is defective, rather than deciding their favorite
> > administration or query tool is defective. Almost all of the tools I
> > tried worked fine when I had all lower case symbols on the server. Based
> > on observing the generated SQL, most of the tools that failed for me
> > when I had mixed case symbols on the server would work against a case
> > preserving mode in PostgreSQL. The tools generally pass through the
> > catalog reported symbols without manipulation.
>
> Right. Tom's argument makes a lot of sense when you think about this
> from the point of view of someone writing extensions or tools that are
> designed to work with anybody's PostgreSQL instance. When you think
> about it from the point of view of a user wanting to write an
> application that can work with any of a number of databases, then your
> argument has a lot of merit to it. I'm not sure there's any way to
> split the baby here: tool authors will obviously prefer that
> PostgreSQL's behavior in this area be invariable, while people trying
> to develop portable database applications will prefer configurability.
> As far as I can see, this is a zero sum game that is bound to have one
> winner and one loser.
Please let me restate the above. For those working only in the Postgres
ecosystem, the rules are pretty clear --- quote nothing and use only
lowercase, or quote everything. The reason "quote nothing" is
insufficient is that tools like pgAdmin will quote mixed-case
identifiers during object creation, so technically it is difficult to
have pure "quote nothing" behavior in Postgres unless you control all
the tooling.
Now, clearly, we have users coming from non-Postgres databases where the
behavior is different, i.e. Oracle might be "quote nothing and use only
uppercase". It seems SQLAnywhere is "quote nothing and case is
preserved".
The problem with opening Postgres up to user-modifiable case folding is
that Postgres ecosystem queries will have to adjust to the fact that
case folding is no longer predictable. For database applications the
fix might be as easy as changing the session state, but for extensions
and libraries, they would need to quote _everything_ to handle
uncontrollable case folding behavior. So, in a way, the crazy quoting
we are trying to avoid for migrated queries now happens in the Postgres
ecosystem, and we might even have more of it.
This basically pushes the quoting overhead from users moving to Postgres
from other databases to Postgres ecosystem tooling. Whether that is
better or worse overall is a judgement call, as Robert stated.
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +