Обсуждение: primary index permits duplicates

Поиск
Список
Период
Сортировка

primary index permits duplicates

От
H Hale
Дата:
Hello,

I am using Postgres 8.0.3 on Fedora Core 4. I may have found a bug in Postgres.

I have a table as follows:


      Table ".master"
  Column  |      Type      | Modifiers
----------+----------------+-----------
 objectid | dsuuid | not null
 classid  | dsuuid | not null
Indexes:
    "master_pkey" PRIMARY KEY, btree (objectid)

dsuuid is a custom data type for uuids with an external library with comparsion functions.

CREATE TYPE dsuuid (
    INTERNALLENGTH = 16,
    INPUT          = dsuuid_in,
    OUTPUT         = dsuuid_out,
   RECEIVE        = dsuuid_recv,
   SEND           = dsuuid_send,
   alignment      = CHAR
);

CREATE OPERATOR CLASS _uuid_ops
DEFAULT FOR TYPE dsuuid USING btree
AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       dsuuid_cmp(dsuuid, dsuuid);


Inserts to this table are done via triggers on other tables. I have found duplicate objectid column entries.
I have  reproduced the problem by inserting directly in the table using psql as follows:

capsa=# insert into master values('30000021-1111-2222-3333-444444444444','30000001-1111-2222-3333-444444444444');
INSERT 21633 1

capsa=# insert into master values('30000021-1111-2222-3333-444444444444','30000001-1111-2222-3333-444444444444');
ERROR:  duplicate key violates unique constraint "master_pkey"

capsa=# insert into master values('30000022-1111-2222-3333-444444444444','40000001-1111-2222-3333-444444444444');
INSERT 21635 1

capsa=# insert into master values('30000021-1111-2222-3333-444444444444','30000001-1111-2222-3333-444444444444');
I NSERT 21636 1

Note the last insert permits duplicate objectid to be inserted.

The uuid custom data type's compare functions have be confirmed to be correct.
I am logging the calls the libs compare functions.
For the last insert what I have found is the postgres finds match but continues checking.
The compare returns 0 if equal otherwise non-zero.

uuid_cmp : 30000021-1111-2222-3333-444444444444 30000021-1111-2222-3333-444444444444 0 <- match found
uuid_cmp : 30000022-1111-2222-3333-444444444444 30000021-1111-2222-3333-444444444444 1 <- but one more is checked










Re: primary index permits duplicates

От
Tom Lane
Дата:
H Hale <hhale21@rogers.com> writes:
>  dsuuid is a custom data type for uuids with an external library with comparsion functions.

Unless you can reproduce this with a standard datatype, you should
probably file this report with the developer(s) of dsuuid.  It sounds
like an incorrect comparison function to me.

>  The compare returns 0 if equal otherwise non-zero.

In fact, if that's what the code actually thinks the API is, that's
the problem right there ... it's supposed to be a three-way result.

            regards, tom lane

Re: primary index permits duplicates

От
H Hale
Дата:
dsuuid is my library
The library uses standard uuid comparison functions provided with linux. To clarify, the compare returns the same value the uuid compare functions.
 
From man page...
 
Returns an integer less than, equal to, or greater than zero if uu1 is found, respectively, to be lexigraphically less than, equal, or greater than uu2.
 
Is this not what Postgres expects?
 
As I mentioned what I have seen is that if  Postgresql finds a match it normally stops,  but in this the case I described it does 1 more comparison and adds a duplicate primary key.
 
This problem has appear a few times over the the last several months during normal use.  If I clear all the data from the db, I can no longer reproduce it. Once a duplicate key is found, then I can reproduce it  again as I described, so this will most likely not be easy to find. I find it only because of  checks for  rowcount==1 after a query in the application code. Postgres never complains as far as I can tell.
 
Let's assume for a moment the dsuuid lib is correct,  how then is it possible to get non-unique values for a primary index? 
 
Is there anything else I could do to track down the cause of this? Logging?
 
 
 
 
 
 
 


Tom Lane <tgl@sss.pgh.pa.us> wrote:
H Hale writes:
> dsuuid is a custom data type for uuids with an external library with comparsion functions.

Unless you can reproduce this with a standard dat atype, you should
probably file this report with the developer(s) of dsuuid. It sounds
like an incorrect comparison function to me.

> The compare returns 0 if equal otherwise non-zero.

In fact, if that's what the code actually thinks the API is, that's
the problem right there ... it's supposed to be a three-way result.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: primary index permits duplicates

От
Tom Lane
Дата:
H Hale <hhale21@rogers.com> writes:
>   Let's assume for a moment the dsuuid lib is correct,  how then is it possible to get non-unique values for a
primaryindex?   

Well, you're assuming a fact not in evidence as far as I'm concerned.
But feel free to send in a reproducible test case, and we'll take a look.

            regards, tom lane