Thread: Identifier case folding notes

I have had some idle thoughts on the issue of identifier case folding.  Maybe 
we can collect our ideas and inch closer to a solution sometime.  Or we 
determine that it's useless and impossible, but then I can at least collect 
that result in the wiki and point future users to it.

Background: PostgreSQL folds unquoted identifiers to lower case.  SQL 
specifies folding to upper case, and most other SQL DBMS do it that way.  If 
an application mixes referring to an object using quoted and unquoted 
versions of an identifier, then incompatibilities arise.  We have always 
stressed to users that one should refer to an object always unquoted or 
always quoted.  While that remains a good suggestion for a number of reasons, 
we have seen occasional complaints that some closed source applications 
violate this rule and therefore cannot be run on PostgreSQL.

A number of solutions have been proposed over time, which I summarize here:

1. Change the lexer to fold to upper case, as it is supposed to do according 
to the SQL standard.  This will break almost everything, because almost all 
built-in objects have lower-case names and thus couldn't be referred to 
anymore except by quoting.  Changing the names of all the internal objects to 
upper-case names would involve vast code changes, probably break just as much, 
and make everything uglier.  So this approach is unworkable.

2. Fold to upper case, but not when referring built-in objects.  The lexer 
generally doesn't know what a name will refer to, so this is not possible to 
implement, at least without resorting to lots of hard-coding or horrid 
kludges.  Also, a behavior like this will probably create all kinds of weird 
inconsistencies, resulting from putting catalog knowledge in the lexer.

3 and 4 -- Two variants of "ignore case altogether":

3. Fold all identifiers to lower case, even quoted ones.  This would probably 
in fact fix the breakage of many of the above-mentioned problem applications, 
and it would in general be very easy to understand for a user.  And it could 
be implemented in about three lines.  One disadvantage is that one could no 
longer have objects that have names different only by case, but that is 
probably rare and incredibly stupid and can be neglected.  The main 
disadvantage is that the case of identifiers and in particular column labels 
is lost.  So applications and programming interfaces that look up result 
columns in a case-sensitive manner would fail.  And things like SELECT expr AS 
"Nice Heading" won't work properly anymore.

4. Compare the "name" data type in a case-insensitive manner.  This would 
probably address most problem cases.  Again, you can't have objects with names 
different in case only.  One condition to implementing this would be that this 
behavior would have be tied down globally at initdb, because it affects system 
indexes and shared catalogs.  That might be impractical for some, because 
you'd need different instances for different behaviors, especially when you 
want to host multiple applications or want to port an affected application to 
the native PostgreSQL behavior over time.

5. One additional approach I thought of is that you swap the case of 
identifiers as you lex them (upper to lower, lower to upper), and then swap 
them back when you send them to the client.  This needs a small change in the 
lexer, one for sending the RowDescription, and support in pg_dump and a few 
other places if desired.  There will, however, be a number of weird, albeit 
self-imposed, side-effects.  I have implemented a little test patch for this.  
It's weird, but it works in basic ways.

Obviously, no solution will ever work completely.  And we probably don't want 
such a solution, because it would create two different and incompatible 
PostgreSQL universes.  If we are aiming for a solution that would allow most 
affected applications to hobble along, we would probably serve most users.  
Implementing some or all of 3, 4, and 5 would probably achieve that.

Comments?

On Tue, 2008-07-08 at 19:25 +0200, Peter Eisentraut wrote:
> 4. Compare the "name" data type in a case-insensitive manner.  This
> would probably address most problem cases.  Again, you can't have
> objects with names 
> different in case only.  One condition to implementing this would be
> that this 
> behavior would have be tied down globally at initdb, because it
> affects system 
> indexes and shared catalogs.  That might be impractical for some,
> because 
> you'd need different instances for different behaviors, especially
> when you 
> want to host multiple applications or want to port an affected
> application to 
> the native PostgreSQL behavior over time.

That sounds the most workable, given your descriptions.

If objects are never different solely by case alone, then you will have
the same index ordering as if you had sent them all to lower case.

Surely it is possible to mix the two approaches somehow?

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

Peter Eisentraut <peter_e@gmx.net> writes:
> I have had some idle thoughts on the issue of identifier case folding.
> ...
> Comments?

IMHO, practically the only solid argument for changing from the way
we do things now is "to meet the letter of the spec".  The various sorts
of gamesmanship you list would most definitely not meet the letter of
the spec; between that and the inevitability of breaking some apps,
I'm inclined to reject them all on sight.

What I think would perhaps be worth investigating is a compile-time
(or at latest initdb-time) option that flips the case folding behavior
to SQL-spec-compliant and also changes all the built-in catalog entries
to upper case.  We would then have a solution we could offer to people
who really need to run apps that depend on SQL-spec case folding ...
and if the upper case hurts their eyes, or breaks some other apps that
they wish they could run in the same DB, well it's their problem.

Of course there would be large amounts of work to try to make psql,
pg_dump, etc behave as nicely as possible with either case-folding rule,
but it doesn't strike me as being so obviously "unworkable" as to be
dismissed at once.
        regards, tom lane

Am Dienstag, 8. Juli 2008 schrieb Tom Lane:
> IMHO, practically the only solid argument for changing from the way
> we do things now is "to meet the letter of the spec".

Well no.  As I have mentioned, there have actually been occasional complaints 
by people who can't run their code generated by closed-source applications, 
because they handle the case differently.

Peter Eisentraut <peter_e@gmx.net> writes:
> Am Dienstag, 8. Juli 2008 schrieb Tom Lane:
>> IMHO, practically the only solid argument for changing from the way
>> we do things now is "to meet the letter of the spec".

> Well no.  As I have mentioned, there have actually been occasional complaints
> by people who can't run their code generated by closed-source applications, 
> because they handle the case differently.

Sure, otherwise we wouldn't really be worrying about this.  But if
someone comes to us and says "this closed source app requires some
weird non-spec-compliant case folding rule, please make Postgres
do that", we're going to say no.  Their argument only has weight
if they say their app expects the SQL-spec behavior.
        regards, tom lane

Tom,

> IMHO, practically the only solid argument for changing from the way
> we do things now is "to meet the letter of the spec".  The various sorts
> of gamesmanship you list would most definitely not meet the letter of
> the spec; between that and the inevitability of breaking some apps,
> I'm inclined to reject them all on sight.

Actually, there are a number of *very* popular database tools, particularly 
in the Java world (such as Netbeans and BIRT) which do mix quoted and 
unquoted identifiers.  In general, users of those tools reject PostgreSQL 
as "broken" for our nonstandard behavoir rather than trying to work around 
it.

So it's not just a standards issue; this problem really *is* hurting us in 
adoption.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On Tuesday 08 July 2008 23:04:51 Josh Berkus wrote:
> Tom,
> 
> > IMHO, practically the only solid argument for changing from the way
> > we do things now is "to meet the letter of the spec".  The various sorts
> > of gamesmanship you list would most definitely not meet the letter of
> > the spec; between that and the inevitability of breaking some apps,
> > I'm inclined to reject them all on sight.
> 
> Actually, there are a number of *very* popular database tools, particularly 
> in the Java world (such as Netbeans and BIRT) which do mix quoted and 
> unquoted identifiers.  In general, users of those tools reject PostgreSQL 
> as "broken" for our nonstandard behavoir rather than trying to work around 
> it.
> 
> So it's not just a standards issue; this problem really *is* hurting us in 
> adoption.
> 
> -- 
> --Josh
> 
> Josh Berkus
> PostgreSQL @ Sun
> San Francisco

Right. From a user's perspective 4) sounds best. I often run into problems having keywords as column-names:

