Thread: B-tree unique index duplicate key error happens only in SUSE 9.3
This bug happens in SUSE 9.3 on both Pentium 4 and AMD64, whether the binaries are from postgresql-8.0.1 RPMs on the SUSE 9.3 DVD or are built from 8.0.3 source code. However this bug does NOT happen with a Debian box (unstable) running 8.0.3 on an x86 (Athlon XP, whether binary or built from source). The problem is Postgresql claims two records has the same value for one string attribute that has a unique constraint, while in fact the two string values are different. To see this bug, just do a restore from the pg_dump'ed file attached to this email. Sample steps and error message follow: -------- begin command --------------- createdb -E utf8 pg_bug psql pg_bug < pg_dup_key_bug.dump NOTICE: CREATE TABLE / UNIQUE will create implicit index "gaocanusers_userid_key" for table "gaocanusers" CREATE TABLE ERROR: duplicate key violates unique constraint "gaocanusers_userid_key" CONTEXT: COPY gaocanusers, line 2: "129406 ���ズ� 27324923@nowhere.com.kk f U\N \N \N \N -- f 2002-09-12 00:00:00 \N \\3031\\3..." ----------- end ------------
Attachment
FYI, Works just fine on gentoo with the UTF8 and ICU patches. ... John > This bug happens in SUSE 9.3 on both Pentium 4 and AMD64,=20 > whether the binaries are from postgresql-8.0.1 RPMs on the=20 > SUSE 9.3 DVD or are built from 8.0.3 source code. However=20 > this bug does NOT happen with a Debian box (unstable) running=20 > 8.0.3 on an x86 (Athlon XP, whether binary or built from=20 > source). The problem is Postgresql claims two records has the=20 > same value for one string attribute that has a unique=20 > constraint, while in fact the two string values are=20 > different. To see this bug, just do a restore from the=20 > pg_dump'ed file attached to this email. Sample steps and=20 > error message follow:
Zhenlei Cai <jpenguin@gmail.com> writes: > This bug happens in SUSE 9.3 on both Pentium 4 and AMD64, whether the > binaries are from postgresql-8.0.1 RPMs on the SUSE 9.3 DVD or are > built from 8.0.3 source code. However this bug does NOT happen with a > Debian box (unstable) running 8.0.3 on an x86 (Athlon XP, whether > binary or built from source). The problem is Postgresql claims two What makes you think this is a Postgres bug, rather than a bug in the locale definition you are using on the SUSE box? Try feeding the two strings in question to strcoll() and see what happens. One way that you can get inconsistent results from strcoll() is if you feed it strings that are invalid according to the character set encoding that strcoll() thinks you are using, which is to say the encoding implied by the current LC_CTYPE locale setting. So it's possible that the real problem is that you have Postgres' database encoding set to something that's incompatible with the postmaster's LC_CTYPE locale. (Try "show lc_ctype" to see what that is exactly.) regards, tom lane