Re: gen_random_uuid key collision

On Thu, Sep 2, 2021 at 4:48 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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