Re: BUG #17892: Bug primary key

On Wed, Apr 12, 2023 at 3:39 AM PG Bug reporting form
<noreply(at)postgresql(dot)org> wrote:
> We have a database with over 3 milions apps in it
> Something happend we just found duplicates with same id, but we have
> constraints : PRIMARY KEY on id

You should run pg_amcheck/amcheck on your database to determine the
extent of the problem. You're running Postgres 13, which has the
amcheck corruption checking module, but not the more user-friendly
pg_amcheck utility. So you'll need to install the amcheck contrib
extension.

Once it is installed, you can run a query like the following (you may
want to customize this):

SELECT bt_index_check(index => c.oid, heapallindexed => true),
c.relname,
c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC;

The query will throw errors when it finds corruption. That should give
you a general idea of the extent of the problem. Maybe the only
corruption is the corruption that you know about already, but it's
more likely that other indexes are also affected.

If this query takes too long to complete you may find it useful to add
something to limit the indexes check, such as: AND n.nspname =
'public' -- that change to the SQL will make the query just test
indexes from the public schema. You could also customize it to just
check indexes on text columns, once you're sure that the problem is
collation related (ask me how if this is useful). You could also specify
"heapallindexed=>false" to make it run faster (though this will also be
less thorough).

Do "SET client_min_messages=DEBUG1 " to get a kind of rudimentary
progress indicator, if that seems useful to you.

The docs have further information on what this bt_index_check
function does:

https://www.postgresql.org/docs/13/amcheck.html
--
Peter Geoghegan