From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: Issues with LC_COLLATE, across 8.3/8.4 on various platforms
Date: Tue, 2 Feb 2010 17:28:39 +0200
User-Agent: KMail/1.9.7
References: <201002021426.44936.achill@matrix.gatewaynet.com>
In-Reply-To: <201002021426.44936.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201002021728.39492.achill@matrix.gatewaynet.com>

It seems that libc's behaviour regarding LC_COLLATE in Linux/FreeBSD are tw=
o different stories, hence the seen results.
(Switching LC_COLLATE between en_US.UTF-8 and POSIX, in FreeBSD it does not=
 have any impact on orderring while in Linux does)
I think i have resolved the issue, so the solution boils down to setting LC=
_COLLATE=3DC in our linux central production postgresql server.
What is the shortest way to change the default locale on a production insta=
llation running PostgreSQL-8.3.9?
Is there anything less painful than dump, initdb,restore?
I know 8.4 allows for per-db locale settings, i am just asking if there is =
a way to solve the issue now without waiting till
the migration to 8.4

Thanx!

=CE=A3=CF=84=CE=B9=CF=82 Tuesday 02 February 2010 14:26:44 =CE=BF/=CE=B7 Ac=
hilleas Mantzios =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> Hi, i have the following problem, text ordering seems to behave incosiste=
ntly across various lc_collate values, OS'es, PostgreSQL versions.
> Some behaviour might be expected, some not, thats why i am asking to see =
where i stand with this.
> Test Data
> postgres@dynacom=3D# SELECT * from test_sort_order;
>          fooname
> -------------------------
>  Cylinder head cover No1
>  Cylinder Liner No1
>  Cylinder head No1
> (3 rows)
>=20
> Now the query=20
> # SELECT * from test_sort_order order by fooname;=20
> in PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu  (lc_collate=3Den_US.UTF-=
8) gives
> 	fooname
> -------------------------
>  Cylinder head cover No1
>  Cylinder head No1
>  Cylinder Liner No1
>=20
> while in all of
> PostgreSQL 8.3.3 on i686-pc-linux-gnu (lc_collate=3DC)
> PostgreSQL 8.3.7 (lc_collate=3Del_GR.UTF-8) on i386-unknown-freebsd6.3=20
> PostgreSQL 8.4.1 on x86_64-unknown-freebsd8.0 (lc_collate=3Den_US.UTF-8),=
 gives
> 	fooname
> -------------------------
>  Cylinder Liner No1
>  Cylinder head No1
>  Cylinder head cover No1
>=20
> Database encoding is SQL_ASCII in all four cases.
>=20
> --=20
> Achilleas Mantzios
>=20


=2D-=20
Achilleas Mantzios