Tom Lane [mailto:t...@sss.pgh.pa.us] wrote: > Quite aside from the impact on the server (which would be extensive), it would break every nontrivial application, and force them all to try to deal with each possible folding behavior. I have read through the various threads related to this issue that you supplied. And, it looks quite clear that a change is unlikely. Maybe it is even a bad idea, though personally I think it could prove worth the pain that you obviously anticipate.
However, before fully dropping this issue, I do have one comment I would like to make (just for the record) on your statement that a change in the current behavior would break most non-trivial applications. I assume you are talking about general purpose tools that attempt to interact with any database in any configuration. Obviously, a purpose built tool, such as our own internal database applications, would be designed only for the behavior of the databases it is intended to work against. I have, over the past few months, tried quite a large number (10, maybe a few more than that) of general purpose tools against PostgreSQL looking for replacements for some of the tools we use against our old database server. And, almost none of them work well if I quote identifiers on the server. Almost all work perfectly well if I accept that all my symbols will be converted to lower case and do not quote the identifiers. Most fail - often not even in corner cases - when the catalog (properly) returns a mixed case symbol like WeeklySales. Very few of the tools I have tried seem to know to quote such a symbol to preserve the case when generating a query to send back to the server. Most of the tools I have tested work through ODBC, though a few connect directly to PostgreSQL. Both types of tools have exhibited similar issues. Maybe the ODBC connection is relevant, since it appears that Microsoft SQL Server does what our current server does and preserves case, at least in some modes. A good fraction of the tools I have tried, push their compatibility with SQL Server. So, the current behavior already breaks many tools unless one accepts that all symbols on the server are lower case. At root, based on reading the threads you provided, this probably indicates defects in the tools, rather than a problem with PostgreSQL. My reading of the standard text quoted in various places is that any mixed case identifier returned from the catalog has to be quoted to match in a query (whether you fold to lower or upper case). 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. I fully understand your concern to keep the number of modal behaviors to a minimum. And, the upper case folding looks to have a lot of side effects, and so is a whole lot harder to implement than I expected. Preserving case, which is what we actually want, by contrast still looks pretty easy, though it is clearly not standard compliant. I do not have any more to add on the subject. I just wanted to make this note that a good fraction of third party tools already fail unless one never quotes identifiers on PostgreSQL. Thank you for taking the time to reply to my original inquiry. Ian Lewis www.mstarlabs.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
