I'll start by my understanding of a summary of the thread so far. The solution we are seeking would have to satisfy the following conditions:
1. Setting should be on a per-database level. A per-server option is not good enough, and a per-session option is too difficult to implement, with no apparent justifiable return.
2. Old applications already working with PG's lowercase folding should have an option to continue working unmodified for the foreseeable future.
Solutions offered so far, and their status: 1. Tri-state. Folder upper, if failes, fold lower, if succeeds, warn. 2. Dual state. Fold lower or upper. Break if client is broken. 3. Create a database conversion tool to change existing case.
Solution 1:
As currently the case folding is performed disjointed from the actual use of the identifier, this solution requires quite a big amount of work. On the other hand, and on second thought, it's main benefit - gradual transition of applications from one to the other, is not really necessary once you declare the current behaviour as there to stay. Existing applications can simply choose to continue using whatever method they currently use. No need for migration.
As such, I think we can simply state that tri-state migration path solution can be discarded for the time being.
Solution 2:
Obviously, this is the way to go. We will have a dabase attribute that states whether things are lower or upper case there.
Solution 3:
(unrelated to the above)
There seems to be some ambiguity about how to handle the translation. Such a tool seems to require guessing which identifiers are accessed quoted, unquoted, or both. The last option, of course, will never work.
We may need such a tool, for some projects may wish to transform from one way to the other. It seems to me, however, that such a tool can wait a little.
Open issues:
1. What do we do with identifiers in "template1" upon database creation?
2. How do we handle queries to tables belonging the catalog that are shared between databases?
Observation: on a lowercase folding DB, any identifier that is not composed only of lowercase characters MUST can be automatically assumed to be accessed only through quoted mode.
I therefor suggest the following path to a solution:
1. CreateDB will be able to create databases from either type.
2. template1 will be defined to be one or the other. For the sake of this discussion, let's assume it's lowercase (current situation)
3. CreateDB, upon being asked to create a new DB that has uppercase folding, will copy over template1, as it currently does.
4. While copying, it will check each identifier. If the identifier is not lowercase only, it is safe to copy it verbatim.
5. If the identifier is lowercase only, convert it to uppercase only. I am assuming here that the authors of the client code chose an uppercase-folding database, so they should know what they are doing when accessing stuff from the standard offering.
6. I'm not sure what are the shared tables from the catalog. I don't think it so unreasonable to ask anyone doing catalog work to assume that catalog entries are case-sensitive. As such, maybe it's best to just leave the data as is.
7. Column headers, however, will have to have a solution. A point still open in current design.
I'm hoping this summary helps in furthering the discussion.
-- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
