Обсуждение: upper and lower doesn't work with german umlaut?
hello, what is wrong?
postgresql 7.1.3
suse linux 7.1
configure postgresql:
./configure --prefix=$DIR_DEST --exec-prefix=$DIR_DEST
--enable-local --enable-multibyte
--enable-odbc --enable-syslog --with-java
init database:
$DIR_DEST/bin/initdb --pgdata=$DIR_DB01 --encoding=LATIN1
check LANG-variable:
postgres@server:/app/pgsql/bin > echo $LANG
de_DE
and that's the result:
my_db=# select lower('ÄäÜüÖöß');
lower
---------
ÄäÜüÖöß
(1 row)
uppercase umlauts are not converted.
thanks
frank
=?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank.Koenig@rossmann.de> writes:
> uppercase umlauts are not converted.
Did you run initdb with the correct LANG environment?
To check, run contrib/pg_controldata and see what it says about
LC_COLLATE and LC_CTYPE.
regards, tom lane
Tom Lane a écrit : > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank.Koenig@rossmann.de> writes: > > uppercase umlauts are not converted. > > Did you run initdb with the correct LANG environment? > > To check, run contrib/pg_controldata and see what it says about > LC_COLLATE and LC_CTYPE. I have relativily the same problem! Where can I find the executable "contrib" ? Thanks!
On Tue, 27 Nov 2001 Vincent.Gaboriau@answare.fr wrote: > > Tom Lane a �crit : > > > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank.Koenig@rossmann.de> writes: > > > uppercase umlauts are not converted. > > > > Did you run initdb with the correct LANG environment? > > > > To check, run contrib/pg_controldata and see what it says about > > LC_COLLATE and LC_CTYPE. > > I have relativily the same problem! Where can I find the executable "contrib" > ? contrib refers to the contrib directory at the top level of the source tree.
Stephan Szabo a écrit : > On Tue, 27 Nov 2001 Vincent.Gaboriau@answare.fr wrote: > > > > > Tom Lane a écrit : > > > > > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank.Koenig@rossmann.de> writes: > > > > uppercase umlauts are not converted. > > > > > > Did you run initdb with the correct LANG environment? > > > > > > To check, run contrib/pg_controldata and see what it says about > > > LC_COLLATE and LC_CTYPE. > > > > I have relativily the same problem! Where can I find the executable "contrib" > > ? > > contrib refers to the contrib directory at the top level of the source > tree. > The sources of postgres have been deleted, so I can't find this "contrib"! So I surch another way to know if postgres have been installed with the local configuration. Can anyone help me?
On Tue, 27 Nov 2001 Vincent.Gaboriau@answare.fr wrote: > > > Stephan Szabo a �crit : > > > On Tue, 27 Nov 2001 Vincent.Gaboriau@answare.fr wrote: > > > > > > > > Tom Lane a �crit : > > > > > > > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank.Koenig@rossmann.de> writes: > > > > > uppercase umlauts are not converted. > > > > > > > > Did you run initdb with the correct LANG environment? > > > > > > > > To check, run contrib/pg_controldata and see what it says about > > > > LC_COLLATE and LC_CTYPE. > > > > > > I have relativily the same problem! Where can I find the executable "contrib" > > > ? > > > > contrib refers to the contrib directory at the top level of the source > > tree. > > > > The sources of postgres have been deleted, so I can't find this "contrib"! > So I surch another way to know if postgres have been installed with the local > configuration. > Can anyone help me? You might be able to get it by looking through the <data dir>/global/pg_control file, but it's a binary file so you'll have to search for it.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 2001 November 27 11:08 am, Vincent.Gaboriau@answare.fr wrote: > > > > To check, run contrib/pg_controldata and see what it says about > > > > LC_COLLATE and LC_CTYPE. > > > > > > I have relativily the same problem! Where can I find the executable > > > "contrib" ? > > > > contrib refers to the contrib directory at the top level of the source > > tree. > > The sources of postgres have been deleted, so I can't find this "contrib"! Under debian linux, it is /usr/lib/postgresql/bin/pg_controldata You should be able to find it quickly using % locate pg_controldata Alternately, if your system isn't properly maintained (and, unless you've got some seriously nice hardware, this will take some time: grab a coffee while you wait...), try % find / -name 'pg_controldata' -print 2> /dev/null - -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iEYEARECAAYFAjwDzroACgkQCT73CrRXhLGCDgCfcWiJQWBl25oDh1qkgliH9hZz ORIAnAtcWijMWTZXtWKWiPUorVz82GXU =9Tyz -----END PGP SIGNATURE-----
> > The sources of postgres have been deleted, so I can't find this "contrib"! > > So I surch another way to know if postgres have been installed with the local > > configuration. > > Can anyone help me? > > You might be able to get it by looking through the > <data dir>/global/pg_control file, but it's a binary file so you'll have > to search for it. I had found it, but I don't know speak fluent binary language ;-) Does a way exist to "decompile" it or to get informations on it? Thanks.
Vincent.Gaboriau@answare.fr writes:
>> You might be able to get it by looking through the
>> <data dir>/global/pg_control file, but it's a binary file so you'll have
>> to search for it.
> I had found it, but I don't know speak fluent binary language ;-)
> Does a way exist to "decompile" it or to get informations on it?
If you can't be troubled to compile up pg_controldata, then you'll
have to resort to good old od:
$ od -c pg_control
0000000 314 201 030 267 255 u 344 277 \0 \0 \0 G 013 355 p 253
0000020 \0 \0 \0 004 < 004 ) 006 \0 \0 \0 \0 \0 \0 \0 8
0000040 \0 \0 \0 \0 7 026 e 210 \0 \0 \0 \0 7 026 D h
0000060 \0 \0 \0 \0 7 026 e 210 \0 \0 \0 \0 \0 \0 \0 \0
0000100 \0 \0 \0 \t \0 001 357 235 \0 017 017 354 < 004 ) 004
0000120 \0 \0 \0 \0 002 \0 \0 C \0 \0 \0 \0 \0 \0 \0
0000140 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0000320 \0 \0 \0 \0 \0 \0 \0 \0 C \0 \0 \0 \0 \0 \0 \0
0000340 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
*
0020000
The LC_COLLATE and LC_CTYPE locale strings should be the last nonzero
things in the file --- they're both "C" in this example.
regards, tom lane
Tom Lane a écrit : > Vincent.Gaboriau@answare.fr writes: > >> You might be able to get it by looking through the > >> <data dir>/global/pg_control file, but it's a binary file so you'll have > >> to search for it. > > > I had found it, but I don't know speak fluent binary language ;-) > > Does a way exist to "decompile" it or to get informations on it? > > If you can't be troubled to compile up pg_controldata, then you'll > have to resort to good old od: > > $ od -c pg_control > 0000000 314 201 030 267 255 u 344 277 \0 \0 \0 G 013 355 p 253 > 0000020 \0 \0 \0 004 < 004 ) 006 \0 \0 \0 \0 \0 \0 \0 8 > 0000040 \0 \0 \0 \0 7 026 e 210 \0 \0 \0 \0 7 026 D h > 0000060 \0 \0 \0 \0 7 026 e 210 \0 \0 \0 \0 \0 \0 \0 \0 > 0000100 \0 \0 \0 \t \0 001 357 235 \0 017 017 354 < 004 ) 004 > 0000120 \0 \0 \0 \0 002 \0 \0 C \0 \0 \0 \0 \0 \0 \0 > 0000140 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 > * > 0000320 \0 \0 \0 \0 \0 \0 \0 \0 C \0 \0 \0 \0 \0 \0 \0 > 0000340 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 > * > 0020000 > > The LC_COLLATE and LC_CTYPE locale strings should be the last nonzero > things in the file --- they're both "C" in this example. > Thanks to Tom Lane! But I have This in my pg_control file: # od -c pg_control 0000000 \0 \0 \0 \0 001 \0 \0 \0 \0 \0 \0 \0 \b \0 \0 \0 0000020 ¤ 006 004 < 004 \0 \0 \0 \0 \0 \0 \0 \0 002 \0 0000040 ± Õ ë \v \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 0000060 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 * 0020000 # So no LC_COLLATE and LC_TYPE in the pg_control. But these variables are set in the user environement!
Vincent.Gaboriau@answare.fr writes:
> But I have This in my pg_control file:
> # od -c pg_control
> 0000000 \0 \0 \0 \0 001 \0 \0 \0 \0 \0 \0 \0 \b \0 \0 \0
> 0000020 � 006 004 < 004 \0 \0 \0 \0 \0 \0 \0 \0 002 \0
> 0000040 � � � \v \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
> 0000060 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0
> *
> 0020000
> #
Er ... *what* version did you say you were running? That doesn't look
like a 7.1 pg_control to me.
regards, tom lane
Tom Lane a écrit : > Vincent.Gaboriau@answare.fr writes: > > But I have This in my pg_control file: > > > # od -c pg_control > > 0000000 \0 \0 \0 \0 001 \0 \0 \0 \0 \0 \0 \0 \b \0 \0 \0 > > 0000020 ¤ 006 004 < 004 \0 \0 \0 \0 \0 \0 \0 \0 002 \0 > > 0000040 ± Õ ë \v \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 > > 0000060 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 > > * > > 0020000 > > # > > Er ... *what* version did you say you were running? That doesn't look > like a 7.1 pg_control to me. No, I have an 7.0.2 on Linux Mandrake 7.2.
Vincent.Gaboriau@answare.fr writes:
>> Er ... *what* version did you say you were running? That doesn't look
>> like a 7.1 pg_control to me.
> No, I have an 7.0.2 on Linux Mandrake 7.2.
Time to update then. 7.0 doesn't freeze the LC_COLLATE setting at
initdb, which means that you can corrupt your indexes by starting the
postmaster with different LC settings at different times. Which is
depressingly easy to do.
regards, tom lane
Tom Lane a écrit : > Vincent.Gaboriau@answare.fr writes: > >> Er ... *what* version did you say you were running? That doesn't look > >> like a 7.1 pg_control to me. > > > No, I have an 7.0.2 on Linux Mandrake 7.2. > > Time to update then. 7.0 doesn't freeze the LC_COLLATE setting at > initdb, which means that you can corrupt your indexes by starting the > postmaster with different LC settings at different times. Which is > depressingly easy to do. Ok! And does the new version correct my character sequences error?
Vincent.Gaboriau@answare.fr writes:
> Ok! And does the new version correct my character sequences error?
Which was ...?
regards, tom lane
Tom Lane a écrit : > Vincent.Gaboriau@answare.fr writes: > > Ok! And does the new version correct my character sequences error? > > Which was ...? Refer to " [ADMIN] character sequence problem" Thanks for your help. regards, Vincent.
> Vincent.Gaboriau@answare.fr writes:
>> Ok! And does the new version correct my character sequences error?
> Which was ...?
Oh, never mind (for some reason my first search for your previous
messages didn't turn up anything).
I'm not sure. The LIKE queries you were complaining of didn't look like
they could use an index anyway, so index corruption wouldn't explain
misbehavior there. But I'd recommend updating from 7.0 to 7.1
regardless.
regards, tom lane
Hello, Using vacuum a table is very usefull to compress the space of a table . However I noticed the index grows also very fast. Does anyone have a way to compress the index? I know if I dump the database, and destroy the orginal database, then create new one using the same database, and restory the dump file, that will dramatically compress the space the database has taken. This is not a good way for a live database that is driven by the web application that means I have to shut down the web application service during this clearup. Thanks, Bangh
bangh <banghe@baileylink.net> writes:
> However I noticed the index grows also very fast. Does anyone have a way to
> compress the index?
REINDEX, or just drop and recreate the indexes.
regards, tom lane
On Thu, 29 Nov 2001, Tom Lane wrote:
> bangh <banghe@baileylink.net> writes:
> > However I noticed the index grows also very fast. Does anyone have a way to
> > compress the index?
>
> REINDEX, or just drop and recreate the indexes.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Okay, after some more testing and cussing/discussing with myself, I have
decided that my "reindex" perl script is ready for public consumption.
To use the attached script, you will need to put your username, password
and hostname into the program (just look for the comment). If you run it
without any parameters, it will give you some simple instructions. I use
it something like:
$ fixtable.pl -I -t relevance kids
The program will then dump out SQL code to re-create ALL indexes (-I) for
the 'relevance' table (-t) in my 'kids' database. You can feed the output
of the script to 'psql', or look at it first (I am paranoid) and then
cut-n-paste it to 'psql' yourself.
The SQL code creates a new index for each index on a table, drops the
original index, renames the new index to the old name, and then repeats
for the next index on the table. This means that the user you login as
when you run 'psql' must own the indexes (otherwise the DROP INDEX fails).
If the drop fails for any reason, you will end up with TWO (2) identical
indexes which will almost certainly hurt performance on inserts. Also,
you need to have enough free space to have a second copy of your largest
index on the table or the CREATE INDEX will fail.
NOTE: This script DOES NOT DO ANYTHING with permissions because I don't
use them. If someone would like to give me some SQL code that will return
the old permissions, and set them on the new index, I would be happy to
add this functionality.
Finally. I am providing this script because I am a nice guy. If your
machine explodes or any table gets injured by your use of this script I
cannot and will not be held responsible. I wrote this script for my own
use, and the only time it has ever failed for me was when I had duplicated
values in a UNIQUE index field. Once I corrected the data corruption, the
script worked correctly. So, at least at first, I recommend that you look
at the SQL that is generated, and make sure you understand what it is
trying to do BEFORE you use it.
- brian
Wm. Brian McCane | Life is full of doors that won't open
Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those
Usenet http://freenews.maxbaud.net/ | that open when you don't want them to.
Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"