Re: client_encoding

If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default
the "client_encoding" in postgresql.conf when the server was last
started, or the value at the time the "ALTER DATABASE ... SET
client_encoding TO DEFAULT" statement is executed?

In other words, if I "ALTER DATABASE ... SET client_encoding TO DEFAULT"
and then restart the server with a different "client_encoding" value in
postgresql.conf, will client_encoding of the database follow the new
"client_encoding" value in postgresql.conf?

My guess is no; that it will keep the "client_encoding" it received
when the "ALTER DATABASE ... SET client_encoding TO DEFAULT" statement
was executed.

What I'm looking for is a way to have the client_encoding of all
databases track some common setting (eg, the "client_encoding" value in
postgresql.conf), but that may not be possible. I know I can use
"PGCLIENTENCODING=${LANG#*.}" (and that's not a bad solution), but I'm
curious if there's a way to avoid that (and similar client methods).
Right now I'm basically ignoring the database's "client_encoding"
setting, and always explicitly setting it in a session where needed.

-- Dean

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

"Dean Gibson (DB Administrator)" <postgresql(at)ultimeth(dot)com> writes:
> If I "ALTER DATABASE ... SET client_encoding TO DEFAULT", is the default
> the "client_encoding" in postgresql.conf when the server was last
> started, or the value at the time the "ALTER DATABASE ... SET
> client_encoding TO DEFAULT" statement is executed?

DEFAULT means that the database isn't overriding anything, so it would
be taken from whatever other source exists for the setting. Your first
suggestion isn't necessarily where it would come from --- but your
second one is definitely wrong.

IIRC client_encoding follows server_encoding unless the client-side code
specifies it wants something else, so this case is not a good example of
the general behavior of ALTER DATABASE.

> What I'm looking for is a way to have the client_encoding of all
> databases track some common setting (eg, the "client_encoding" value in
> postgresql.conf), but that may not be possible.

You can set client_encoding in postgresql.conf if you want to, but
I'm having a hard time understanding why you think that'd be a good
idea --- *particularly* if your database encodings aren't all the same.

regards, tom lane

On 2008-02-21 19:59, Tom Lane wrote:
> You can set client_encoding in postgresql.conf if you want to, but I'm having a hard time understanding why you think that'd be a good idea --- *particularly* if your database encodings aren't all the same.
>
> regards, tom lane
>

Actually, I'm getting external data in different encodings, and just
trying to understand what all my options are. I don't particularly like
the idea of setting (let alone changing) "client_encoding" in
postgresql.conf either (see below); I just mentioned that as a
potential example of where one possibly could set a default.

When working with PSQL, I'd like to be able to have the /default
/client_encoding track the current Linux shell encoding ($LANG) I'm
using at the moment, but change the client_encoding momentarily in scripts.

Setting "\encoding `echo ${LANG#*.}`" in a psqlrc file sets the proper
encoding, but doesn't set the default used by "SET client_encoding TO
DEFAULT", whereas "PGCLIENTENCODING=${LANG#*.}" sets the proper default,
unless $LANG changes.

So, I use "\encoding `echo ${LANG#*.}`" in a psqlrc file (that covers
interactive use), and add "export PGCLIENTENCODING=${LANG#*.}" in
scripts that run PSQL. That seems to be the best solution.

-----

The last two weekends, in order to maximize my options and flexibility,
I reinitialized all my servers with:

initdb -W -D /var/lib/pgsql/data --locale POSIX -E UTF8

I assume that gives me the most flexibility; if not, let me know (I
changed the four "LC_..." variables in postgresql.conf to = "en_US"). I
then recreated all my databases with a server_encoding of "UTF8", but
with varying client_encodings. For phpBB, UTF8, since v3.0 requires
that. For the others, either LATIN1 or UTF8, depending upon the
external data that's loaded into them.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.