Re: WIP patch: Collation support - Mailing list pgsql-hackers

Martijn van Oosterhout wrote:
> On Wed, Sep 10, 2008 at 12:51:02PM +0300, Heikki Linnakangas wrote:
>>> Since the set of collations isn't exactly denumerable, we need some way
>>> to allow the user to specify the collation they want. The only
>>> collation PostgreSQL knows about is the C collation. Anything else is
>>> user-defined.
>> Let's just use the name of the OS locale, like we do now. Having a
>> pg_collation catalog just moves the problem elsewhere: we'd still need
>> something in pg_collation to tie the collation to the OS locale.
>
> There's not a one-to-one mapping between collation and locale name. A
> locale name includes information about the charset and a collation may
> have paramters like case-sensetivity and pad-attribute which are not
> present in the locale name. You need a mapping anyway, which is what
> this table is for.

Ideally, we would delegate the case-sensitivity and padding to the
collation implementation (ie. OS setlocale() or ICU). That said, I don't
think operating systems normally ship case-insensitive variants of
locales by default, so I agree it would be nice if we could implement
that ourselves. Still, we could identify case-sensitive locale names for
example by a suffix, like "en_GB.UTF8.case-insensitive".

I agree we will eventually need a way to give shorthand names for
collations, and a pg_collation catalog will then come handy. But that
can wait until we have the basic infrastructure ready to support column
and query-level collation.

>>> But that put us back where we started: every database having the same
>>> collation. We're trying to move away from that. Just reindex everything
>>> and be done with it.
>> That's easier said than done, unfortunately.
>
> I don't see an alternative.

Well, I proposed disallowing using a different collation than the source
database, except for using template0 as the source. That's pretty
limited, but is trivial to implement and still let's you have databases
with different collations in the same cluster.

I worked a bit on Radek's patch, stripping out all the pg_collate and
pg_charset catalog changes and commands, leaving just the core
functionality of database-level collations. It needs some cleanup and
documentation, but something like this I'd like to commit in this commit
fest. The new catalogs can wait until we have a real need for them.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com
*** src/backend/access/transam/xlog.c
--- src/backend/access/transam/xlog.c
***************
*** 3847,3853 **** WriteControlFile(void)
  {
      int            fd;
      char        buffer[PG_CONTROL_SIZE];        /* need not be aligned */
-     char       *localeptr;

      /*
       * Initialize version and compatibility-check fields
--- 3847,3852 ----
***************
*** 3875,3893 **** WriteControlFile(void)
  #endif
      ControlFile->float4ByVal = FLOAT4PASSBYVAL;
      ControlFile->float8ByVal = FLOAT8PASSBYVAL;
!
!     ControlFile->localeBuflen = LOCALE_NAME_BUFLEN;
!     localeptr = setlocale(LC_COLLATE, NULL);
!     if (!localeptr)
!         ereport(PANIC,
!                 (errmsg("invalid LC_COLLATE setting")));
!     StrNCpy(ControlFile->lc_collate, localeptr, LOCALE_NAME_BUFLEN);
!     localeptr = setlocale(LC_CTYPE, NULL);
!     if (!localeptr)
!         ereport(PANIC,
!                 (errmsg("invalid LC_CTYPE setting")));
!     StrNCpy(ControlFile->lc_ctype, localeptr, LOCALE_NAME_BUFLEN);
!
      /* Contents are protected with a CRC */
      INIT_CRC32(ControlFile->crc);
      COMP_CRC32(ControlFile->crc,
--- 3874,3880 ----
  #endif
      ControlFile->float4ByVal = FLOAT4PASSBYVAL;
      ControlFile->float8ByVal = FLOAT8PASSBYVAL;
!
      /* Contents are protected with a CRC */
      INIT_CRC32(ControlFile->crc);
      COMP_CRC32(ControlFile->crc,
***************
*** 4126,4159 **** ReadControlFile(void)
                             " but the server was compiled without USE_FLOAT8_BYVAL."),
                   errhint("It looks like you need to recompile or initdb.")));
  #endif
