[PATCHES] TODO item: locale per database patch (new iteration)
Hi,
it's a renewed locale per database patch. Unfortunately, i've not found
clean way to rebuild database indexes automatically, if locale settings
of two databases (created and template) are differs. Now it's only
raises a NOTICE. So, if anyone has a right notion about it - let will
express. Comment and suggestions are highly appreciated
Index: src/backend/access/transam/xlog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.226
diff -u -r1.226 xlog.c
--- src/backend/access/transam/xlog.c 11 Jan 2006 08:43:12 - 1.226
+++ src/backend/access/transam/xlog.c 22 Jan 2006 16:41:02 -
@@ -3394,7 +3394,6 @@
{
int fd;
charbuffer[BLCKSZ]; /* need not be aligned */
- char *localeptr;
/*
* Initialize version and compatibility-check fields
@@ -3418,18 +3417,6 @@
ControlFile-enableIntTimes = FALSE;
#endif
- 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,
@@ -3612,34 +3599,6 @@
but the server was compiled without
HAVE_INT64_TIMESTAMP.),
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
Index: src/backend/commands/dbcommands.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v
retrieving revision 1.175
diff -u -r1.175 dbcommands.c
--- src/backend/commands/dbcommands.c 22 Nov 2005 18:17:08 - 1.175
+++ src/backend/commands/dbcommands.c 22 Jan 2006 16:41:03 -
@@ -25,6 +25,10 @@
#include unistd.h
#include sys/stat.h
+#ifdef HAVE_LANGINFO_H
+#include langinfo.h
+#endif
+
#include access/genam.h
#include access/heapam.h
#include catalog/catalog.h
@@ -49,6 +53,7 @@
#include utils/fmgroids.h
#include utils/guc.h
#include utils/lsyscache.h
+#include utils/pg_locale.h
#include utils/syscache.h
@@ -57,9 +62,11 @@
int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP,
Oid *dbLastSysOidP,
TransactionId *dbVacuumXidP, TransactionId
*dbFrozenXidP,
- Oid *dbTablespace);
+ Oid *dbTablespace, char **dbCollate, char **dbCtype);
static bool have_createdb_privilege(void);
[PATCHES] Patch for database locale settings
Hi, this patch allow to use database locale settings. It remove cluster locale settings, and append LCCTYPE and LCCOLLATE items to CREATE DATABASE syntax. Any considerations ? best regards, Alexey SlynkoIndex: src/backend/access/transam/xlog.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v retrieving revision 1.182 diff -c -r1.182 xlog.c *** src/backend/access/transam/xlog.c 24 Mar 2005 04:36:17 - 1.182 --- src/backend/access/transam/xlog.c 25 Mar 2005 09:42:17 - *** *** 3128,3145 ControlFile-enableIntTimes = FALSE; #endif - 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_CRC64(ControlFile-crc); COMP_CRC64(ControlFile-crc, --- 3128,3133 *** *** 3309,3341 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 (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 (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 --- 3297,3302 Index: src/backend/commands/dbcommands.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/dbcommands.c,v retrieving revision 1.155 diff -c -r1.155 dbcommands.c *** src/backend/commands/dbcommands.c 23 Mar 2005 00:03:28 - 1.155 --- src/backend/commands/dbcommands.c 25 Mar 2005 09:42:18 - *** *** 25,30 --- 25,34 #include unistd.h #include sys/stat.h + #ifdef HAVE_LANGINFO_H + #include langinfo.h + #endif + #include access/genam.h #include access/heapam.h #include catalog/catname.h *** *** 49,54 --- 53,59 #include utils/fmgroids.h #include utils/guc.h #include utils/lsyscache.h + #include utils/pg_locale.h #include utils/syscache.h *** *** 57,65 int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, Oid *dbLastSysOidP, TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP, ! Oid *dbTablespace); static bool have_createdb_privilege(void); static void remove_dbtablespaces(Oid db_id); /* --- 62,72 int *encodingP, bool *dbIsTemplateP, bool *dbAllowConnP, Oid *dbLastSysOidP, TransactionId *dbVacuumXidP, TransactionId *dbFrozenXidP, ! Oid *dbTablespace, char **dbCollate, char **dbCtype); static bool have_createdb_privilege(void); static void remove_dbtablespaces(Oid
Re: [PATCHES] Patch for database locale settings
On Mon, Mar 28, 2005 at 12:16:42PM +0400, Alexey Slynko wrote: this patch allow to use database locale settings. It remove cluster locale settings, and append LCCTYPE and LCCOLLATE items to CREATE DATABASE syntax. Any considerations ? The problem with this is what happens to indexes on shared relations. If you change the collation, they are no longer valid. And you can't just reindex them, because then they would no longer be valid for other databases. I havn't see any shared indices, that uses any variable collation, like text indices. All shared indexed columns have integer and name type. As I understand, all this types have fixed collation. Or I've missed something ? Maybe it would work if we forced indexes on shared relations to be scanned using a fixed collation. Not sure about the ctype part ... -- Alvaro Herrera ([EMAIL PROTECTED]) Los romnticos son seres que mueren de deseos de vida Alexey Slynko E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Patch for database locale settings
Alvaro Herrera [EMAIL PROTECTED] writes: Maybe it would work if we forced indexes on shared relations to be scanned using a fixed collation. The shared relations only have indexes on name, oid, and integer: select distinct atttypid::regtype from pg_class c join pg_attribute a on c.oid = a.attrelid where relisshared and relkind = 'i'; and name has non-locale-sensitive ordering rules anyway. So that's not the big problem; we could probably get away with decreeing that name will always be that way and that shared relations can't have locale-dependent indexes. The big problem (and the reason why this idea has been shot down in the past) is that CREATE DATABASE can't change the locale from what it is in the template database unless it's prepared to reindex any locale- sensitive indexes in the non-shared part of the template database. Which would be a difficult undertaking seeing that we can't even connect to the copied database until after commit. We could maybe say that we will never have any locale-dependent indexes at all on any system catalog, but what of user-defined tables in template databases? It would simply not work to do something as simple as creating a table with an indexed text column in template1. There is another way to broke indexes, like specify another user-defined template database in CREATE DATABASE. I think, that we can add new parameter, like LOCALEDEPEND, in CREATE TYPE syntax. It allows to separate locale-dependent indexes and reindex them. But I havn't yet any idea, how we can reindex database immediately after creation. Any suggestions ? On the other hand you could argue that people already run the same kind of risk when changing database encoding at CREATE, which is a feature that's been there a long time and hasn't garnered many complaints. Not so much that their indexes will break as that their data will. So perhaps we should be willing to document don't do that. Certainly it would be a lot more useful if both locale and encoding could be set per-database. regards, tom lane Alexey Slynko E-mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