andreak=# create table test(user varchar);
ERROR:  syntax error at or near "user"
LINE 1: create table test(user varchar);                         ^
andreak=# create table test("user" varchar);
CREATE TABLE
andreak=# insert into test("USER") values('testuser');
ERROR:  column "USER" of relation "test" does not exist
LINE 1: insert into test("USER") values('testuser');                        ^
andreak=# insert into test(user) values('testuser');
ERROR:  syntax error at or near "user"
LINE 1: insert into test(user) values('testuser');                        ^
andreak=# insert into test("user") values('testuser');
INSERT 0 1

As you know, the only way of referring to the "user"-column is to qoute it in lowercase, which many apps and tools
don'tdo.
 

-- 
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager

>>> Josh Berkus <josh@agliodbs.com> wrote:
> Actually, there are a number of *very* popular database tools,
particularly 
> in the Java world (such as Netbeans and BIRT) which do mix quoted and

> unquoted identifiers.  In general, users of those tools reject
PostgreSQL 
> as "broken" for our nonstandard behavoir rather than trying to work
around 
> it.
Do these tools expect an unquoted identifier to be treated according
to the standard?  As I read it, an unquoted identifier should be
treated identically to the same identifier folded to uppercase and
wrapped in quotes, except that it will be guaranteed to be considered
an identifier, rather than possibly considered as a reserved word,
etc.
From our perspective, we're OK with the status quo since we always
quote all identifiers.  I don't think any of the suggestions would
bite us (if implemented bug-free) because we also forbid names which
differ only in capitalization.  We help out our programmers by letting
them ignore quoting (except identifiers which are reserved words) and
capitalization when they write queries in our tool; we correct the
capitalization and wrap the identifiers in quotes as we generate the
Java query classes.  Doing something like that in psql autocompletion
and in other PostgreSQL tools would be a nice feature, if practicable.
-Kevin

Tom Lane wrote:
> What I think would perhaps be worth investigating is a compile-time
> (or at latest initdb-time) option that flips the case folding behavior
> to SQL-spec-compliant and also changes all the built-in catalog entries
> to upper case.  We would then have a solution we could offer to people
> who really need to run apps that depend on SQL-spec case folding ...
> and if the upper case hurts their eyes, or breaks some other apps that
> they wish they could run in the same DB, well it's their problem.

+1 for a compile-time option for spec-compliant behavior.   Even
where the spec is stupid (timestamp with time zone literals) it'd
be nice to have the option; both for feature completeness
checklists and for teachers who want to teach targeting the spec.

>>> Ron Mayer <rm_pg@cheapcomplexdevices.com> wrote: 
> +1 for a compile-time option for spec-compliant behavior.   Even
> where the spec is stupid (timestamp with time zone literals) it'd
> be nice to have the option; both for feature completeness
> checklists and for teachers who want to teach targeting the spec.
In my world it would be even more important for feature completeness
itself, and for production applications written to the spec for
portability.  But, agreed: +1
-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Josh Berkus <josh@agliodbs.com> wrote:
>> Actually, there are a number of *very* popular database tools,
>> particularly 
>> in the Java world (such as Netbeans and BIRT) which do mix quoted and
>> unquoted identifiers.

> Do these tools expect an unquoted identifier to be treated according
> to the standard?

Unfortunately, they almost certainly don't.  I'd bet long odds that
what they expect is mysql's traditional behavior, which is not even
within hailing distance of being spec compliant.  (In a quick test,
it looks like mysql 5.0's default behavior is never to fold case
at all; and then there's the problem that they use the wrong kind
of quotes ...)
        regards, tom lane

Andreas Joseph Krogh <andreak@officenet.no> writes:
> Right. From a user's perspective 4) sounds best. I often run into problems having keywords as column-names:

None of the proposals on the table will remove the need to use quotes in
that case.
        regards, tom lane

Peter Eisentraut wrote:
> I have had some idle thoughts on the issue of identifier case folding.  Maybe 
> we can collect our ideas and inch closer to a solution sometime.  Or we 
> determine that it's useless and impossible, but then I can at least collect 
> that result in the wiki and point future users to it.
>
> Background: PostgreSQL folds unquoted identifiers to lower case.  SQL 
> specifies folding to upper case, and most other SQL DBMS do it that way.  If 
> an application mixes referring to an object using quoted and unquoted 
> versions of an identifier, then incompatibilities arise.  We have always 
> stressed to users that one should refer to an object always unquoted or 
> always quoted.  While that remains a good suggestion for a number of reasons, 
> we have seen occasional complaints that some closed source applications 
> violate this rule and therefore cannot be run on PostgreSQL.
>
> A number of solutions have been proposed over time, which I summarize here:
>
> 1. Change the lexer to fold to upper case, as it is supposed to do according 
> to the SQL standard.  This will break almost everything, because almost all 
> built-in objects have lower-case names and thus couldn't be referred to 
> anymore except by quoting.  Changing the names of all the internal objects to 
> upper-case names would involve vast code changes, probably break just as much, 
> and make everything uglier.  So this approach is unworkable.
>
> 2. Fold to upper case, but not when referring built-in objects.  The lexer 
> generally doesn't know what a name will refer to, so this is not possible to 
> implement, at least without resorting to lots of hard-coding or horrid 
> kludges.  Also, a behavior like this will probably create all kinds of weird 
> inconsistencies, resulting from putting catalog knowledge in the lexer.
>
> 3 and 4 -- Two variants of "ignore case altogether":
>
> 3. Fold all identifiers to lower case, even quoted ones.  This would probably 
> in fact fix the breakage of many of the above-mentioned problem applications, 
> and it would in general be very easy to understand for a user.  And it could 
> be implemented in about three lines.  One disadvantage is that one could no 
> longer have objects that have names different only by case, but that is 
> probably rare and incredibly stupid and can be neglected.  The main 
> disadvantage is that the case of identifiers and in particular column labels 
> is lost.  So applications and programming interfaces that look up result 
> columns in a case-sensitive manner would fail.  And things like SELECT expr AS 
> "Nice Heading" won't work properly anymore.
>
> 4. Compare the "name" data type in a case-insensitive manner.  This would 
> probably address most problem cases.  Again, you can't have objects with names 
> different in case only.  One condition to implementing this would be that this 
> behavior would have be tied down globally at initdb, because it affects system 
> indexes and shared catalogs.  That might be impractical for some, because 
> you'd need different instances for different behaviors, especially when you 
> want to host multiple applications or want to port an affected application to 
> the native PostgreSQL behavior over time.
>
> 5. One additional approach I thought of is that you swap the case of 
> identifiers as you lex them (upper to lower, lower to upper), and then swap 
> them back when you send them to the client.  This needs a small change in the 
> lexer, one for sending the RowDescription, and support in pg_dump and a few 
> other places if desired.  There will, however, be a number of weird, albeit 
> self-imposed, side-effects.  I have implemented a little test patch for this.  
> It's weird, but it works in basic ways.
>
> Obviously, no solution will ever work completely.  And we probably don't want 
> such a solution, because it would create two different and incompatible 
> PostgreSQL universes.  If we are aiming for a solution that would allow most 
> affected applications to hobble along, we would probably serve most users.  
> Implementing some or all of 3, 4, and 5 would probably achieve that.
>
>   


I'm not sure if you've read all the archive history on this. Here are 
the pointers from the TODO list:

http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php 
http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php 
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php


The fact is that we have substantial groups of users who want different 
things:
. current users who want no change so there is no breakage in existing apps
. users on other DBs who want Spec compliance
. users on yet other DBs who want case preservation

The last group should not be lightly dismissed - it is quite common 
behaviour on MSSQL as well as on MySQL, so we have some incentive to 
make this possible to encourage migration.

I'm strongly of the opinion therefore that this should be behaviour 
determined at initdb time (can't make it later because of shared 
catalogs). I suspect that we won't be able to do all this by simple 
transformations in the lexer, unlike what we do now. But I do think it's 
worth doing.

cheers