-
-     if (ControlFile->localeBuflen != LOCALE_NAME_BUFLEN)
-         ereport(FATAL,
-                 (errmsg("database files are incompatible with server"),
-                  errdetail("The database cluster was initialized with LOCALE_NAME_BUFLEN %d,"
-                   " but the server was compiled with LOCALE_NAME_BUFLEN %d.",
-                            ControlFile->localeBuflen, LOCALE_NAME_BUFLEN),
-                  errhint("It looks like you need to recompile or initdb.")));
-     if (pg_perm_setlocale(LC_COLLATE, ControlFile->lc_collate) == NULL)
-         ereport(FATAL,
-             (errmsg("database files are incompatible with operating system"),
-              errdetail("The database cluster was initialized with LC_COLLATE \"%s\","
-                        " which is not recognized by setlocale().",
-                        ControlFile->lc_collate),
-              errhint("It looks like you need to initdb or install locale support.")));
-     if (pg_perm_setlocale(LC_CTYPE, ControlFile->lc_ctype) == NULL)
-         ereport(FATAL,
-             (errmsg("database files are incompatible with operating system"),
-         errdetail("The database cluster was initialized with LC_CTYPE \"%s\","
-                   " which is not recognized by setlocale().",
-                   ControlFile->lc_ctype),
-              errhint("It looks like you need to initdb or install locale support.")));
-
-     /* Make the fixed locale settings visible as GUC variables, too */
-     SetConfigOption("lc_collate", ControlFile->lc_collate,
-                     PGC_INTERNAL, PGC_S_OVERRIDE);
-     SetConfigOption("lc_ctype", ControlFile->lc_ctype,
-                     PGC_INTERNAL, PGC_S_OVERRIDE);
  }

  void
--- 4113,4118 ----
*** src/backend/commands/dbcommands.c
--- src/backend/commands/dbcommands.c
***************
*** 69,75 **** static bool get_db_info(const char *name, LOCKMODE lockmode,
              Oid *dbIdP, Oid *ownerIdP,
              int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
              Oid *dbLastSysOidP, TransactionId *dbFrozenXidP,
!             Oid *dbTablespace);
  static bool have_createdb_privilege(void);
  static void remove_dbtablespaces(Oid db_id);
  static bool check_db_file_conflict(Oid db_id);
--- 69,75 ----
              Oid *dbIdP, Oid *ownerIdP,
              int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
              Oid *dbLastSysOidP, TransactionId *dbFrozenXidP,
!             Oid *dbTablespace, char **dbCollation, char **dbCtype);
  static bool have_createdb_privilege(void);
  static void remove_dbtablespaces(Oid db_id);
  static bool check_db_file_conflict(Oid db_id);
***************
*** 87,92 **** createdb(const CreatedbStmt *stmt)
--- 87,94 ----
      Oid            src_dboid;
      Oid            src_owner;
      int            src_encoding;
+     char       *src_collation;
+     char       *src_ctype;
      bool        src_istemplate;
      bool        src_allowconn;
      Oid            src_lastsysoid;
***************
*** 104,113 **** createdb(const CreatedbStmt *stmt)
--- 106,119 ----
      DefElem    *downer = NULL;
      DefElem    *dtemplate = NULL;
      DefElem    *dencoding = NULL;
+     DefElem    *dcollation = NULL;
+     DefElem    *dctype = NULL;
      DefElem    *dconnlimit = NULL;
      char       *dbname = stmt->dbname;
      char       *dbowner = NULL;
      const char *dbtemplate = NULL;
+     char       *lc_collate = NULL;
+     char       *lc_ctype = NULL;
      int            encoding = -1;
      int            dbconnlimit = -1;
      int            ctype_encoding;
***************
*** 152,157 **** createdb(const CreatedbStmt *stmt)
--- 158,179 ----
                           errmsg("conflicting or redundant options")));
              dencoding = defel;
          }
+         else if (strcmp(defel->defname, "collate") == 0)
+         {
+             if (dcollation)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dcollation = defel;
+         }
+         else if (strcmp(defel->defname, "ctype") == 0)
+         {
+             if (dctype)
+                 ereport(ERROR,
+                         (errcode(ERRCODE_SYNTAX_ERROR),
+                          errmsg("conflicting or redundant options")));
+             dctype = defel;
+         }
          else if (strcmp(defel->defname, "connectionlimit") == 0)
          {
              if (dconnlimit)
***************
*** 205,210 **** createdb(const CreatedbStmt *stmt)
--- 227,247 ----
              elog(ERROR, "unrecognized node type: %d",
                   nodeTag(dencoding->arg));
      }
