Re: BUG #17361: Unique index constraint inconsistence

On Tue, Jan 11, 2022 at 3:15 AM Роман Григорович <atzedus(at)gmail(dot)com> wrote:
> If I understand correctly, will the 'REINEX SYSTEM' command help to avoid such problems in the future OS upgrades?

No. It will only fix the problem after the fact. The only way to
prevent the problem is to make sure that your libc/OS collations never
change (except perhaps in a very controlled way). OS upgrades are
typically involved in cases where a change in the behavior of a
collation leads to index corruption. If you want to do an OS upgrade
(and cannot do an online upgrade using logical replication), then you
must account for the possibility that the underlying collations will
change.

I suggest that you use contrib/pageinspect to find any B-Tree indexes
that have problems. It's a contrib extension, so you must first run
"CREATE EXTENSION amcheck;". From there, 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.

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, should you need it:
https://www.postgresql.org/docs/12/amcheck.html

--
Peter Geoghegan