Make LC_COLLATE and LC_CTYPE database-level settings. Collation and

ctype are now more like encoding, stored in new datcollate and datctype
columns in pg_database.

This is a stripped-down version of Radek Strnad's patch, with further
changes by me.

7 files changed, 115 insertions, 49 deletions

diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index e305c69fa3..a4bd34cb68 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -42,7 +42,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  * Portions taken from FreeBSD.
  *
- * $PostgreSQL: pgsql/src/bin/initdb/initdb.c,v 1.159 2008/08/05 12:09:30 mha Exp $
+ * $PostgreSQL: pgsql/src/bin/initdb/initdb.c,v 1.160 2008/09/23 09:20:37 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1353,6 +1353,10 @@ bootstrap_template1(char *short_version)
 
 	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.
 	 *
@@ -2179,6 +2183,8 @@ locale_date_order(const char *locale)
 
 /*
  * check if given string is a valid locale specifier
+ *
+ * this should match the backend check_locale() function
  */
 static bool
 chklocale(const char *locale)
@@ -2378,12 +2384,12 @@ usage(const char *progname)
 	printf(_("\nOptions:\n"));
 	printf(_(" [-D, --pgdata=]DATADIR     location for this database cluster\n"));
 	printf(_("  -E, --encoding=ENCODING   set default encoding for new databases\n"));
-	printf(_("  --locale=LOCALE           initialize database cluster with given locale\n"));
+	printf(_("  --locale=LOCALE           set default locale for new databases\n"));
 	printf(_("  --lc-collate, --lc-ctype, --lc-messages=LOCALE\n"
 			 "  --lc-monetary, --lc-numeric, --lc-time=LOCALE\n"
-			 "                            initialize database cluster with given locale\n"
-			 "                            in the respective category (default taken from\n"
-			 "                            environment)\n"));
+			 "                            set default locale in the respective\n"
+			 "                            category for new databases (default\n"
+			 "                            taken from environment)\n"));
 	printf(_("  --no-locale               equivalent to --locale=C\n"));
 	printf(_("  -T, --text-search-config=CFG\n"
 		 "                            default text search configuration\n"));
diff --git a/src/bin/pg_controldata/pg_controldata.c b/src/bin/pg_controldata/pg_controldata.c
index 122ef37996..50a4754cd5 100644
--- a/src/bin/pg_controldata/pg_controldata.c
+++ b/src/bin/pg_controldata/pg_controldata.c
@@ -6,7 +6,7 @@
  * copyright (c) Oliver Elphick <olly@lfix.co.uk>, 2001;
  * licence: BSD
  *
- * $PostgreSQL: pgsql/src/bin/pg_controldata/pg_controldata.c,v 1.39 2008/04/21 00:26:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_controldata/pg_controldata.c,v 1.40 2008/09/23 09:20:37 heikki Exp $
  */
 #include "postgres_fe.h"
 
@@ -220,12 +220,5 @@ 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;
 }
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 25b0f58e7d..55b944ae94 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -12,7 +12,7 @@
  *	by PostgreSQL
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.500 2008/09/08 15:26:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.501 2008/09/23 09:20:37 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1542,12 +1542,16 @@ dumpDatabase(Archive *AH)
 				i_oid,
 				i_dba,
 				i_encoding,
+				i_collate,
+				i_ctype,
 				i_tablespace;
 	CatalogId	dbCatId;
 	DumpId		dbDumpId;
 	const char *datname,
 			   *dba,
 			   *encoding,
+			   *collate,
+			   *ctype,
 			   *tablespace;
 
 	datname = PQdb(g_conn);
@@ -1559,11 +1563,26 @@ dumpDatabase(Archive *AH)
 	selectSourceSchema("pg_catalog");
 
 	/* Get the database owner and parameters from pg_database */