+     /*
+     if (dlc_collate && dlc_collate->arg) {
+         lc_collate = strVal(dlc_collate->arg);
+         if ((locale_collate_assign(lc_collate, false, (GucSource)NULL)) == NULL)
+             ereport(ERROR,
+                 (errcode(ERRCODE_UNDEFINED_OBJECT),
+                 errmsg("%s is not a valid LC_COLLATE name",
+                         lc_collate)));
+     }
+     */
+     if (dcollation && dcollation->arg)
+         lc_collate = strVal(dcollation->arg);
+     if (dctype && dctype->arg)
+         lc_ctype = strVal(dctype->arg);
+
      if (dconnlimit && dconnlimit->arg)
          dbconnlimit = intVal(dconnlimit->arg);

***************
*** 243,249 **** createdb(const CreatedbStmt *stmt)
      if (!get_db_info(dbtemplate, ShareLock,
                       &src_dboid, &src_owner, &src_encoding,
                       &src_istemplate, &src_allowconn, &src_lastsysoid,
!                      &src_frozenxid, &src_deftablespace))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
                   errmsg("template database \"%s\" does not exist",
--- 280,286 ----
      if (!get_db_info(dbtemplate, ShareLock,
                       &src_dboid, &src_owner, &src_encoding,
                       &src_istemplate, &src_allowconn, &src_lastsysoid,
!                      &src_frozenxid, &src_deftablespace, &src_collation, &src_ctype))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
                   errmsg("template database \"%s\" does not exist",
***************
*** 305,310 **** createdb(const CreatedbStmt *stmt)
--- 342,359 ----
               errdetail("The server's LC_CTYPE setting requires encoding %s.",
                         pg_encoding_to_char(ctype_encoding))));

+     /*
+      * Message about reindexing new database
+      *
+      * We know that template0 doesn't contain any indexes that depend on
+      * collation or ctype.
+      */
+     if (strcmp(dbtemplate, "template0") != 0 &&
+         (strcmp(lc_collate, src_collation) || strcmp(lc_ctype, src_ctype)))
+         ereport(NOTICE,
+                 (errmsg("database \"%s\" needs to be reindexed manually (REINDEX DATABASE)",
+                         dbname)));
+
      /* Resolve default tablespace for new database */
      if (dtablespacename && dtablespacename->arg)
      {
***************
*** 369,374 **** createdb(const CreatedbStmt *stmt)
--- 418,432 ----
          /* Note there is no additional permission check in this path */
      }

