Thread: gen_random_uuid key collision

On 9/2/21 4:25 PM, jesusthefrog wrote:
> Hello, I'm hoping someone might be able to shed a little light on a 
> strange situation I encountered recently.

> 
> 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.

What is the table schema as returned by \d <table> in psql?

> Anyone have any thoughts on this?
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

jesusthefrog <jesusthefrog@gmail.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

On 9/2/21 4:38 PM, jesusthefrog wrote:
> On Thu, Sep 2, 2021 at 7:35 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     What is the table schema as returned by \d <table> in psql?
> 
> 
> The tables are in various schemas; that one is in one called 

But only one is generating errors. Schema refers to an object's 
definition as well as a namespace. So what does:

\d <table>

return?

> "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------


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 9/2/21 5:38 PM, jesusthefrog wrote:
> 
> On Thu, Sep 2, 2021 at 8:05 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     But only one is generating errors. Schema refers to an object's
>     definition as well as a namespace. So what does:
> 
>     \d <table>
> 
>     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 

I have no idea, probably more about eliminating possibilities then anything.

> 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------


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Sep 2, 2021 at 4:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

This is easy enough to check using the contrib/amcheck extension.

jesusthefrog could try this, and report back what they see:

CREATE EXTENSION IF NOT EXISTS amcheck
SELECT bt_index_check('my_uuid_index', true);

If that doesn't show any errors, then there is a chance that this will:

SELECT bt_index_parent_check('my_uuid_index', true);

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.

--
Peter Geoghegan

> On Sep 2, 2021, at 4:25 PM, jesusthefrog <jesusthefrog@gmail.com> wrote:
>
> Anyone have any thoughts on this?

I agree with Peter's suggestion upthread to run amcheck on the index, but if that comes back with no corruption, can
youverify that there are no rules or triggers that might cause multiple copies of the rows to be inserted?  Likewise,
canyou verify that you have no replication subscriptions that could be putting duplicates into the table? 

Another idea that seems unlikely given your lack of trouble with other tables is that you might check whether you have
anyfunctions that reset the seed for your random generator.  I haven't looked specifically at your uuid generator, and
Idon't know if it gets nondeterministic randomness from /dev/random or similar, but deterministic random generators can
bemade to produce the same sequence again if the seed it reset. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company