andrew

"Peter Eisentraut" <peter_e@gmx.net> writes:

> One disadvantage is that one could no longer have objects that have names
> different only by case, but that is probably rare and incredibly stupid and
> can be neglected.

Certainly not if you hope to claim being within a mile of spec -- which seems
like the only point of fiddling with this. Breaking this would take as further
from spec-compliance than we are today.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!

Andrew Dunstan wrote:
> I'm not sure if you've read all the archive history on this. Here are
> the pointers from the TODO list:
>
> http://archives.postgresql.org/pgsql-hackers/2004-04/msg00818.php
> http://archives.postgresql.org/pgsql-hackers/2006-10/msg01527.php
> http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php
>
>
> The fact is that we have substantial groups of users who want
> different things:
> . current users who want no change so there is no breakage in existing
> apps
> . users on other DBs who want Spec compliance
> . users on yet other DBs who want case preservation
>
> The last group should not be lightly dismissed - it is quite common
> behaviour on MSSQL as well as on MySQL, so we have some incentive to
> make this possible to encourage migration.
>
> I'm strongly of the opinion therefore that this should be behaviour
> determined at initdb time (can't make it later because of shared
> catalogs). I suspect that we won't be able to do all this by simple
> transformations in the lexer, unlike what we do now. But I do think
> it's worth doing.
>
> cheers
>
> andrew
>
Hi,

as part of the
http://archives.postgresql.org/pgsql-hackers/2008-03/msg00849.php
thread, I did a reasonable amount of discovery work on making the 3
options andrew presents a reality.  As I'm not skilled enough I never
got far enough to make them all work at once.  I did however get lower
case and case preservation working.

To make those tow work the catalogs need no changes.  Some of the
regressions tests expect case folding, so they did need changing to
operate correctly.  I was unable to make the input files to initdb
correctly fold the case of system catalogs for the upper case version.
I'm sure somebody with more experience would not find it as difficult as
I did.  Function names tended to be where all the gotchas were.  Count()
vs count() vs COUNT() for example.

Once the db was up and running, the issue becomes all the supporting
tools.  psql was made to autocomplete with case preservation, I was
going to make pg_dump just quote everything.  I then got to the point of
adding a fixed GUC like LC_LOCALE that allows psql to read the case
folding situation and act according.  That is where my progress ended.

Attached is what i had worked in.  It's a patch against 8.3.1.  I know
it's not CVS head, but it is what I was using at the time to experiment.

Regards

Russell
=== modified file 'src/backend/access/transam/xlog.c'
--- src/backend/access/transam/xlog.c    2008-03-27 12:10:18 +0000
+++ src/backend/access/transam/xlog.c    2008-03-27 14:15:13 +0000
@@ -4040,6 +4040,9 @@
                     PGC_INTERNAL, PGC_S_OVERRIDE);
     SetConfigOption("lc_ctype", ControlFile->lc_ctype,
                     PGC_INTERNAL, PGC_S_OVERRIDE);
+    /* Make the fixed case folding visible as GUC variables, too */
+    SetConfigOption("identifier_case_folding", ControlFile->identifierCaseFolding,
+                    PGC_INTERNAL, PGC_S_OVERRIDE);
 }

 void
@@ -4290,6 +4293,10 @@
     ControlFile->time = checkPoint.time;
     ControlFile->checkPoint = checkPoint.redo;
     ControlFile->checkPointCopy = checkPoint;
+
+    /* Set the case folding option */
+    strncpy(ControlFile->identifierCaseFolding, "preserved", 9);
+
     /* some additional ControlFile fields are set in WriteControlFile() */

     WriteControlFile();

=== modified file 'src/backend/catalog/information_schema.sql'
--- src/backend/catalog/information_schema.sql    2008-03-27 12:10:18 +0000
+++ src/backend/catalog/information_schema.sql    2008-03-27 12:12:15 +0000
@@ -23,7 +23,7 @@
  */

 CREATE SCHEMA information_schema;
-GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
+GRANT usage ON SCHEMA information_schema TO public;
 SET search_path TO information_schema, public;


@@ -33,7 +33,7 @@

 /* Expand any 1-D array into a set with integers 1..N */
 CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
-    RETURNS SETOF RECORD
+    RETURNS SETOF record
     LANGUAGE sql STRICT IMMUTABLE
     AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
         from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
@@ -214,7 +214,7 @@
 CREATE VIEW information_schema_catalog_name AS
     SELECT CAST(current_database() AS sql_identifier) AS catalog_name;

-GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
+GRANT SELECT ON information_schema_catalog_name TO public;


 /*
@@ -241,9 +241,9 @@
     FROM pg_auth_members m
          JOIN pg_authid a ON (m.member = a.oid)
          JOIN pg_authid b ON (m.roleid = b.oid)
-    WHERE pg_has_role(a.oid, 'USAGE');
+    WHERE pg_has_role(a.oid, 'usage');

-GRANT SELECT ON applicable_roles TO PUBLIC;
+GRANT SELECT ON applicable_roles TO public;


 /*
@@ -256,7 +256,7 @@
     FROM applicable_roles
     WHERE is_grantable = 'YES';

-GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
+GRANT SELECT ON administrable_role_authorizations TO public;


 /*
@@ -353,7 +353,7 @@
           AND a.attnum > 0 AND NOT a.attisdropped
           AND c.relkind in ('c');

-GRANT SELECT ON attributes TO PUBLIC;
+GRANT SELECT ON attributes TO public;


 /*
@@ -384,9 +384,9 @@
       AND d.refobjid = p.oid
       AND d.refclassid = 'pg_catalog.pg_proc'::regclass
       AND p.pronamespace = np.oid
-      AND pg_has_role(p.proowner, 'USAGE');
+      AND pg_has_role(p.proowner, 'usage');

-GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
+GRANT SELECT ON check_constraint_routine_usage TO public;


 /*
@@ -404,7 +404,7 @@
            LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
            LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
            LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
-    WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
+    WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'usage')
       AND con.contype = 'c'

     UNION
@@ -422,9 +422,9 @@
       AND NOT a.attisdropped
       AND a.attnotnull
       AND r.relkind = 'r'
-      AND pg_has_role(r.relowner, 'USAGE');
+      AND pg_has_role(r.relowner, 'usage');

-GRANT SELECT ON check_constraints TO PUBLIC;
+GRANT SELECT ON check_constraints TO public;


 /*
@@ -475,9 +475,9 @@
           AND c.relkind IN ('r', 'v')
           AND a.attnum > 0
           AND NOT a.attisdropped
-          AND pg_has_role(t.typowner, 'USAGE');
+          AND pg_has_role(t.typowner, 'usage');

-GRANT SELECT ON column_domain_usage TO PUBLIC;
+GRANT SELECT ON column_domain_usage TO public;


 /*
@@ -505,7 +505,7 @@
          (
            SELECT oid, rolname FROM pg_authid
            UNION ALL
-           SELECT 0::oid, 'PUBLIC'
+           SELECT 0::oid, 'public'
          ) AS grantee (oid, rolname),
          (SELECT 'SELECT' UNION ALL
           SELECT 'INSERT' UNION ALL
@@ -519,11 +519,11 @@
           AND c.relkind IN ('r', 'v')
           AND aclcontains(c.relacl,
                           makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
-          AND (pg_has_role(u_grantor.oid, 'USAGE')
-               OR pg_has_role(grantee.oid, 'USAGE')
-               OR grantee.rolname = 'PUBLIC');
+          AND (pg_has_role(u_grantor.oid, 'usage')
+               OR pg_has_role(grantee.oid, 'usage')
+               OR grantee.rolname = 'public');

-GRANT SELECT ON column_privileges TO PUBLIC;
+GRANT SELECT ON column_privileges TO public;


 /*
@@ -549,9 +549,9 @@
           AND a.atttypid = t.oid
           AND nc.oid = c.relnamespace
           AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
-          AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
+          AND pg_has_role(coalesce(bt.typowner, t.typowner), 'usage');

-GRANT SELECT ON column_udt_usage TO PUBLIC;
+GRANT SELECT ON column_udt_usage TO public;


 /*
@@ -670,13 +670,13 @@

           AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')

-          AND (pg_has_role(c.relowner, 'USAGE')
+          AND (pg_has_role(c.relowner, 'usage')
                OR has_table_privilege(c.oid, 'SELECT')
                OR has_table_privilege(c.oid, 'INSERT')
                OR has_table_privilege(c.oid, 'UPDATE')
                OR has_table_privilege(c.oid, 'REFERENCES') );

-GRANT SELECT ON columns TO PUBLIC;
+GRANT SELECT ON columns TO public;


 /*
@@ -726,9 +726,9 @@

       ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)

-    WHERE pg_has_role(x.tblowner, 'USAGE');
+    WHERE pg_has_role(x.tblowner, 'usage');

-GRANT SELECT ON constraint_column_usage TO PUBLIC;
+GRANT SELECT ON constraint_column_usage TO public;


 /*
@@ -751,9 +751,9 @@
           AND ( (c.contype = 'f' AND c.confrelid = r.oid)
              OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
           AND r.relkind = 'r'
-          AND pg_has_role(r.relowner, 'USAGE');
+          AND pg_has_role(r.relowner, 'usage');

-GRANT SELECT ON constraint_table_usage TO PUBLIC;
+GRANT SELECT ON constraint_table_usage TO public;


 -- 5.23 DATA_TYPE_PRIVILEGES view appears later.
@@ -796,7 +796,7 @@
           AND n.oid = t.typnamespace
           AND t.oid = con.contypid;

-GRANT SELECT ON domain_constraints TO PUBLIC;
+GRANT SELECT ON domain_constraints TO public;


 /*
@@ -819,9 +819,9 @@
           AND t.typbasetype = bt.oid
           AND bt.typnamespace = nbt.oid
           AND t.typtype = 'd'
-          AND pg_has_role(bt.typowner, 'USAGE');
+          AND pg_has_role(bt.typowner, 'usage');

-GRANT SELECT ON domain_udt_usage TO PUBLIC;
+GRANT SELECT ON domain_udt_usage TO public;


 /*
@@ -903,7 +903,7 @@
           AND bt.typnamespace = nbt.oid
           AND t.typtype = 'd';

-GRANT SELECT ON domains TO PUBLIC;
+GRANT SELECT ON domains TO public;


 -- 5.28 ELEMENT_TYPES view appears later.
@@ -917,9 +917,9 @@
 CREATE VIEW enabled_roles AS
     SELECT CAST(a.rolname AS sql_identifier) AS role_name
     FROM pg_authid a
-    WHERE pg_has_role(a.oid, 'USAGE');
+    WHERE pg_has_role(a.oid, 'usage');

-GRANT SELECT ON enabled_roles TO PUBLIC;
+GRANT SELECT ON enabled_roles TO public;


 /*
@@ -963,7 +963,7 @@
                 AND c.contype IN ('p', 'u', 'f')
                 AND r.relkind = 'r'
                 AND (NOT pg_is_other_temp_schema(nr.oid))
-                AND (pg_has_role(r.relowner, 'USAGE')
+                AND (pg_has_role(r.relowner, 'usage')
                      OR has_table_privilege(r.oid, 'SELECT')
                      OR has_table_privilege(r.oid, 'INSERT')
                      OR has_table_privilege(r.oid, 'UPDATE')
@@ -972,7 +972,7 @@
           AND a.attnum = (ss.x).x
           AND NOT a.attisdropped;

-GRANT SELECT ON key_column_usage TO PUBLIC;
+GRANT SELECT ON key_column_usage TO public;


 /*
@@ -1044,11 +1044,11 @@
                  _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
           FROM pg_namespace n, pg_proc p
           WHERE n.oid = p.pronamespace
-                AND (pg_has_role(p.proowner, 'USAGE') OR
+                AND (pg_has_role(p.proowner, 'usage') OR
                      has_function_privilege(p.oid, 'EXECUTE'))) AS ss
     WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;

-GRANT SELECT ON parameters TO PUBLIC;
+GRANT SELECT ON parameters TO public;


 /*
@@ -1109,9 +1109,9 @@
     WHERE c.relkind = 'r'
           AND con.contype = 'f'
           AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
-          AND pg_has_role(c.relowner, 'USAGE');
+          AND pg_has_role(c.relowner, 'usage');

-GRANT SELECT ON referential_constraints TO PUBLIC;
+GRANT SELECT ON referential_constraints TO public;


 /*
@@ -1152,7 +1152,7 @@
           AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
                OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_column_grants TO PUBLIC;
+GRANT SELECT ON role_column_grants TO public;


 /*
@@ -1186,7 +1186,7 @@
           AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
                OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_routine_grants TO PUBLIC;
+GRANT SELECT ON role_routine_grants TO public;


 /*
@@ -1225,7 +1225,7 @@
           AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles)
                OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles));

-GRANT SELECT ON role_table_grants TO PUBLIC;
+GRANT SELECT ON role_table_grants TO public;


 /*
@@ -1241,7 +1241,7 @@
  * ROLE_USAGE_GRANTS view
  */

--- See USAGE_PRIVILEGES.
+-- See usage_PRIVILEGES.

 CREATE VIEW role_usage_grants AS
     SELECT CAST(null AS sql_identifier) AS grantor,
@@ -1250,12 +1250,12 @@
            CAST(null AS sql_identifier) AS object_schema,
            CAST(null AS sql_identifier) AS object_name,
            CAST(null AS character_data) AS object_type,
-           CAST('USAGE' AS character_data) AS privilege_type,
+           CAST('usage' AS character_data) AS privilege_type,
            CAST(null AS character_data) AS is_grantable

     WHERE false;