+     /*
+      * If collation is specified for database, use it, otherwise inherit
+      * database cluster's collation.
+      */
+     if (lc_collate == NULL)
+         lc_collate = src_collation;
+     if (lc_ctype == NULL)
+         lc_ctype = src_ctype;
+
      /*
       * Check for db name conflict.    This is just to give a more friendly error
       * message than "unique index violation".  There's a race condition but
***************
*** 421,426 **** createdb(const CreatedbStmt *stmt)
--- 479,486 ----
          DirectFunctionCall1(namein, CStringGetDatum(dbname));
      new_record[Anum_pg_database_datdba - 1] = ObjectIdGetDatum(datdba);
      new_record[Anum_pg_database_encoding - 1] = Int32GetDatum(encoding);
+     new_record[Anum_pg_database_collation - 1] = DirectFunctionCall1(namein, CStringGetDatum(lc_collate));
+     new_record[Anum_pg_database_ctype - 1] = DirectFunctionCall1(namein, CStringGetDatum(lc_ctype));
      new_record[Anum_pg_database_datistemplate - 1] = BoolGetDatum(false);
      new_record[Anum_pg_database_datallowconn - 1] = BoolGetDatum(true);
      new_record[Anum_pg_database_datconnlimit - 1] = Int32GetDatum(dbconnlimit);
***************
*** 629,635 **** dropdb(const char *dbname, bool missing_ok)
      pgdbrel = heap_open(DatabaseRelationId, RowExclusiveLock);

      if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
!                      &db_istemplate, NULL, NULL, NULL, NULL))
      {
          if (!missing_ok)
          {
--- 689,695 ----
      pgdbrel = heap_open(DatabaseRelationId, RowExclusiveLock);

      if (!get_db_info(dbname, AccessExclusiveLock, &db_id, NULL, NULL,
!                      &db_istemplate, NULL, NULL, NULL, NULL, NULL, NULL))
      {
          if (!missing_ok)
          {
***************
*** 781,787 **** RenameDatabase(const char *oldname, const char *newname)
      rel = heap_open(DatabaseRelationId, RowExclusiveLock);

      if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL,
!                      NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
                   errmsg("database \"%s\" does not exist", oldname)));
--- 841,847 ----
      rel = heap_open(DatabaseRelationId, RowExclusiveLock);

      if (!get_db_info(oldname, AccessExclusiveLock, &db_id, NULL, NULL,
!                      NULL, NULL, NULL, NULL, NULL, NULL, NULL))
          ereport(ERROR,
                  (errcode(ERRCODE_UNDEFINED_DATABASE),
                   errmsg("database \"%s\" does not exist", oldname)));
***************
*** 1168,1174 **** get_db_info(const char *name, LOCKMODE lockmode,
              Oid *dbIdP, Oid *ownerIdP,
              int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
              Oid *dbLastSysOidP, TransactionId *dbFrozenXidP,
!             Oid *dbTablespace)
  {
      bool        result = false;
      Relation    relation;
--- 1228,1234 ----
              Oid *dbIdP, Oid *ownerIdP,
              int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
              Oid *dbLastSysOidP, TransactionId *dbFrozenXidP,
!             Oid *dbTablespace, char **dbCollation, char **dbCtype)
  {
      bool        result = false;
      Relation    relation;
***************
*** 1259,1264 **** get_db_info(const char *name, LOCKMODE lockmode,
--- 1319,1329 ----
                  /* default tablespace for this database */
                  if (dbTablespace)
                      *dbTablespace = dbform->dattablespace;
+                  /* default locale settings for this database */
+                  if (dbCollation)
+                      *dbCollation = pstrdup(NameStr(dbform->collation));
+                  if (dbCtype)
+                      *dbCtype = pstrdup(NameStr(dbform->ctype));
                  ReleaseSysCache(tuple);
                  result = true;
                  break;
*** src/backend/parser/gram.y
--- src/backend/parser/gram.y
***************
*** 398,404 **** static TypeName *TableFuncTypeName(List *columns);
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
      COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
      CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
!     CREATEROLE CREATEUSER CROSS CSV CURRENT_P CURRENT_DATE CURRENT_ROLE
      CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
--- 398,404 ----
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
      COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
      CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
!     CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
      CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
***************
*** 5458,5463 **** createdb_opt_item:
--- 5458,5479 ----
                  {
                      $$ = makeDefElem("encoding", NULL);
                  }
