Re: PostgreSQL Gotchas - Mailing list pgsql-general

Greg Stark wrote:

>Tom Lane <tgl@sss.pgh.pa.us> writes:
>
>
>
>>If you write, say,
>>
>>    select max(relpages) from pg_class;
>>
>>and the lexer thinks that it should fold unquoted identifiers to upper
>>case, then the catalog entries defining these names had better read
>>PG_CLASS, RELPAGES, and MAX, not the lower-case names they contain
>>today.
>>
>>
>
>Well the case of unquoted identifiers could be finessed by having it match
>RELPAGES first and fail over to relpages second. It could even be made to
>match RelPages and whatever if there isn't any ambiguity.
>
>
Personally I don't see why relpages and pg_class are problems.  After
all, the backend treats identifiers as already quoted internally.  If
you change what the language means, you are going to get backward
compatibility issues.  End of story.  But we have done this sort of
thing before so it is not the end of the world as long as we provide a
way to get back.  Indeed treating identifiers as already quoted should
prevent a huge number of bugs that might otherwise be problematic.  But
because there are no guarantees that the system catalogs won't change
with each new major release, this isn't a problem.  I.e. it isn't the
responsibility of the core development team (IMO) to make this work.
The information_schema is a bigger problem, but I don't see why you
can't have two information schemas with cases to match each behavior.
Sure it means a little more disk space, but I don't really see why it
would be a major issue.

You do have an issue with MAX v. max which could get nasty.  I have
proposed solving this one with a duplicate entry in the system catalogs
with upper names for aggregates and builtin functions.

Basically, if what I have read is accurate, this seems to be the least
amount of work to make the following statement work:
SELECT max("relpages") FROM "pg_class";

What we don't want to see is the requirement for a statement even less
standard, like:
SELECT "max"("relpage") FROM "pg_class";

Note that if you allow system catalogs to be created in upper case, and
the backend treats identifiers as already double-quoted, then you have
to go through and case fold these again, which is problematic, impacts
performance, and adds the potential for many bugs.

>I think the problem case arises when you have code that has a quoted
>"relpages". In that case the code is just not going to work unless the column
>really is named "relpages" not "RELPAGES".
>
>So to be completely backward compatible you need "relpages" as well as an
>unquoted relpages to work. And to be spec compliant you need "RELPAGES" to
>work too. That makes things a bit sticky.
>
>
Is this really a requirement given that the system catalogs are not
guaranteed to be backwards compatible and have been changed in the past?

Best Wishes,
Chris Travers