-GRANT SELECT ON role_usage_grants TO PUBLIC;
+GRANT SELECT ON role_usage_grants TO public;


 /*
@@ -1300,17 +1300,17 @@
          (
            SELECT oid, rolname FROM pg_authid
            UNION ALL
-           SELECT 0::oid, 'PUBLIC'
+           SELECT 0::oid, 'public'
          ) AS grantee (oid, rolname)

     WHERE p.pronamespace = n.oid
           AND aclcontains(p.proacl,
                           makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false))
-          AND (pg_has_role(u_grantor.oid, 'USAGE')
-               OR pg_has_role(grantee.oid, 'USAGE')
-               OR grantee.rolname = 'PUBLIC');
+          AND (pg_has_role(u_grantor.oid, 'usage')
+               OR pg_has_role(grantee.oid, 'usage')
+               OR grantee.rolname = 'public');

-GRANT SELECT ON routine_privileges TO PUBLIC;
+GRANT SELECT ON routine_privileges TO public;


 /*
@@ -1388,7 +1388,7 @@
            CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
              AS routine_body,
            CAST(
-             CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
+             CASE WHEN pg_has_role(p.proowner, 'usage') THEN p.prosrc ELSE null END
              AS character_data) AS routine_definition,
            CAST(
              CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
@@ -1444,10 +1444,10 @@

     WHERE n.oid = p.pronamespace AND p.prolang = l.oid
           AND p.prorettype = t.oid AND t.typnamespace = nt.oid
-          AND (pg_has_role(p.proowner, 'USAGE')
+          AND (pg_has_role(p.proowner, 'usage')
                OR has_function_privilege(p.oid, 'EXECUTE'));

-GRANT SELECT ON routines TO PUBLIC;
+GRANT SELECT ON routines TO public;


 /*
@@ -1464,9 +1464,9 @@
            CAST(null AS sql_identifier) AS default_character_set_name,
            CAST(null AS character_data) AS sql_path
     FROM pg_namespace n, pg_authid u
-    WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
+    WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'usage');

-GRANT SELECT ON schemata TO PUBLIC;
+GRANT SELECT ON schemata TO public;


 /*
@@ -1490,11 +1490,11 @@
     WHERE c.relnamespace = nc.oid
           AND c.relkind = 'S'
           AND (NOT pg_is_other_temp_schema(nc.oid))
-          AND (pg_has_role(c.relowner, 'USAGE')
+          AND (pg_has_role(c.relowner, 'usage')
                OR has_table_privilege(c.oid, 'SELECT')
                OR has_table_privilege(c.oid, 'UPDATE') );

-GRANT SELECT ON sequences TO PUBLIC;
+GRANT SELECT ON sequences TO public;


 /*
@@ -1514,7 +1514,7 @@

 -- Will be filled with external data by initdb.

-GRANT SELECT ON sql_features TO PUBLIC;
+GRANT SELECT ON sql_features TO public;


 /*
@@ -1546,7 +1546,7 @@
 INSERT INTO sql_implementation_info VALUES ('94',    'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters
allowed');
 INSERT INTO sql_implementation_info VALUES ('46',    'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');

-GRANT SELECT ON sql_implementation_info TO PUBLIC;
+GRANT SELECT ON sql_implementation_info TO public;


 /*
@@ -1569,7 +1569,7 @@
 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
 INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');

-GRANT SELECT ON sql_languages TO PUBLIC;
+GRANT SELECT ON sql_languages TO public;


 /*
@@ -1596,7 +1596,7 @@
 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
 INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');

-GRANT SELECT ON sql_packages TO PUBLIC;
+GRANT SELECT ON sql_packages TO public;


 /*
@@ -1666,7 +1666,7 @@
         comments = 'Might be less, depending on character set.'
     WHERE supported_value = 63;

-GRANT SELECT ON sql_sizing TO PUBLIC;
+GRANT SELECT ON sql_sizing TO public;


 /*
@@ -1686,7 +1686,7 @@
     comments        character_data
 ) WITHOUT OIDS;

-GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
+GRANT SELECT ON sql_sizing_profiles TO public;


 /*
@@ -1721,7 +1721,7 @@
           AND c.conrelid = r.oid
           AND r.relkind = 'r'
           AND (NOT pg_is_other_temp_schema(nr.oid))
-          AND (pg_has_role(r.relowner, 'USAGE')
+          AND (pg_has_role(r.relowner, 'usage')
                -- SELECT privilege omitted, per SQL standard
                OR has_table_privilege(r.oid, 'INSERT')
                OR has_table_privilege(r.oid, 'UPDATE')
@@ -1754,7 +1754,7 @@
           AND NOT a.attisdropped
           AND r.relkind = 'r'
           AND (NOT pg_is_other_temp_schema(nr.oid))
-          AND (pg_has_role(r.relowner, 'USAGE')
+          AND (pg_has_role(r.relowner, 'usage')
                OR has_table_privilege(r.oid, 'SELECT')
                OR has_table_privilege(r.oid, 'INSERT')
                OR has_table_privilege(r.oid, 'UPDATE')
@@ -1762,7 +1762,7 @@
                OR has_table_privilege(r.oid, 'REFERENCES')
                OR has_table_privilege(r.oid, 'TRIGGER') );

-GRANT SELECT ON table_constraints TO PUBLIC;
+GRANT SELECT ON table_constraints TO public;


 /*
@@ -1797,7 +1797,7 @@
          (
            SELECT oid, rolname FROM pg_authid
            UNION ALL
-           SELECT 0::oid, 'PUBLIC'
+           SELECT 0::oid, 'public'
          ) AS grantee (oid, rolname),
          (SELECT 'SELECT' UNION ALL
           SELECT 'DELETE' UNION ALL
@@ -1810,11 +1810,11 @@
           AND c.relkind IN ('r', 'v')
           AND aclcontains(c.relacl,
                           makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))
-          AND (pg_has_role(u_grantor.oid, 'USAGE')
-               OR pg_has_role(grantee.oid, 'USAGE')
-               OR grantee.rolname = 'PUBLIC');
+          AND (pg_has_role(u_grantor.oid, 'usage')
+               OR pg_has_role(grantee.oid, 'usage')
+               OR grantee.rolname = 'public');

-GRANT SELECT ON table_privileges TO PUBLIC;
+GRANT SELECT ON table_privileges TO public;


 /*
@@ -1854,7 +1854,7 @@
     WHERE c.relnamespace = nc.oid
           AND c.relkind IN ('r', 'v')
           AND (NOT pg_is_other_temp_schema(nc.oid))
-          AND (pg_has_role(c.relowner, 'USAGE')
+          AND (pg_has_role(c.relowner, 'usage')
                OR has_table_privilege(c.oid, 'SELECT')
                OR has_table_privilege(c.oid, 'INSERT')
                OR has_table_privilege(c.oid, 'UPDATE')
@@ -1862,7 +1862,7 @@
                OR has_table_privilege(c.oid, 'REFERENCES')
                OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON tables TO PUBLIC;
+GRANT SELECT ON tables TO public;


 /*
@@ -1899,7 +1899,7 @@
            CAST(null AS sql_identifier) AS event_object_column
     WHERE false;

-GRANT SELECT ON triggered_update_columns TO PUBLIC;
+GRANT SELECT ON triggered_update_columns TO public;


 /*
@@ -1912,7 +1912,7 @@

 /*
  * 5.66
- * TRIGGER_ROUTINE_USAGE view
+ * TRIGGER_ROUTINE_e view
  */

 -- not tracked by PostgreSQL
@@ -1975,7 +1975,7 @@
           AND t.tgtype & em.num <> 0
           AND NOT t.tgisconstraint
           AND (NOT pg_is_other_temp_schema(n.oid))