+             | COLLATE opt_equal Sconst
+                 {
+                     $$ = makeDefElem("collate", (Node *)makeString($3));
+                 }
+             | COLLATE opt_equal DEFAULT
+                 {
+                     $$ = makeDefElem("collate", NULL);
+                 }
+             | CTYPE opt_equal Sconst
+                 {
+                     $$ = makeDefElem("ctype", (Node *)makeString($3));
+                 }
+             | CTYPE opt_equal DEFAULT
+                 {
+                     $$ = makeDefElem("ctype", NULL);
+                 }
              | CONNECTION LIMIT opt_equal SignedIconst
                  {
                      $$ = makeDefElem("connectionlimit", (Node *)makeInteger($4));
***************
*** 9216,9221 **** unreserved_keyword:
--- 9232,9238 ----
              | CREATEROLE
              | CREATEUSER
              | CSV
+             | CTYPE
              | CURRENT_P
              | CURSOR
              | CYCLE
*** src/backend/parser/keywords.c
--- src/backend/parser/keywords.c
***************
*** 114,119 **** const ScanKeyword ScanKeywords[] = {
--- 114,120 ----
      {"createuser", CREATEUSER, UNRESERVED_KEYWORD},
      {"cross", CROSS, TYPE_FUNC_NAME_KEYWORD},
      {"csv", CSV, UNRESERVED_KEYWORD},
+     {"ctype", CTYPE, UNRESERVED_KEYWORD},
      {"current", CURRENT_P, UNRESERVED_KEYWORD},
      {"current_date", CURRENT_DATE, RESERVED_KEYWORD},
      {"current_role", CURRENT_ROLE, RESERVED_KEYWORD},
*** src/backend/utils/adt/pg_locale.c
--- src/backend/utils/adt/pg_locale.c
***************
*** 228,233 **** locale_xxx_assign(int category, const char *value, bool doit, GucSource source)
--- 228,245 ----
      return value;
  }

+ const char *
+ locale_collate_assign(const char *value, bool doit, GucSource source)
+ {
+     return locale_xxx_assign(LC_COLLATE, value, doit, source);
+ }
+
+ const char *
+ locale_ctype_assign(const char *value, bool doit, GucSource source)
+ {
+     return locale_xxx_assign(LC_CTYPE, value, doit, source);
+ }
+

  const char *
  locale_monetary_assign(const char *value, bool doit, GucSource source)
*** src/backend/utils/init/postinit.c
--- src/backend/utils/init/postinit.c
***************
*** 159,164 **** CheckMyDatabase(const char *name, bool am_superuser)
--- 159,166 ----
  {
      HeapTuple    tup;
      Form_pg_database dbform;
+     char       *collate;
+     char       *ctype;

      /* Fetch our real pg_database row */
      tup = SearchSysCache(DATABASEOID,
***************
*** 240,245 **** CheckMyDatabase(const char *name, bool am_superuser)
--- 242,270 ----
      /* If we have no other source of client_encoding, use server encoding */
      SetConfigOption("client_encoding", GetDatabaseEncodingName(),
                      PGC_BACKEND, PGC_S_DEFAULT);
+
+     /* assign locale variables */
+     collate = NameStr(dbform->collation);
+     ctype = NameStr(dbform->ctype);
+
+     if (setlocale(LC_COLLATE, collate) == NULL)
+         ereport(FATAL,
+             (errmsg("database local is incompatible with OS"),
+             errdetail("The database was initialized with LC_COLLATE \"%s\", "
+                         " which is not recognized by setlocale().", collate),
+             errhint("Try to recreate the database or install locale support.")));
+
+     if (setlocale(LC_CTYPE, ctype) == NULL)
+         ereport(FATAL,
+             (errmsg("database local is incompatible with OS"),
+             errdetail("The database was initialized with LC_CTYPE \"%s\", "
+                         " which is not recognized by setlocale().", ctype),
+             errhint("Try to recreate the database or install locale support.")));
+
+     /* Record it as a GUC internal option, too */
+
+     SetConfigOption("lc_collate", collate, PGC_INTERNAL, PGC_S_DATABASE);
+     SetConfigOption("lc_ctype", ctype, PGC_INTERNAL, PGC_S_DATABASE);

      /*
       * Lastly, set up any database-specific configuration variables.
*** src/bin/initdb/initdb.c
--- src/bin/initdb/initdb.c
***************
*** 1171,1176 **** setup_config(void)
--- 1171,1184 ----
      conflines = replace_token(conflines, "#port = 5432", repltok);
  #endif

+     snprintf(repltok, sizeof(repltok), "lc_collate = '%s'",
+              escape_quotes(lc_collate));
+     conflines = replace_token(conflines, "#lc_collate = 'C'", repltok);
+
+     snprintf(repltok, sizeof(repltok), "lc_ctype = '%s'",
+              escape_quotes(lc_collate));
+     conflines = replace_token(conflines, "#lc_ctype = 'C'", repltok);
+
      snprintf(repltok, sizeof(repltok), "lc_messages = '%s'",
               escape_quotes(lc_messages));
      conflines = replace_token(conflines, "#lc_messages = 'C'", repltok);
***************
*** 1353,1358 **** bootstrap_template1(char *short_version)
--- 1361,1370 ----

      bki_lines = replace_token(bki_lines, "ENCODING", encodingid);

+     bki_lines = replace_token(bki_lines, "LC_COLLATE", lc_collate);
+
+     bki_lines = replace_token(bki_lines, "LC_CTYPE", lc_ctype);
+
      /*
       * Pass correct LC_xxx environment to bootstrap.
       *
***************
*** 2806,2815 **** main(int argc, char *argv[])
          strcmp(lc_ctype, lc_numeric) == 0 &&
          strcmp(lc_ctype, lc_monetary) == 0 &&
          strcmp(lc_ctype, lc_messages) == 0)
!         printf(_("The database cluster will be initialized with locale %s.\n"), lc_ctype);
      else
      {
!         printf(_("The database cluster will be initialized with locales\n"
                   "  COLLATE:  %s\n"
                   "  CTYPE:    %s\n"
                   "  MESSAGES: %s\n"
--- 2818,2827 ----
          strcmp(lc_ctype, lc_numeric) == 0 &&
          strcmp(lc_ctype, lc_monetary) == 0 &&
          strcmp(lc_ctype, lc_messages) == 0)
!         printf(_("The database template1 will be initialized with locale %s.\n"), lc_ctype);
      else
      {
!         printf(_("The database template1 will be initialized with locales\n"
                   "  COLLATE:  %s\n"
                   "  CTYPE:    %s\n"
                   "  MESSAGES: %s\n"
*** src/bin/pg_controldata/pg_controldata.c
--- src/bin/pg_controldata/pg_controldata.c
***************
*** 220,231 **** main(int argc, char *argv[])
             (ControlFile.float4ByVal ? _("by value") : _("by reference")));
      printf(_("Float8 argument passing:              %s\n"),
             (ControlFile.float8ByVal ? _("by value") : _("by reference")));
-     printf(_("Maximum length of locale name:        %u\n"),
-            ControlFile.localeBuflen);
-     printf(_("LC_COLLATE:                           %s\n"),
-            ControlFile.lc_collate);
-     printf(_("LC_CTYPE:                             %s\n"),
-            ControlFile.lc_ctype);
-
      return 0;
  }
--- 220,224 ----
*** src/bin/pg_resetxlog/pg_resetxlog.c
--- src/bin/pg_resetxlog/pg_resetxlog.c
***************
*** 493,514 **** GuessControlValues(void)
  #endif
      ControlFile.float4ByVal = FLOAT4PASSBYVAL;
      ControlFile.float8ByVal = FLOAT8PASSBYVAL;
-     ControlFile.localeBuflen = LOCALE_NAME_BUFLEN;
-
-     localeptr = setlocale(LC_COLLATE, "");
-     if (!localeptr)
-     {
-         fprintf(stderr, _("%s: invalid LC_COLLATE setting\n"), progname);
-         exit(1);
-     }
-     strlcpy(ControlFile.lc_collate, localeptr, sizeof(ControlFile.lc_collate));
-     localeptr = setlocale(LC_CTYPE, "");
-     if (!localeptr)
-     {
-         fprintf(stderr, _("%s: invalid LC_CTYPE setting\n"), progname);
-         exit(1);
-     }
-     strlcpy(ControlFile.lc_ctype, localeptr, sizeof(ControlFile.lc_ctype));

      /*
       * XXX eventually, should try to grovel through old XLOG to develop more
--- 493,498 ----
***************
*** 584,595 **** PrintControlValues(bool guessed)
             (ControlFile.float4ByVal ? _("by value") : _("by reference")));
      printf(_("Float8 argument passing:              %s\n"),
             (ControlFile.float8ByVal ? _("by value") : _("by reference")));
-     printf(_("Maximum length of locale name:        %u\n"),
-            ControlFile.localeBuflen);
-     printf(_("LC_COLLATE:                           %s\n"),
-            ControlFile.lc_collate);
-     printf(_("LC_CTYPE:                             %s\n"),
-            ControlFile.lc_ctype);
  }


--- 568,573 ----
*** src/bin/scripts/createdb.c
--- src/bin/scripts/createdb.c
***************
*** 32,37 **** main(int argc, char *argv[])
--- 32,39 ----
          {"tablespace", required_argument, NULL, 'D'},
          {"template", required_argument, NULL, 'T'},
          {"encoding", required_argument, NULL, 'E'},
+         {"lc-collate", required_argument, NULL, 1},
+         {"lc-ctype", required_argument, NULL, 2},
          {NULL, 0, NULL, 0}
      };

***************
*** 50,55 **** main(int argc, char *argv[])
--- 52,59 ----
      char       *tablespace = NULL;
      char       *template = NULL;
      char       *encoding = NULL;
+     char       *lc_collate = NULL;
+     char       *lc_ctype = NULL;

      PQExpBufferData sql;

***************
*** 95,100 **** main(int argc, char *argv[])
--- 99,110 ----
              case 'E':
                  encoding = optarg;
                  break;
+             case 1:
+                 lc_collate = optarg;
+                 break;
+             case 2:
+                 lc_ctype = optarg;
+                 break;
              default:
                  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
                  exit(1);
***************
*** 152,159 **** main(int argc, char *argv[])
          appendPQExpBuffer(&sql, " ENCODING '%s'", encoding);
      if (template)
          appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
      appendPQExpBuffer(&sql, ";\n");
!
      conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
                             host, port, username, password, progname);

--- 162,174 ----
          appendPQExpBuffer(&sql, " ENCODING '%s'", encoding);
      if (template)
          appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
+     if (lc_collate)
+         appendPQExpBuffer(&sql, " LCCOLLATE %s", fmtId(lc_collate));
+     if (lc_ctype)
+         appendPQExpBuffer(&sql, " LCCTYPE %s", fmtId(lc_ctype));
+
      appendPQExpBuffer(&sql, ";\n");
!
      conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
                             host, port, username, password, progname);

***************
*** 207,224 **** help(const char *progname)
      printf(_("Usage:\n"));
      printf(_("  %s [OPTION]... [DBNAME] [DESCRIPTION]\n"), progname);
      printf(_("\nOptions:\n"));
!     printf(_("  -D, --tablespace=TABLESPACE  default tablespace for the database\n"));
!     printf(_("  -E, --encoding=ENCODING      encoding for the database\n"));
!     printf(_("  -O, --owner=OWNER            database user to own the new database\n"));
!     printf(_("  -T, --template=TEMPLATE      template database to copy\n"));
!     printf(_("  -e, --echo                   show the commands being sent to the server\n"));
!     printf(_("  --help                       show this help, then exit\n"));
!     printf(_("  --version                    output version information, then exit\n"));
      printf(_("\nConnection options:\n"));
!     printf(_("  -h, --host=HOSTNAME          database server host or socket directory\n"));
!     printf(_("  -p, --port=PORT              database server port\n"));
!     printf(_("  -U, --username=USERNAME      user name to connect as\n"));
!     printf(_("  -W, --password               force password prompt\n"));
      printf(_("\nBy default, a database with the same name as the current user is created.\n"));
      printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
  }
--- 222,240 ----
      printf(_("Usage:\n"));
      printf(_("  %s [OPTION]... [DBNAME] [DESCRIPTION]\n"), progname);
      printf(_("\nOptions:\n"));
!     printf(_("  -D, --tablespace=TABLESPACE      default tablespace for the database\n"));
!     printf(_("  -E, --encoding=ENCODING          encoding for the database\n"));
!     printf(_("    --lc-collate, --lc-ctype=LOCALE    locale for the database\n"));
!     printf(_("  -O, --owner=OWNER                database user to own the new database\n"));
!     printf(_("  -T, --template=TEMPLATE          template database to copy\n"));
!     printf(_("  -e, --echo                       show the commands being sent to the server\n"));
!     printf(_("  --help                           show this help, then exit\n"));
!     printf(_("  --version                        output version information, then exit\n"));
      printf(_("\nConnection options:\n"));
!     printf(_("  -h, --host=HOSTNAME              database server host or socket directory\n"));
!     printf(_("  -p, --port=PORT                  database server port\n"));
!     printf(_("  -U, --username=USERNAME          user name to connect as\n"));
!     printf(_("  -W, --password                   force password prompt\n"));
      printf(_("\nBy default, a database with the same name as the current user is created.\n"));
      printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
  }
*** src/include/catalog/pg_control.h
--- src/include/catalog/pg_control.h
***************
*** 144,154 **** typedef struct ControlFileData
      bool        float4ByVal;    /* float4 pass-by-value? */
      bool        float8ByVal;    /* float8, int8, etc pass-by-value? */

-     /* active locales */
-     uint32        localeBuflen;
-     char        lc_collate[LOCALE_NAME_BUFLEN];
-     char        lc_ctype[LOCALE_NAME_BUFLEN];
-
      /* CRC of all above ... MUST BE LAST! */
      pg_crc32    crc;
  } ControlFileData;
--- 144,149 ----
*** src/include/catalog/pg_database.h
--- src/include/catalog/pg_database.h
***************
*** 33,38 **** CATALOG(pg_database,1262) BKI_SHARED_RELATION
--- 33,40 ----
      NameData    datname;        /* database name */
      Oid            datdba;            /* owner of database */
      int4        encoding;        /* character encoding */
+     NameData    collation;        /* LC_COLLATE of database */
+     NameData    ctype;            /* LC_CTYPE of database */
      bool        datistemplate;    /* allowed as CREATE DATABASE template? */
      bool        datallowconn;    /* new connections allowed? */
      int4        datconnlimit;    /* max connections allowed (-1=no limit) */
***************
*** 54,73 **** typedef FormData_pg_database *Form_pg_database;
   *        compiler constants for pg_database
   * ----------------
   */
! #define Natts_pg_database                11
  #define Anum_pg_database_datname        1
  #define Anum_pg_database_datdba            2
  #define Anum_pg_database_encoding        3
! #define Anum_pg_database_datistemplate    4
! #define Anum_pg_database_datallowconn    5
! #define Anum_pg_database_datconnlimit    6
! #define Anum_pg_database_datlastsysoid    7
! #define Anum_pg_database_datfrozenxid    8
! #define Anum_pg_database_dattablespace    9
! #define Anum_pg_database_datconfig        10
! #define Anum_pg_database_datacl            11

! DATA(insert OID = 1 (  template1 PGUID ENCODING t t -1 0 0 1663 _null_ _null_ ));
  SHDESCR("default template database");
  #define TemplateDbOid            1

--- 56,77 ----
   *        compiler constants for pg_database
   * ----------------
   */
! #define Natts_pg_database                13
  #define Anum_pg_database_datname        1
  #define Anum_pg_database_datdba            2
  #define Anum_pg_database_encoding        3
! #define Anum_pg_database_collation        4
! #define Anum_pg_database_ctype            5
! #define Anum_pg_database_datistemplate    6
! #define Anum_pg_database_datallowconn    7
! #define Anum_pg_database_datconnlimit    8
! #define Anum_pg_database_datlastsysoid    9
! #define Anum_pg_database_datfrozenxid    10
! #define Anum_pg_database_dattablespace    11
! #define Anum_pg_database_datconfig        12
! #define Anum_pg_database_datacl            13

! DATA(insert OID = 1 (  template1 PGUID ENCODING "LC_COLLATE" "LC_CTYPE" t t -1 0 0 1663 _null_ _null_));
  SHDESCR("default template database");
  #define TemplateDbOid            1

*** src/interfaces/ecpg/preproc/preproc.y
--- src/interfaces/ecpg/preproc/preproc.y
***************
*** 428,434 **** add_typedef(char *name, char * dimension, char * length, enum ECPGttype type_enu
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
      COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
      CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
!     CREATEROLE CREATEUSER CROSS CSV CURRENT_P CURRENT_DATE CURRENT_ROLE
      CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS
--- 428,434 ----
      CLUSTER COALESCE COLLATE COLUMN COMMENT COMMIT
      COMMITTED CONCURRENTLY CONFIGURATION CONNECTION CONSTRAINT CONSTRAINTS
      CONTENT_P CONTINUE_P CONVERSION_P COPY COST CREATE CREATEDB
!     CREATEROLE CREATEUSER CROSS CSV CTYPE CURRENT_P CURRENT_DATE CURRENT_ROLE
      CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CYCLE

      DATABASE DAY_P DEALLOCATE DEC DECIMAL_P DECLARE DEFAULT DEFAULTS