gen_random_uuid key collision

From: jesusthefrog <jesusthefrog(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: gen_random_uuid key collision
Date: 2021-09-02 23:25:17
Message-ID: CAH_wARuGPc6xDLGLo4U1=gvuKvnKO3NVjwzkFef5sPETQ9-Hcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello, I'm hoping someone might be able to shed a little light on a strange
situation I encountered recently.

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.

We're running postgres 12, so I believe the gen_random_uuid function is
provided by the pgcrypto extension, but either way it'll be the same for
that entire database instance, so I can't explain why only one table would
be having problems if it were due to a bug in the function. Also, since I
believe it just uses openssl (which we have linked) to generate random
bytes, the chance of a bug should be very low.

Anyone have any thoughts on this?

--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GIT d- s+ a- C++++ L+++ S++ B+ P++>++++ E++ W+++
N o? K- !w++++ O- M- V? PS++ PE- Y+ PGP t+ 5+++
X R+ tv b+++ DI++ D++ G+ e-- h- r++ y
------END GEEK CODE BLOCK------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2021-09-02 23:35:17 Re: gen_random_uuid key collision
Previous Message Tom Lane 2021-09-02 23:22:46 Re: Query takes around 15 to 20 min over 20Lakh rows