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');
INSERT 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