From: Ken Johanson <pg-user@kensystem.com>
To: Bruce Momjian <bruce@momjian.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Martijn van Oosterhout <kleptog@svana.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: [HACKERS] Case Preservation disregarding case
Date: Tue, 05 Dec 2006 21:28:42 -0700
Message-ID: <457646FA.1020804@kensystem.com> (raw)
In-Reply-To: <200612031657.kB3Gv6408322@momjian.us>
References: <200612031657.kB3Gv6408322@momjian.us>
Bruce Momjian wrote:
> Tom Lane wrote:
>>
>> The real bottom line, though, is that this community has little respect
>> for proposals that involve moving away from the SQL spec rather than
>> closer to it; and that's what you're asking us to do. The spec is not
>> at all vague about the case-sensitivity of identifiers. Now certainly
>> we are not doing exactly what the spec says, but what you ask is even
>> less like the spec's requirements.
>
> I think there are two major issues here:
>
> o if you quote identifiers that have any upper-case characters,
> do it both at table creation and use
> o display of non-quoted identifiers is lower-case
>
> I think we are OK making people either always quote, or always not
> quote. What we don't currently have a good answer for is people wanting
> the identifiers displayed using the original case. You can use quotes
> all the time of you want such display, but it is a pain. I think this
> is the crux of the complaints.
>
> Saving the case of the original creation and displaying that does work,
> but then it isn't clear if the identifier needs quotes (is the
> upper-case real or just for display). This gets us into even more
> confusion.
>
> Can someone can think of an answer to all this?
>
I believe there is no one answer that will solve all cases.. but one
solution that might come close is something like this (I take no credit,
others have described this before me):
PG would support two modes of operation:
1) Standard compliant, and;
2) Quirks / compatibility mode (case preserving but case-insensitive)
I believe (and may be wrong) that the #2 mode-of-operation would only
require:
a) resultset data to have it's (unquoted) identifiers to be case-folded,
and;
b) queries with (unquoted) identifiers (joins / aliases etc) would
continue in the planner to be case folded, so would work as now (and in
effect be case-insensitive).
c) the table describe syntax would return the case-preserved id name
(which it already does if quoted?), or d:
d) in addition to a), optionally send metadata describing the
case-preserved name; this might someday allow newer drivers to display
(display only, not column lookup) those names in database-designer views
(iSQL types apps)
If #a is possible, then drivers should not break, even if the DB is
config'd to use setting #2. But I don't know the low-level protocol of
PG to know if that is possible. ..
Hopefully I'm not missing any points here, please correct me if so...
I believe what I described above (while not being standard complaint per
se) is identical to how mysql and mssql work (operationally anyway)
right now.. On the other had Oracle and others work as PG does now, but
my point in discussing this, is that the first two DBs have enough
market, that offering a compatibility mode to ease the burden of porting
apps would have substantial value (I know this from experience)
Ken
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pg-user@kensystem.com, bruce@momjian.us, tgl@sss.pgh.pa.us, kleptog@svana.org
Subject: Re: [HACKERS] Case Preservation disregarding case
In-Reply-To: <457646FA.1020804@kensystem.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox