Re: PostgreSQL Gotchas

Jan Wieck wrote:

> On 10/13/2005 2:40 PM, Tom Lane wrote:
>
>> Chris Travers <chris@travelamericas.com> writes:
>>
>>> So basically, the problem is that any fix for case folding would
>>> touch a fair bit of code and possibly cause other problems.
>>> However, I haven't seen anyone worry about performance issues in
>>> such a fix, just that it might be a fair bit of work.
>>
>>
>> More that it's likely to create serious forwards, backwards, and
>> sideways compatibility problems :-(.  The work involved is mostly
>> in figuring out how to deal with that.
>>
>> Since the end reward for all this work would be having to read CATALOGS
>> WRITTEN IN ALL UPPER CASE, none of the key developers seem very
>> interested ...
>
>
> Just an idea ...
>
> if the release that offers UPPER case folding as an option also makes
> sure that all internal and tool references to system catalog objects
> are properly quoted (as they IMHO should be anyway), then it would be
> reduced to a third party tool/application problem accessing the system
> catalog in a database that has this new config option selected.

This is largely what I have been suggesting.  However, you still have
two (small) problems that could be solved fairly easily I think.  Take
Tom's example:

SELECT MAX(relpages) FROM pg_class;

Now, I am perfectly happy to require relpages and pg_class to be quoted
(if you don't want to, there are always views....), however, MAX is also
an identifier, which if I understand Tom correctly is also folded to
lower case.  If you fold MAX to upper case, you may not find the
aggregate.  I don't see an easy answer aside from having separate
entries for "max" and "MAX" which are functionally the same.

Secondly, you have one other area where you could get non-standard
behavior if you require the user to quote lower-case system
identifiers.  This is the information_schema.  Solving this would simply
require a separate INFORMATION_SCHEMA, maybe composed as views off the
views and tables in information_schema.

Finally, if this was such a pressing issue, I am sure it would already
be on the TODO list.  My suggestion is that we put it on the TODO list
simply as a way of ensuring that when people run into this problem, we
are showing that we are open to contributions in this area.  I am sure
that there are firms out there which will run into it eventually because
Oracle is standards-compliant in this area and there are many
applications that people are trying to port over which could run into
these sort of issues.  This is open source and I see no problem with
pushing the work in an area like this back to the contributor who is
helping his/her customer.  After all, this helps companies which do
consulting make money...

If someone can make a case for preserving case in identifiers (which I
doubt can be made given the fact that not all identifiers are created
equal, so is MAX() the same as Max() the same as mAx()?), then we can
put that in the TODO list as well.  The TODO list is already long, so I
don't see any reason not to put an entry there.

Best Wishes,
Chris Travers
Metatron Technology Consulting