initdb stores default client_encoding from environment-variable

initdb stores default client_encoding from environment-variable

Postgres Version: 8.4.3 and 8.3.6
Operating System: Sun Solaris 5.10 and SuseEnterprise 9

When a database is initialized with the initdb-command, the default
client_enconding, which will be stored in the DB, depends on the value
of the environment-variable PGCLIENTENCODING at the time of running
initdb. This behaviour is not documented.
Furthermore I didn't find a command to change this
default client_encoding in the database later.
The default client_encoding does not depend on the database encoding!

This is a small shell-script to show this behaviour.

#!/bin/bash -x
#
PGHOST="localhost"
PGPORT=7654
PGDATABASE=postgres
PGUSER=postgres
export PGHOST PGPORT PGDATABASE PGUSER
export LD_LIBRARY_PATH=/usr/local/pgsql/lib

binpath=/usr/local/pgsql/bin
dir=/data/DB-2

$binpath/pg_ctl stop -D $dir/pg-base -m fast -o '-p 7654'
#
# remove Database
#
rm -r $dir/pg-base 2> /dev/null
sleep 1
mkdir $dir/pg-base 2> /dev/null

PGCLIENTENCODING="WIN1250"
export PGCLIENTENCODING

$binpath/initdb --encoding=UTF8 -D $dir/pg-base

$binpath/pg_ctl start -D $dir/pg-base -l $dir/pg-server.log -o '-p 7654'

sleep 5

unset PGCLIENTENCODING # IMPORTANT !!

$binpath/psql -c "select version();"
$binpath/psql -c "show client_encoding;"

On Fri, Apr 9, 2010 at 4:49 AM, Wolfgang.Koenig
<Wolfgang(dot)Koenig(at)versatel(dot)de> wrote:
> When a database is initialized with the initdb-command, the default
> client_enconding, which will be stored in the DB, depends on the value
> of the environment-variable PGCLIENTENCODING at the time of running
> initdb. This behaviour is not documented.

Hmm... yes, that does appear to be an oversight in the documentation.

...Robert

[re-posting as I mistakenly sent this only to the OP]

On 09/04/10 16:49, Wolfgang.Koenig wrote:
> initdb stores default client_encoding from environment-variable
>
>
> Postgres Version: 8.4.3 and 8.3.6
> Operating System: Sun Solaris 5.10 and SuseEnterprise 9
>
> When a database is initialized with the initdb-command, the default
> client_enconding, which will be stored in the DB, depends on the value
> of the environment-variable PGCLIENTENCODING at the time of running
> initdb. This behaviour is not documented.
> Furthermore I didn't find a command to change this
> default client_encoding in the database later.
> The default client_encoding does not depend on the database encoding!

I cannot reproduce this issue based on the test script you've provided.
However, I was testing on Ubuntu 9.10, not a vanilla 8.4, so this
can't be taken as definitive.

$ env | grep PG
$ PGPORT=7654 psql -U postgres postgres -c 'show client_encoding;'
client_encoding
-----------------
UTF8
(1 row)

$ PGPORT=7654 psql -U postgres postgres
(snip blah blah)
postgres=# show port ;
port
------
7654
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access
privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 |
template0 | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 | =c/postgres
:
postgres=CTc/postgres
template1 | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 | =c/postgres
:
postgres=CTc/postgres
(3 rows)

$ PGCLIENTENCODING="WIN1250" PGPORT=7654 psql -U postgres postgres -c
'show client_encoding;'
client_encoding
-----------------
WIN1250
(1 row)

--
Craig Ringer

Perhaps this is some Solaris-specific change he is seeing.

---------------------------------------------------------------------------

Craig Ringer wrote:
> [re-posting as I mistakenly sent this only to the OP]
>
> On 09/04/10 16:49, Wolfgang.Koenig wrote:
> > initdb stores default client_encoding from environment-variable
> >
> >
> > Postgres Version: 8.4.3 and 8.3.6
> > Operating System: Sun Solaris 5.10 and SuseEnterprise 9
> >
> > When a database is initialized with the initdb-command, the default
> > client_enconding, which will be stored in the DB, depends on the value
> > of the environment-variable PGCLIENTENCODING at the time of running
> > initdb. This behaviour is not documented.
> > Furthermore I didn't find a command to change this
> > default client_encoding in the database later.
> > The default client_encoding does not depend on the database encoding!
>
>
> I cannot reproduce this issue based on the test script you've provided.
> However, I was testing on Ubuntu 9.10, not a vanilla 8.4, so this
> can't be taken as definitive.
>
> $ env | grep PG
> $ PGPORT=7654 psql -U postgres postgres -c 'show client_encoding;'
> client_encoding
> -----------------
> UTF8
> (1 row)
>
> $ PGPORT=7654 psql -U postgres postgres
> (snip blah blah)
> postgres=# show port ;
> port
> ------
> 7654
> (1 row)
> postgres=# \l
> List of databases
> Name | Owner | Encoding | Collation | Ctype | Access
> privileges
> -----------+----------+----------+-------------+-------------+-----------------------
> postgres | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 |
> template0 | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 | =c/postgres
> :
> postgres=CTc/postgres
> template1 | postgres | UTF8 | en_AU.utf-8 | en_AU.utf-8 | =c/postgres
> :
> postgres=CTc/postgres
> (3 rows)
>
>
>
> $ PGCLIENTENCODING="WIN1250" PGPORT=7654 psql -U postgres postgres -c
> 'show client_encoding;'
> client_encoding
> -----------------
> WIN1250
> (1 row)
>
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

"Wolfgang.Koenig" <Wolfgang(dot)Koenig(at)versatel(dot)de> writes:
> initdb stores default client_encoding from environment-variable
> Postgres Version: 8.4.3 and 8.3.6
> Operating System: Sun Solaris 5.10 and SuseEnterprise 9

> When a database is initialized with the initdb-command, the default
> client_enconding, which will be stored in the DB, depends on the value
> of the environment-variable PGCLIENTENCODING at the time of running
> initdb.

Hmm, it doesn't do that for me. initdb actually takes some pains to
ensure that PGCLIENTENCODING doesn't affect it:

/* Also ensure backend isn't confused by this environment var: */
unsetenv("PGCLIENTENCODING");

I suppose that unsetenv isn't working properly on your platform for some
reason. Does configure think that it's present? If not, we're going to
fall back to the implementation in src/port/unsetenv.c, which might not
work for you.

regards, tom lane