Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A0C142E0322 for ; Thu, 13 Mar 2008 12:43:26 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 95519-06 for ; Thu, 13 Mar 2008 12:43:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id D9B3D2E02C2 for ; Thu, 13 Mar 2008 12:43:23 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2DFhMvS017760; Thu, 13 Mar 2008 11:43:22 -0400 (EDT) To: meetesh.karia@alumni.duke.edu cc: Robert Treat , pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: Encoding problems with migration from 8.0.14 to 8.3.0 on Windows In-reply-to: <47D8FB49.6000406@gmail.com> References: <47D7CBAA.4090308@gmail.com> <47D7DC8C.7000204@gmail.com> <200803122328.42683.xzilla@users.sourceforge.net> <47D8FB49.6000406@gmail.com> Comments: In-reply-to Meetesh Karia message dated "Thu, 13 Mar 2008 11:00:41 +0100" Date: Thu, 13 Mar 2008 11:43:22 -0400 Message-ID: <17759.1205423002@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/110 X-Sequence-Number: 28593 Meetesh Karia writes: > Additionally, here's what I get when I run your test below (my server > encoding is UTF-8): > ltefull=# create table x (r varchar(255) unique); > NOTICE: CREATE TABLE / UNIQUE will create implicit index "x_r_key" for > table "x" > CREATE TABLE > ltefull=# > ltefull=# set client_encoding=WIN1250; > SET > ltefull=# insert into x (r) values ('Daniel Brühl'); > INSERT 0 1 > ltefull=# > ltefull=# insert into x (r) values ('Daniel Bruehl'); > ERROR: duplicate key value violates unique constraint "x_r_key" You said this was on Windows, right? I was about to say "that should be impossible", until I looked at varstr_cmp() and realized that whoever put in the WIN32/UTF8 special case omitted this part: /* * In some locales strcoll() can claim that nonidentical strings are * equal. Believing that would be bad news for a number of reasons, * so we follow Perl's lead and sort "equal" strings according to * strcmp(). */ if (result == 0) result = strcmp(a1p, a2p); So we behave differently on Windows (with UTF8) than anywhere else. This is pretty nasty, not least because it means that texteq is inconsistent with other text comparison operators. I think this is a "must fix" bug for 8.3.1, anyone disagree? regards, tom lane