gen_random_uuid key collision
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--
Re: gen_random_uuid key collision
On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver wrote: > What is the table schema as returned by \d in psql? > > The tables are in various schemas; that one is in one called "access_control", but we always set the search path explicitly to (in this case) "access_control, public". Anyway, if if were a problem with finding the function, I would be seeing a different error .The function successfully runs, it is just (apparently) occasionally generating the same UUID multiple times. -- -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--
Re: gen_random_uuid key collision
On Thu, Sep 2, 2021 at 7:47 PM Tom Lane wrote: > > BTW, are you *entirely* certain that your application never inserts > non-default values into that column? > > regards, tom lane > Yes, I double checked that we never attempt to bind a value for that column. I'll have a go at just rebuilding the pkey index and see if that helps. -- -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--
Re: gen_random_uuid key collision
On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver wrote: > But only one is generating errors. Schema refers to an object's > definition as well as a namespace. So what does: > > \d > > return? > I see what you mean. I don't have access to the instance at the moment so I'd have to take a look tomorrow. What, specifically, would be interesting in that output? When I looked at it this morning, I didn't see anything which looked out of the ordinary to me. -- -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--
Re: gen_random_uuid key collision
> > Note that the parent variant takes a disruptive lock that will block > write DML. You might prefer to just use the first query if this is > running in a production environment. > Fortunately this has only been observed on the dev instance. This morning I tried just dropping and recreating the index, so I'll see if that has solved it. If it has, then the root cause may remain a mystery; if not, I'll try amcheck. -- -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--
