Re: Vacuum

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(at)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(dot)Koenig(at)rossmann(dot)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

"König, Frank" writes:

> ./configure --prefix=$DIR_DEST --exec-prefix=$DIR_DEST
> --enable-local --enable-multibyte
^^^^^
locale

> --enable-odbc --enable-syslog --with-java

--
Peter Eisentraut peter_e(at)gmx(dot)net

> "K?nig, Frank" writes:
>
> > ./configure --prefix=$DIR_DEST --exec-prefix=$DIR_DEST
> > --enable-local --enable-multibyte
> ^^^^^
> locale
>
> > --enable-odbc --enable-syslog --with-java

And why does configure ignore flags it doesn't support. I sure don't
like that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> And why does configure ignore flags it doesn't support. I sure don't
> like that.

Complain to the GNU people. I've always considered this a serious bug
in autoconf, but they steadfastly maintain it's a feature.

regards, tom lane

Tom Lane a écrit :

> =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank(dot)Koenig(at)rossmann(dot)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(dot)Gaboriau(at)answare(dot)fr wrote:

>
> Tom Lane a crit :
>
> > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank(dot)Koenig(at)rossmann(dot)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(dot)Gaboriau(at)answare(dot)fr wrote:
>
> >
> > Tom Lane a écrit :
> >
> > > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank(dot)Koenig(at)rossmann(dot)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(dot)Gaboriau(at)answare(dot)fr wrote:

>
>
> Stephan Szabo a crit :
>
> > On Tue, 27 Nov 2001 Vincent(dot)Gaboriau(at)answare(dot)fr wrote:
> >
> > >
> > > Tom Lane a crit :
> > >
> > > > =?ISO-8859-1?Q?=22K=F6nig=2C_Frank=22?= <Frank(dot)Koenig(at)rossmann(dot)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(dot)Gaboriau(at)answare(dot)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(at)xyzzy(dot)dhs(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)fr writes:
> Ok! And does the new version correct my character sequences error?

Which was ...?

regards, tom lane

Tom Lane a écrit :

> Vincent(dot)Gaboriau(at)answare(dot)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(dot)Gaboriau(at)answare(dot)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(at)baileylink(dot)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(at)baileylink(dot)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"