Re: gen_random_uuid key collision

jesusthefrog <jesusthefrog(at)gmail(dot)com> writes:
> I work with a postgres instance which has dozens (probably hundreds) of
> tables which each have a column defined as "uuid primary key default
> gen_random_uuid()".

> Most of the time this is fine, but one specific table has recently started
> repeatedly having inserts fail because of a unique constraint violation
> involving the primary key. The table only has ~10,000 rows, but I'll
> sometimes see two or three collisions in a single day. No other table (even
> those with many, many more rows) exhibit this issue.

That is pretty weird, all right. The only idea that comes to mind
immediately is that maybe that table's pkey index is corrupt and needs
to be reindexed. This isn't a great theory, because I don't see why
a corrupt index would lead to bogus unique-constraint errors rather
than missed ones. But at least it squares with the observation that
only that table is having issues.

BTW, are you *entirely* certain that your application never inserts
non-default values into that column?

regards, tom lane