Re: PostgreSQL Gotchas

On Sat, Oct 15, 2005 at 05:46:03PM -0700, Chris Travers wrote:
> 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.

I think you're certainly waving away a lot of potential issues with saying
that "as long as system identifiers are quoted we're fine". Tom's
example is just the tip of the iceberg.

- consider functions like nextval/currval/substring. Some of these are
created and used internally too, should they always be quoted? Would
these functions all appear twice in the functions list?
- consider oid,xmin,xmax,etc are they system identifiers? You can't
provide two copies of those, since each column can only appear once in
the catalogs.
- consider pg_dump, psql and other such tools with intimate knowledge
of the catalogs, they would all need to updated.
- consider the number of third party apps that uses these "system
identifiers". Updating all of those is a massive task, for what gain?

There is some sympathy around here for the view that in this case the
standard is simply wrong. As said on this thread already, uppercase
only was appropriate for twenty years ago but not now. Older
programming languages tended to have their keywords in uppercase, yet
no modern language would consider such an act.

So, I think in the long run there is going to be some sympathy for
helping people with "QUOTEDUPPERCASE" identifiers to port their stuff
but I don't think PostgreSQL itself will ever default to folding up.
Hence the discussion about folding quoted identifiers also, which
solves the issue without masses of changes everywhere.

Curiously, MySQL does as we do, although they don't accept standard
quoting at all (using backquotes). Do people complain about them too?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.