-	if (g_fout->remoteVersion >= 80200)
+	if (g_fout->remoteVersion >= 80400)
+	{
+		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
+						  "(%s datdba) as dba, "
+						  "pg_encoding_to_char(encoding) as encoding, "
+						  "datcollate, datctype, "
+						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace, "
+					  "shobj_description(oid, 'pg_database') as description "
+
+						  "FROM pg_database "
+						  "WHERE datname = ",
+						  username_subquery);
+		appendStringLiteralAH(dbQry, datname, AH);
+	}
+	else if (g_fout->remoteVersion >= 80200)
 	{
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) as dba, "
 						  "pg_encoding_to_char(encoding) as encoding, "
+						  "NULL as datcollate, NULL as datctype, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace, "
 					  "shobj_description(oid, 'pg_database') as description "
 
@@ -1577,6 +1596,7 @@ dumpDatabase(Archive *AH)
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) as dba, "
 						  "pg_encoding_to_char(encoding) as encoding, "
+						  "NULL as datcollate, NULL as datctype, "
 						  "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) as tablespace "
 						  "FROM pg_database "
 						  "WHERE datname = ",
@@ -1588,6 +1608,7 @@ dumpDatabase(Archive *AH)
 		appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
 						  "(%s datdba) as dba, "
 						  "pg_encoding_to_char(encoding) as encoding, "
+						  "NULL as datcollate, NULL as datctype, "
 						  "NULL as tablespace "
 						  "FROM pg_database "
 						  "WHERE datname = ",
@@ -1601,6 +1622,7 @@ dumpDatabase(Archive *AH)
 						  "oid, "
 						  "(%s datdba) as dba, "
 						  "pg_encoding_to_char(encoding) as encoding, "
+						  "NULL as datcollate, NULL as datctype, "
 						  "NULL as tablespace "
 						  "FROM pg_database "
 						  "WHERE datname = ",
@@ -1631,12 +1653,16 @@ dumpDatabase(Archive *AH)
 	i_oid = PQfnumber(res, "oid");
 	i_dba = PQfnumber(res, "dba");
 	i_encoding = PQfnumber(res, "encoding");
+	i_collate = PQfnumber(res, "collate");
+	i_ctype = PQfnumber(res, "ctype");
 	i_tablespace = PQfnumber(res, "tablespace");
 
 	dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
 	dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid));
 	dba = PQgetvalue(res, 0, i_dba);
 	encoding = PQgetvalue(res, 0, i_encoding);
+	collate = PQgetvalue(res, 0, i_collate);
+	ctype = PQgetvalue(res, 0, i_ctype);
 	tablespace = PQgetvalue(res, 0, i_tablespace);
 
 	appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
@@ -1646,6 +1672,16 @@ dumpDatabase(Archive *AH)
 		appendPQExpBuffer(creaQry, " ENCODING = ");
 		appendStringLiteralAH(creaQry, encoding, AH);
 	}
+	if (strlen(collate) > 0)
+	{
+		appendPQExpBuffer(creaQry, " COLLATE = ");
+		appendStringLiteralAH(creaQry, collate, AH);
+	}
+	if (strlen(ctype) > 0)
+	{
+		appendPQExpBuffer(creaQry, " CTYPE = ");
+		appendStringLiteralAH(creaQry, ctype, AH);
+	}
 	if (strlen(tablespace) > 0 && strcmp(tablespace, "pg_default") != 0)
 		appendPQExpBuffer(creaQry, " TABLESPACE = %s",
 						  fmtId(tablespace));
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 3bac1a38ce..ceb8cd48a0 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  *
- * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.106 2008/08/29 17:28:43 alvherre Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_dump/pg_dumpall.c,v 1.107 2008/09/23 09:20:38 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -925,11 +925,22 @@ dumpCreateDB(PGconn *conn)
 
 	fprintf(OPF, "--\n-- Database creation\n--\n\n");
 
