Re: PostgreSQL Gotchas

For the record, I am trying to flesh out my thoughts on this issue.  I
am not asking Tom to do this BTW :-) because I am sure he has more
pressing contributions to make.  However, because it can be an issue for
those migrating from, say, Oracle, it would be good to have a discussion
on what would be minimally required to make this happen.

>
>Well sure, it would only be worthwhile if you could come up with rules that
>complied with the standard 100% of the time that the standard specifies
>behaviour.
>
I doubt that the standard says anything about system catalogs.  If our
system catalogs are in lower case, but case is normally folded to upper
case, then this can create some problems, but if the backend is already
treating identifiers as already quoted when it looks in the catalogs, I
see this as a non-issue.  The system catalogs are not guaranteed to be
backwards compatible anyway.  For those cases of aggregates and
functions where we get problems like not finding the MAX aggregate, we
can solve that by having two MAX aggregates which are identical, but one
is in upper case and the other in lower case.

The same would be required

> But if you could do that and satisfy 99% of the backwards
>compatibility issues including any catalog related issues then it seems like
>it would be worthwhile.
>
>
What you are asking here is functionally impossible if you expect user
queries to operate as is against the catalogs when the semantics of the
language have changed.  When you have semantic issues regarding
standards-compliance, you are going to have to choose between
standards-compliance and backwards-compatibility.  Hence I was
suggesting that an option be provided to the user to choose between
these options.

Scratches head....  I guess if you really wanted to allow the queries to
really go without modifications, I guess you could create a second
(duplicate) PG_CATALOG schema with views like PG_CLASS....  This seems
to me to be iceing on the cake, however, and not part of the minimal
requirements.  Gotta love VIEWs :-)

I guess that would solve every case of backwards-compatibility that I
can find where the problem is not bad application design (of the sort
that currently causes problems).

>But on further thought, if you want to have pg_dump et al output lowercase
>names (which I certainly prefer) then I think what you would have to do is
>have a bit that travels with every identifier that indicates whether it was
>quoted or not.
>
>
If this is what you want, then leave folding to lower case.  Simple :-)

>So two identifiers match if either is an unquoted identifier and they match
>case insensitively. Or if both are quoted and they match case sensitively.
>
>
Sorry, not standards-compliant, as Tom pointed out....

Here are the basic assumptions I would make in such a solution:

1)  We want the option of choosing standards-compliance over
backwards-compatibility.
2)  When in doubt, throw and error and abort the transaction.
3)  NEVER EVER guess as to what the user really meant when the exact
instructions are ambiguous.

If we didn't make these assumptions, we would be using MySQL :-)

Best Wishes,
Chris Travers
Metatron Technology COnsulting