Bringing PostgreSQL torwards the standard regarding case folding

I'm opening a new thread, as the previous one was too nested, and
contained too much emotions.

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/