Обсуждение: 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"