Re: primary index permits duplicates

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(at)sss(dot)pgh(dot)pa(dot)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 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

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