-	if (server_version >= 80100)
+	if (server_version >= 80400)
 		res = executeQuery(conn,
 						   "SELECT datname, "
 						   "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
 						   "pg_encoding_to_char(d.encoding), "
+						   "datcollate, datctype, "
+						   "datistemplate, datacl, datconnlimit, "
+						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
+			  "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
+						   "WHERE datallowconn ORDER BY 1");
+	else if (server_version >= 80100)
+		res = executeQuery(conn,
+						   "SELECT datname, "
+						   "coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
+						   "pg_encoding_to_char(d.encoding), "
+						   "null::text AS datcollate, null::text AS datctype, "
 						   "datistemplate, datacl, datconnlimit, "
 						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
 			  "FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
@@ -939,6 +950,7 @@ dumpCreateDB(PGconn *conn)
 						   "SELECT datname, "
 						   "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 						   "pg_encoding_to_char(d.encoding), "
+						   "null::text AS datcollate, null::text AS datctype, "
 						   "datistemplate, datacl, -1 as datconnlimit, "
 						   "(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
 		   "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
@@ -948,6 +960,7 @@ dumpCreateDB(PGconn *conn)
 						   "SELECT datname, "
 						   "coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 						   "pg_encoding_to_char(d.encoding), "
+						   "null::text AS datcollate, null::text AS datctype, "
 						   "datistemplate, datacl, -1 as datconnlimit, "
 						   "'pg_default' AS dattablespace "
 		   "FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
@@ -959,6 +972,7 @@ dumpCreateDB(PGconn *conn)
 					"(select usename from pg_shadow where usesysid=datdba), "
 						   "(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
 						   "pg_encoding_to_char(d.encoding), "
+						   "null::text AS datcollate, null::text AS datctype, "
 						   "datistemplate, '' as datacl, -1 as datconnlimit, "
 						   "'pg_default' AS dattablespace "
 						   "FROM pg_database d "
@@ -973,6 +987,7 @@ dumpCreateDB(PGconn *conn)
 						   "SELECT datname, "
 					"(select usename from pg_shadow where usesysid=datdba), "
 						   "pg_encoding_to_char(d.encoding), "
+						   "null::text AS datcollate, null::text AS datctype, "
 						   "'f' as datistemplate, "
 						   "'' as datacl, -1 as datconnlimit, "
 						   "'pg_default' AS dattablespace "
@@ -985,10 +1000,12 @@ dumpCreateDB(PGconn *conn)
 		char	   *dbname = PQgetvalue(res, i, 0);
 		char	   *dbowner = PQgetvalue(res, i, 1);
 		char	   *dbencoding = PQgetvalue(res, i, 2);
-		char	   *dbistemplate = PQgetvalue(res, i, 3);
-		char	   *dbacl = PQgetvalue(res, i, 4);
-		char	   *dbconnlimit = PQgetvalue(res, i, 5);
-		char	   *dbtablespace = PQgetvalue(res, i, 6);
+		char	   *dbcollate = PQgetvalue(res, i, 3);
+		char	   *dbctype = PQgetvalue(res, i, 4);
+		char	   *dbistemplate = PQgetvalue(res, i, 5);
+		char	   *dbacl = PQgetvalue(res, i, 6);
+		char	   *dbconnlimit = PQgetvalue(res, i, 7);
+		char	   *dbtablespace = PQgetvalue(res, i, 8);
 		char	   *fdbname;
 
 		fdbname = strdup(fmtId(dbname));
@@ -1016,6 +1033,18 @@ dumpCreateDB(PGconn *conn)
 			appendPQExpBuffer(buf, " ENCODING = ");
 			appendStringLiteralConn(buf, dbencoding, conn);
 
+			if (strlen(dbcollate) != 0)
+			{
+				appendPQExpBuffer(buf, " COLLATE = ");
+				appendStringLiteralConn(buf, dbcollate, conn);
+			}
+
+			if (strlen(dbctype) != 0)
+			{
+				appendPQExpBuffer(buf, " CTYPE = ");
+				appendStringLiteralConn(buf, dbctype, conn);
+			}
+
 			/*
 			 * Output tablespace if it isn't the default.  For default, it
 			 * uses the default from the template database.  If tablespace is
diff --git a/src/bin/pg_resetxlog/pg_resetxlog.c b/src/bin/pg_resetxlog/pg_resetxlog.c
index 109fec7fac..c3cfafea7a 100644
--- a/src/bin/pg_resetxlog/pg_resetxlog.c
+++ b/src/bin/pg_resetxlog/pg_resetxlog.c
@@ -23,7 +23,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/pg_resetxlog/pg_resetxlog.c,v 1.65 2008/04/21 00:26:46 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/pg_resetxlog/pg_resetxlog.c,v 1.66 2008/09/23 09:20:38 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -493,22 +493,6 @@ 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
@@ -584,12 +568,6 @@ 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);
 }
 
 
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 137ea6c2d4..c801fe7c52 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -8,7 +8,7 @@
  *
  * Copyright (c) 2000-2008, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.184 2008/07/18 04:20:24 tgl Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.185 2008/09/23 09:20:38 heikki Exp $
  */
 #include "postgres_fe.h"
 
@@ -454,11 +454,18 @@ listAllDbs(bool verbose)
 	printfPQExpBuffer(&buf,
 					  "SELECT d.datname as \"%s\",\n"
 					  "       pg_catalog.pg_get_userbyid(d.datdba) as \"%s\",\n"
-					  "       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n"
-					  "       d.datacl as \"%s\"",
+					  "       pg_catalog.pg_encoding_to_char(d.encoding) as \"%s\",\n",
 					  gettext_noop("Name"),
 					  gettext_noop("Owner"),
-					  gettext_noop("Encoding"),
+					  gettext_noop("Encoding"));
+	if (pset.sversion >= 80400)
+		appendPQExpBuffer(&buf,
+						  "       d.datcollate as \"%s\",\n"
+						  "       d.datctype as \"%s\",\n",
+						  gettext_noop("Collation"),
+						  gettext_noop("Ctype"));
+	appendPQExpBuffer(&buf,
+					  "       d.datacl as \"%s\"",
 					  gettext_noop("Access Privileges"));
 	if (verbose && pset.sversion >= 80200)
 		appendPQExpBuffer(&buf,
diff --git a/src/bin/scripts/createdb.c b/src/bin/scripts/createdb.c
index d42eea93d1..da216819b6 100644
--- a/src/bin/scripts/createdb.c
+++ b/src/bin/scripts/createdb.c
@@ -5,7 +5,7 @@
  * Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/bin/scripts/createdb.c,v 1.26 2008/01/01 19:45:56 momjian Exp $
+ * $PostgreSQL: pgsql/src/bin/scripts/createdb.c,v 1.27 2008/09/23 09:20:38 heikki Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -32,6 +32,8 @@ main(int argc, char *argv[])
 		{"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,6 +52,8 @@ main(int argc, char *argv[])
 	char	   *tablespace = NULL;
 	char	   *template = NULL;
 	char	   *encoding = NULL;
+	char	   *lc_collate = NULL;
+	char	   *lc_ctype = NULL;
 
 	PQExpBufferData sql;
 
@@ -95,6 +99,12 @@ main(int argc, char *argv[])
 			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,6 +162,11 @@ main(int argc, char *argv[])
 		appendPQExpBuffer(&sql, " ENCODING '%s'", encoding);
 	if (template)
 		appendPQExpBuffer(&sql, " TEMPLATE %s", fmtId(template));
+	if (lc_collate)
+		appendPQExpBuffer(&sql, " COLLATE '%s'", lc_collate);
+	if (lc_ctype)
+		appendPQExpBuffer(&sql, " CTYPE '%s'", lc_ctype);
+
 	appendPQExpBuffer(&sql, ";\n");
 
 	conn = connectDatabase(strcmp(dbname, "postgres") == 0 ? "template1" : "postgres",
@@ -209,6 +224,8 @@ help(const char *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=LOCALE          LC_COLLATE setting for the database\n"));
+	printf(_("  --lc-ctype=LOCALE            LC_CTYPE setting 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"));