Обсуждение: false unique constraint error...for me

Hi all,
I don't know if it's a bug or not...but things are quite strange for me.
My problem comes from a unique constraint violation whereas tha data I try
to insert in my table are different (at least for me).
My database is encoded using SQL_ASCII, postgresql 7.4.8 on a Red Hat
Advanced Server v3 or 4)
I created a table :
CREATE TABLE trace_object (
    object_id serial NOT NULL,
    object_barcode character varying(15) NOT NULL,
    object_barcode_128 character varying(25),
);
ALTER TABLE ONLY trace_object ADD CONSTRAINT pk_trace_object PRIMARY KEY
(object_id);
ALTER TABLE ONLY trace_object ADD CONSTRAINT trace_object_object_barcode_key
UNIQUE (object_barcode);
ALTER TABLE ONLY trace_object ADD CONSTRAINT unique_barcode128 UNIQUE
(object_barcode_128);

The column "object_barcode" contains human readable barcode (e.g.
AB28662097) and the column "object_barcode_128" contains the "crypted"
barcode readable by LASER scanners once printed with the corresponding font.
We can determine the object_barcode_128 content, applying a function on
object_barcode (you can find it here :
http://grandzebu.net/informatique/codbar/code128_PLpgSQL.asc, sorry the
comments are in french). Let's call this function text2code128().

If I do :
INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES
('AB28662097', text2code128('AB28662097'));
INSERT INTO trace_object (object_barcode, object_barcode_128) VALUES
('AB28662098', text2code128('AB28662098'));

I get the error : ERROR: duplicate key violates unique constraint
"unique_barcode128"
But the string returned by text2code128('AB28662097') and
text2code128('AB28662098') are different!!!, i.e., respectively ÌABÇ<b4ÅÃÎ
and ÌABÇ<b4ÆÊÎ.

Why do I get an error here? I really don't understand...I get this error
using my cgi interface, phpPgAdmin and command line.

Some other things :
If I drop the unique constraint unique_barcode128, I can insert my previous
data. Then if the request is :
SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 =
(SELECT text2code128('AB28662098'))
the two rows are returned ('ÌABÇ<b4ÅÃÎ' and 'ÌABÇ<b4ÆÊÎ').

if my request is :
SELECT object_barcode_128 FROM trace_object WHERE object_barcode_128 LIKE
(SELECT text2code128('AB28662098'))
I get one row 'ÌABÇ<b4ÆÊÎ'

could you please help me understanding what happens....
I know that the unique index is created using B-TREE (CREATE UNIQUE INDEX
unique_barcode128 ON trace_object USING btree (object_barcode_128)). Is
there a way to have a look at the content of this index? Do you know how it
works and where I can find more information abour it?

thank you for your help
Gérald

_________________________________________________________________
Gagnez des pc Windows Vista avec Live.com http://www.image-addict.fr/

=?iso-8859-1?B?ZGrpIGRq6Q==?= <gerald2545@hotmail.com> writes:
> I get the error : ERROR: duplicate key violates unique constraint
> "unique_barcode128"
> But the string returned by text2code128('AB28662097') and
> text2code128('AB28662098') are different!!!, i.e., respectively �AB�<b4���
> and �AB�<b4���.

What locale are you running the server in?  It's possible that these
strings are equal according to the locale-specific strcoll() behavior.
In particular, if you are using a locale that expects UTF8, it's pretty
common for strcoll to go nuts when faced with non-UTF8-legal strings.

You might be better off using bytea instead of varchar.

            regards, tom lane

you were right, the server uses "lc_collate     en_US.utf8".
quite amazing situation.
As you mentioned, I replaced the column type (bytea instead of varchar) and
some code (text2code128 now returns bytea instead of text, using decode
function).
I have been able to insert my data without problem

Then, before creating my barcode labels, I use the decode function to change
the binary data to text data, and that's it.

I really appreciated your help.

Have a nice week-end

Gérald



>From: Tom Lane <tgl@sss.pgh.pa.us>
>To: djé djé <gerald2545@hotmail.com>
>CC: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] false unique constraint error...for me Date: Fri, 23
>Feb 2007 10:19:07 -0500
>
>=?iso-8859-1?B?ZGrpIGRq6Q==?= <gerald2545@hotmail.com> writes:
> > I get the error : ERROR: duplicate key violates unique constraint
> > "unique_barcode128"
> > But the string returned by text2code128('AB28662097') and
> > text2code128('AB28662098') are different!!!, i.e., respectively
>ÌABÇ<b4ÅÃÎ
> > and ÌABÇ<b4ÆÊÎ.
>
>What locale are you running the server in?  It's possible that these
>strings are equal according to the locale-specific strcoll() behavior.
>In particular, if you are using a locale that expects UTF8, it's pretty
>common for strcoll to go nuts when faced with non-UTF8-legal strings.
>
>You might be better off using bytea instead of varchar.
>
>            regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

_________________________________________________________________
Personnalisez votre Messenger avec Live.com
http://www.windowslive.fr/livecom/