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 <br...@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 + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