-          AND (pg_has_role(c.relowner, 'USAGE')
+          AND (pg_has_role(c.relowner, 'usage')
                -- SELECT privilege omitted, per SQL standard
                OR has_table_privilege(c.oid, 'INSERT')
                OR has_table_privilege(c.oid, 'UPDATE')
@@ -1983,7 +1983,7 @@
                OR has_table_privilege(c.oid, 'REFERENCES')
                OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON triggers TO PUBLIC;
+GRANT SELECT ON triggers TO public;


 /*
@@ -2005,12 +2005,12 @@

 CREATE VIEW usage_privileges AS
     SELECT CAST(u.rolname AS sql_identifier) AS grantor,
-           CAST('PUBLIC' AS sql_identifier) AS grantee,
+           CAST('public' AS sql_identifier) AS grantee,
            CAST(current_database() AS sql_identifier) AS object_catalog,
            CAST(n.nspname AS sql_identifier) AS object_schema,
            CAST(t.typname AS sql_identifier) AS object_name,
            CAST('DOMAIN' AS character_data) AS object_type,
-           CAST('USAGE' AS character_data) AS privilege_type,
+           CAST('usage' AS character_data) AS privilege_type,
            CAST('NO' AS character_data) AS is_grantable

     FROM pg_authid u,
@@ -2021,7 +2021,7 @@
           AND t.typnamespace = n.oid
           AND t.typtype = 'd';

-GRANT SELECT ON usage_privileges TO PUBLIC;
+GRANT SELECT ON usage_privileges TO public;


 /*
@@ -2066,9 +2066,9 @@
           AND t.relkind IN ('r', 'v')
           AND t.oid = a.attrelid
           AND dt.refobjsubid = a.attnum
-          AND pg_has_role(t.relowner, 'USAGE');
+          AND pg_has_role(t.relowner, 'usage');

-GRANT SELECT ON view_column_usage TO PUBLIC;
+GRANT SELECT ON view_column_usage TO public;


 /*
@@ -2099,9 +2099,9 @@
           AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
           AND dp.refobjid = p.oid
           AND p.pronamespace = np.oid
-          AND pg_has_role(p.proowner, 'USAGE');
+          AND pg_has_role(p.proowner, 'usage');

-GRANT SELECT ON view_routine_usage TO PUBLIC;
+GRANT SELECT ON view_routine_usage TO public;


 /*
@@ -2134,9 +2134,9 @@
           AND dt.refobjid = t.oid
           AND t.relnamespace = nt.oid
           AND t.relkind IN ('r', 'v')
-          AND pg_has_role(t.relowner, 'USAGE');
+          AND pg_has_role(t.relowner, 'usage');

-GRANT SELECT ON view_table_usage TO PUBLIC;
+GRANT SELECT ON view_table_usage TO public;


 /*
@@ -2150,7 +2150,7 @@
            CAST(c.relname AS sql_identifier) AS table_name,

            CAST(
-             CASE WHEN pg_has_role(c.relowner, 'USAGE')
+             CASE WHEN pg_has_role(c.relowner, 'usage')
                   THEN pg_get_viewdef(c.oid)
                   ELSE null END
              AS character_data) AS view_definition,
@@ -2173,7 +2173,7 @@
     WHERE c.relnamespace = nc.oid
           AND c.relkind = 'v'
           AND (NOT pg_is_other_temp_schema(nc.oid))
-          AND (pg_has_role(c.relowner, 'USAGE')
+          AND (pg_has_role(c.relowner, 'usage')
                OR has_table_privilege(c.oid, 'SELECT')
                OR has_table_privilege(c.oid, 'INSERT')
                OR has_table_privilege(c.oid, 'UPDATE')
@@ -2181,7 +2181,7 @@
                OR has_table_privilege(c.oid, 'REFERENCES')
                OR has_table_privilege(c.oid, 'TRIGGER') );

-GRANT SELECT ON views TO PUBLIC;
+GRANT SELECT ON views TO public;


 -- The following views have dependencies that force them to appear out of order.
@@ -2211,7 +2211,7 @@
         SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
       ) AS x (objschema, objname, objtype, objdtdid);

-GRANT SELECT ON data_type_privileges TO PUBLIC;
+GRANT SELECT ON data_type_privileges TO public;


 /*
@@ -2303,4 +2303,4 @@
               ( SELECT object_schema, object_name, object_type, dtd_identifier
                     FROM data_type_privileges );

-GRANT SELECT ON element_types TO PUBLIC;
+GRANT SELECT ON element_types TO public;

=== modified file 'src/backend/catalog/system_views.sql'
--- src/backend/catalog/system_views.sql    2008-03-27 12:10:18 +0000
+++ src/backend/catalog/system_views.sql    2008-03-27 12:12:15 +0000
@@ -184,7 +184,7 @@
     ON UPDATE TO pg_settings
     DO INSTEAD NOTHING;

-GRANT SELECT, UPDATE ON pg_settings TO PUBLIC;
+GRANT SELECT, UPDATE ON pg_settings TO public;

 CREATE VIEW pg_timezone_abbrevs AS
     SELECT * FROM pg_timezone_abbrevs();
@@ -427,7 +427,7 @@
     ) AS tt
 WHERE tt.tokid = parse.tokid
 $$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE sql STRICT STABLE;

 COMMENT ON FUNCTION ts_debug(regconfig,text) IS
     'debug function for text search configuration';
@@ -443,7 +443,7 @@
 $$
     SELECT * FROM pg_catalog.ts_debug( pg_catalog.get_current_ts_config(), $1);
 $$
-LANGUAGE SQL STRICT STABLE;
+LANGUAGE sql STRICT STABLE;

 COMMENT ON FUNCTION ts_debug(text) IS
     'debug function for current text search configuration';

=== modified file 'src/backend/parser/scansup.c'
--- src/backend/parser/scansup.c    2008-03-27 12:10:18 +0000
+++ src/backend/parser/scansup.c    2008-03-27 12:12:15 +0000
@@ -20,6 +20,7 @@
 #include "parser/scansup.h"
 #include "mb/pg_wchar.h"

+char *identifier_case_folding;

 /* ----------------
  *        scanstr
@@ -130,9 +131,25 @@
 {
     char       *result;
     int            i;
+    int         folding;

     result = palloc(len + 1);

+    if (identifier_case_folding == NULL)
+    {
+        folding = 0;
+    }
+    else if (strcmp(identifier_case_folding, "preserved") == 0)
+    {
+        folding = 0;
+    }
+    else if (strcmp(identifier_case_folding, "lower") == 0)
+    {
+        folding = 1;
+    }
+    else
+        folding = 2;
+
     /*
      * SQL99 specifies Unicode-aware case normalization, which we don't yet
      * have the infrastructure for.  Instead we use tolower() to provide a
@@ -145,11 +162,22 @@
     for (i = 0; i < len; i++)
     {
         unsigned char ch = (unsigned char) ident[i];
+        switch (folding)
+        {
+            case 1:
+                if (ch >= 'A' && ch <= 'Z')
+                    ch += 'a' - 'A';
+                else if (IS_HIGHBIT_SET(ch) && isupper(ch))
+                    ch = tolower(ch);
+                break;
+            case 2:
+                if (ch >= 'a' && ch <= 'z')
+                    ch -= 'a' - 'A';
+                else if (IS_HIGHBIT_SET(ch) && islower(ch))
+                    ch = toupper(ch);
+                break;
+        }

-        if (ch >= 'A' && ch <= 'Z')
-            ch += 'a' - 'A';
-        else if (IS_HIGHBIT_SET(ch) && isupper(ch))
-            ch = tolower(ch);
         result[i] = (char) ch;
     }
     result[i] = '\0';

=== modified file 'src/backend/snowball/snowball_func.sql.in'
--- src/backend/snowball/snowball_func.sql.in    2008-03-27 12:10:18 +0000
+++ src/backend/snowball/snowball_func.sql.in    2008-03-27 12:12:15 +0000
@@ -2,13 +2,13 @@

 SET search_path = pg_catalog;

-CREATE FUNCTION dsnowball_init(INTERNAL)
-    RETURNS INTERNAL AS '$libdir/dict_snowball', 'dsnowball_init'
-LANGUAGE C STRICT;
+CREATE FUNCTION dsnowball_init(internal)
+    RETURNS internal AS '$libdir/dict_snowball', 'dsnowball_init'
+LANGUAGE c STRICT;

-CREATE FUNCTION dsnowball_lexize(INTERNAL, INTERNAL, INTERNAL, INTERNAL)
-    RETURNS INTERNAL AS '$libdir/dict_snowball', 'dsnowball_lexize'
-LANGUAGE C STRICT;
+CREATE FUNCTION dsnowball_lexize(internal, internal, internal, internal)
+    RETURNS internal AS '$libdir/dict_snowball', 'dsnowball_lexize'
+LANGUAGE c STRICT;

 CREATE TEXT SEARCH TEMPLATE snowball
     (INIT = dsnowball_init,

=== modified file 'src/backend/utils/adt/ruleutils.c'
--- src/backend/utils/adt/ruleutils.c    2008-03-27 12:10:18 +0000
+++ src/backend/utils/adt/ruleutils.c    2008-03-27 12:12:15 +0000
@@ -40,6 +40,7 @@
 #include "parser/parse_func.h"
 #include "parser/parse_oper.h"
 #include "parser/parsetree.h"
+#include "parser/scansup.h"
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
@@ -5185,19 +5186,38 @@
     const char *ptr;
     char       *result;
     char       *optr;
+    bool        foldingLower;
+    bool        foldingUpper;
+
+    if (strcmp(identifier_case_folding, "preserved") == 0)
+    {
+        foldingLower = false;
+        foldingUpper = false;
+    }
+    else if (strcmp(identifier_case_folding, "lower") == 0)
+    {
+        foldingLower = true;
+        foldingUpper = false;
+    }
+    else
+    {
+        foldingLower = false;
+        foldingUpper = true;
+    }

     /*
      * would like to use <ctype.h> macros here, but they might yield unwanted
      * locale-specific results...
      */
-    safe = ((ident[0] >= 'a' && ident[0] <= 'z') || ident[0] == '_');
+    safe = ((!foldingUpper && ident[0] >= 'a' && ident[0] <= 'z') || ident[0] == '_' || (!foldingLower && ident[0] >=
'A'&& ident[0] <= 'Z')); 

     for (ptr = ident; *ptr; ptr++)
     {
         char        ch = *ptr;

-        if ((ch >= 'a' && ch <= 'z') ||
+        if ((!foldingUpper && ch >= 'a' && ch <= 'z') ||
             (ch >= '0' && ch <= '9') ||
+            (!foldingLower && ch >= 'A' && ch <= 'Z') ||
             (ch == '_'))
         {
             /* okay */

=== modified file 'src/backend/utils/mb/conversion_procs/Makefile'
--- src/backend/utils/mb/conversion_procs/Makefile    2008-03-27 11:43:42 +0000
+++ src/backend/utils/mb/conversion_procs/Makefile    2008-03-27 14:14:02 +0000
@@ -173,7 +173,7 @@
         func=$$1; shift; \
         obj=$$1; shift; \
         echo "-- $$se --> $$de"; \
-        echo "CREATE OR REPLACE FUNCTION $$func (INTEGER, INTEGER, CSTRING, INTERNAL, INTEGER) RETURNS VOID AS
'$$"libdir"/$$obj','$$func' LANGUAGE C STRICT;"; \ 
+        echo "CREATE OR REPLACE FUNCTION $$func (integer, integer, cstring, internal, integer) RETURNS void AS
'$$"libdir"/$$obj','$$func' LANGUAGE c strict;"; \ 
         echo "DROP CONVERSION pg_catalog.$$name;"; \
         echo "CREATE DEFAULT CONVERSION pg_catalog.$$name FOR '$$se' TO '$$de' FROM $$func;"; \
     done > $@

=== modified file 'src/backend/utils/misc/check_guc'
--- src/backend/utils/misc/check_guc    2008-03-27 12:10:18 +0000
+++ src/backend/utils/misc/check_guc    2008-03-27 12:12:15 +0000
@@ -21,7 +21,7 @@
 pre_auth_delay role seed server_encoding server_version server_version_int \
 session_authorization trace_lock_oidmin trace_lock_table trace_locks trace_lwlocks \
 trace_notify trace_userlocks transaction_isolation transaction_read_only \
-zero_damaged_pages"
+zero_damaged_pages identifier_case_folding"

 ### What options are listed in postgresql.conf.sample, but don't appear
 ### in guc.c?

=== modified file 'src/backend/utils/misc/guc.c'
--- src/backend/utils/misc/guc.c    2008-03-27 12:10:18 +0000
+++ src/backend/utils/misc/guc.c    2008-03-27 14:13:14 +0000
@@ -2447,6 +2447,16 @@
         &TSCurrentConfig,
         "pg_catalog.simple", assignTSCurrentConfig, NULL
     },
+
+    {
+        {"identifier_case_folding", PGC_INTERNAL, COMPAT_OPTIONS_CLIENT,
+            gettext_noop("Shows the identifier case folding. Options are lower, upper or preserve case."),
+            NULL,
+            GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+        },
+        &identifier_case_folding,
+        NULL, NULL, NULL
+    },

 #ifdef USE_SSL
     {

=== modified file 'src/bin/initdb/initdb.c'
--- src/bin/initdb/initdb.c    2008-03-27 12:10:18 +0000
+++ src/bin/initdb/initdb.c    2008-03-27 12:12:15 +0000
@@ -1798,8 +1798,8 @@
         "UPDATE pg_class "
         "  SET relacl = E'{\"=r/\\\\\"$POSTGRES_SUPERUSERNAME\\\\\"\"}' "
         "  WHERE relkind IN ('r', 'v', 'S') AND relacl IS NULL;\n",
-        "GRANT USAGE ON SCHEMA pg_catalog TO PUBLIC;\n",
-        "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n",
+        "GRANT usage ON SCHEMA pg_catalog TO public;\n",
+        "GRANT create, usage ON SCHEMA public TO public;\n",
         NULL
     };


=== modified file 'src/bin/pg_controldata/pg_controldata.c'
--- src/bin/pg_controldata/pg_controldata.c    2008-03-27 12:10:18 +0000
+++ src/bin/pg_controldata/pg_controldata.c    2008-03-27 12:12:15 +0000
@@ -60,7 +60,6 @@
     return _("unrecognized status code");
 }

-
 int
 main(int argc, char *argv[])
 {
@@ -208,6 +207,8 @@
            ControlFile.toast_max_chunk_size);
     printf(_("Date/time type storage:               %s\n"),
            (ControlFile.enableIntTimes ? _("64-bit integers") : _("floating-point numbers")));
+    printf(_("Identifier case folding:              %s\n"),
+           ControlFile.identifierCaseFolding);
     printf(_("Maximum length of locale name:        %u\n"),
            ControlFile.localeBuflen);
     printf(_("LC_COLLATE:                           %s\n"),

=== modified file 'src/include/catalog/pg_control.h'
--- src/include/catalog/pg_control.h    2008-03-27 12:10:18 +0000
+++ src/include/catalog/pg_control.h    2008-03-27 12:12:15 +0000
@@ -22,7 +22,7 @@


 /* Version identifier for this pg_control format */
-#define PG_CONTROL_VERSION    833
+#define PG_CONTROL_VERSION    834

 /*
  * Body of CheckPoint XLOG records.  This is declared here because we keep
@@ -141,6 +141,9 @@
     /* flag indicating internal format of timestamp, interval, time */
     uint32        enableIntTimes; /* int64 storage enabled? */

+    /* This data defines the case folding set by the initdb */
+    char        identifierCaseFolding[10]; /* what case folding option was used at initdb time? */
+
     /* active locales */
     uint32        localeBuflen;
     char        lc_collate[LOCALE_NAME_BUFLEN];

=== modified file 'src/include/parser/scansup.h'
--- src/include/parser/scansup.h    2008-03-27 12:10:18 +0000
+++ src/include/parser/scansup.h    2008-03-27 12:12:15 +0000
@@ -15,6 +15,8 @@
 #ifndef SCANSUP_H
 #define SCANSUP_H

+extern char *identifier_case_folding;
+
 extern char *scanstr(const char *s);

 extern char *downcase_truncate_identifier(const char *ident, int len,

=== modified file 'src/test/regress/expected/bit.out'
--- src/test/regress/expected/bit.out    2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/bit.out    2008-03-27 12:12:15 +0000
@@ -107,7 +107,7 @@
 (4 rows)

 --- Bit operations
-DROP TABLE varbit_table;
+DROP TABLE VARBIT_TABLE;
 CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
 COPY varbit_table FROM stdin;
 SELECT a, b, ~a AS "~ a", a & b AS "a & b",

=== modified file 'src/test/regress/expected/float8.out'
--- src/test/regress/expected/float8.out    2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/float8.out    2008-03-27 12:12:15 +0000
@@ -232,7 +232,7 @@
 (5 rows)

 -- ceil / ceiling
-select ceil(f1) as ceil_f1 from float8_tbl f;
+select ceil(f1) as ceil_f1 from FLOAT8_TBL f;
        ceil_f1
 ----------------------
                     0
@@ -242,7 +242,7 @@
                     1
 (5 rows)

-select ceiling(f1) as ceiling_f1 from float8_tbl f;
+select ceiling(f1) as ceiling_f1 from FLOAT8_TBL f;
       ceiling_f1
 ----------------------
                     0
@@ -253,7 +253,7 @@
 (5 rows)

 -- floor
-select floor(f1) as floor_f1 from float8_tbl f;
+select floor(f1) as floor_f1 from FLOAT8_TBL f;
        floor_f1
 ----------------------
                     0
@@ -264,7 +264,7 @@
 (5 rows)

 -- sign
-select sign(f1) as sign_f1 from float8_tbl f;
+select sign(f1) as sign_f1 from FLOAT8_TBL f;
  sign_f1
 ---------
        0

=== modified file 'src/test/regress/expected/numeric.out'
--- src/test/regress/expected/numeric.out    2008-03-27 12:10:18 +0000
+++ src/test/regress/expected/numeric.out    2008-03-27 12:12:15 +0000
@@ -646,10 +646,10 @@
 (0 rows)

 -- ******************************
--- * POWER(10, LN(value)) check
+-- * power(10, LN(value)) check
 -- ******************************
 DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
+INSERT INTO num_result SELECT id, 0, power(numeric '10', ln(abs(round(val,200))))
     FROM num_data
     WHERE val != '0.0';
 SELECT t1.id1, t1.result, t2.expected

=== modified file 'src/test/regress/sql/bit.sql'
--- src/test/regress/sql/bit.sql    2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/bit.sql    2008-03-27 12:12:15 +0000
@@ -54,7 +54,7 @@
        FROM VARBIT_TABLE;

 --- Bit operations
-DROP TABLE varbit_table;
+DROP TABLE VARBIT_TABLE;
 CREATE TABLE varbit_table (a BIT VARYING(16), b BIT VARYING(16));
 COPY varbit_table FROM stdin;
 X0F    X10
@@ -78,7 +78,7 @@
 DROP TABLE varbit_table;

 --- Bit operations
-DROP TABLE bit_table;
+DROP TABLE BIT_TABLE;
 CREATE TABLE bit_table (a BIT(16), b BIT(16));
 COPY bit_table FROM stdin;
 X0F00    X1000

=== modified file 'src/test/regress/sql/float8.sql'
--- src/test/regress/sql/float8.sql    2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/float8.sql    2008-03-27 12:12:15 +0000
@@ -87,14 +87,14 @@
    FROM FLOAT8_TBL f;

 -- ceil / ceiling
-select ceil(f1) as ceil_f1 from float8_tbl f;
-select ceiling(f1) as ceiling_f1 from float8_tbl f;
+select ceil(f1) as ceil_f1 from FLOAT8_TBL f;
+select ceiling(f1) as ceiling_f1 from FLOAT8_TBL f;

 -- floor
-select floor(f1) as floor_f1 from float8_tbl f;
+select floor(f1) as floor_f1 from FLOAT8_TBL f;

 -- sign
-select sign(f1) as sign_f1 from float8_tbl f;
+select sign(f1) as sign_f1 from FLOAT8_TBL f;

 -- square root
 SELECT sqrt(float8 '64') AS eight;

=== modified file 'src/test/regress/sql/numeric.sql'
--- src/test/regress/sql/numeric.sql    2008-03-27 12:10:18 +0000
+++ src/test/regress/sql/numeric.sql    2008-03-27 12:12:15 +0000
@@ -591,7 +591,7 @@
 -- * Square root check
 -- ******************************
 DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, SQRT(ABS(val))
+INSERT INTO num_result SELECT id, 0, sqrt(abs(val))
     FROM num_data;
 SELECT t1.id1, t1.result, t2.expected
     FROM num_result t1, num_exp_sqrt t2
@@ -602,7 +602,7 @@
 -- * Natural logarithm check
 -- ******************************
 DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, LN(ABS(val))
+INSERT INTO num_result SELECT id, 0, ln(abs(val))
     FROM num_data
     WHERE val != '0.0';
 SELECT t1.id1, t1.result, t2.expected
@@ -614,7 +614,7 @@
 -- * Logarithm base 10 check
 -- ******************************
 DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
+INSERT INTO num_result SELECT id, 0, log(numeric '10', abs(val))
     FROM num_data
     WHERE val != '0.0';
 SELECT t1.id1, t1.result, t2.expected
@@ -623,10 +623,10 @@
     AND t1.result != t2.expected;

 -- ******************************
--- * POWER(10, LN(value)) check
+-- * power(10, ln(value)) check
 -- ******************************
 DELETE FROM num_result;
-INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
+INSERT INTO num_result SELECT id, 0, power(numeric '10', ln(abs(round(val,200))))
     FROM num_data
     WHERE val != '0.0';
 SELECT t1.id1, t1.result, t2.expected
@@ -638,9 +638,9 @@
 -- * miscellaneous checks for things that have been broken in the past...
 -- ******************************
 -- numeric AVG used to fail on some platforms
-SELECT AVG(val) FROM num_data;
-SELECT STDDEV(val) FROM num_data;
-SELECT VARIANCE(val) FROM num_data;
+SELECT avg(val) FROM num_data;
+SELECT stddev(val) FROM num_data;
+SELECT variance(val) FROM num_data;

 -- Check for appropriate rounding and overflow
 CREATE TABLE fract_only (id int, val numeric(4,4));

On Wednesday 09 July 2008 00:35:07 Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > Right. From a user's perspective 4) sounds best. I often run into problems having keywords as column-names:
>
> None of the proposals on the table will remove the need to use quotes in
> that case.

I know, but then tools/frameworks won't fail when they produce queries like SELECT "USER" FROM test, because it sends
"USER"and not "user". 

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Tlf:    +47 24 15 38 90 |                                             |
Fax:    +47 24 15 38 91 |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

Am Mittwoch, 9. Juli 2008 schrieb Gregory Stark:
> "Peter Eisentraut" <peter_e@gmx.net> writes:
> > One disadvantage is that one could no longer have objects that have names
> > different only by case, but that is probably rare and incredibly stupid
> > and can be neglected.
>
> Certainly not if you hope to claim being within a mile of spec

Which I don't.

> -- which seems like the only point of fiddling with this.

No, the point is making more applications run.

> Breaking this would take as further from spec-compliance than we are today.

As far as standards compliance goes, I don't think there is a well-defined 
distance measure, but if you can come up with one, I would actually be quite 
keen on debating this point. :-)  In any case, any of this would only be an 
option anyway.

Hi,

Peter Eisentraut wrote:
...
> 4. Compare the "name" data type in a case-insensitive manner.  This would 
> probably address most problem cases.  Again, you can't have objects with names 
> different in case only.  One condition to implementing this would be that this 
> behavior would have be tied down globally at initdb, because it affects system 
> indexes and shared catalogs.  That might be impractical for some, because 
> you'd need different instances for different behaviors, especially when you 
> want to host multiple applications or want to port an affected application to 
> the native PostgreSQL behavior over time.

The whole stuff as I understand is to fix the behavior with applications 
creating objects without quotes and accessing them "QUOTEDUPPERCASE"?

Would a small script fixing the schema by using rename not fix this for
many applications?

T.

"Tino Wildenhain" <tino@wildenhain.de> writes:

> The whole stuff as I understand is to fix the behavior with applications
> creating objects without quotes and accessing them "QUOTEDUPPERCASE"?
>
> Would a small script fixing the schema by using rename not fix this for
> many applications?

Well there are other circumstances where this can arise

select "FOO" from (select col as foo from bar)

Not to mention temporary objects which are created and dropped inside
functions.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!

Tom,

> Unfortunately, they almost certainly don't.  I'd bet long odds that
> what they expect is mysql's traditional behavior,

Nope.  They're looking for Oracle, which is spec-complaint since they 
wrote that spec.

--Josh