35.4 索引、collation 与 `amcheck`

CREATE EXTENSION IF NOT EXISTS amcheck;

SELECT bt_index_check(
  index => 'public.orders_created_idx'::regclass,
  heapallindexed => false,
  checkunique => false
);

SELECT bt_index_check(
  index => 'public.orders_created_idx'::regclass,
  heapallindexed => true,
  checkunique => false
);

SELECT bt_index_parent_check(
  index => 'public.orders_created_idx'::regclass,
  heapallindexed => true,
  rootdescend => true,
  checkunique => false
);

exact reported index, heapallindexed=false
  -> exact index, heapallindexed=true on clone/low-load window
      -> parent check on writable clone or maintenance window
          -> wider object set by tablespace/provider/change scope

SELECT *
FROM verify_heapam(
  relation => 'public.orders'::regclass,
  on_error_stop => false,
  check_toast => true,
  skip => 'none',
  startblock => 0,
  endblock => 999
);

SELECT n.nspname,
       c.collname,
       c.collprovider,
       c.collversion AS stored_version,
       pg_collation_actual_version(c.oid) AS actual_version,
       c.collversion IS DISTINCT FROM
         pg_collation_actual_version(c.oid) AS mismatch
FROM pg_collation AS c
JOIN pg_namespace AS n ON n.oid = c.collnamespace
WHERE c.collversion IS NOT NULL
ORDER BY mismatch DESC, 1, 2;

SELECT datname,
       datcollversion AS stored_version,
       pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database
ORDER BY datname;

c = libc
i = ICU
b = builtin

SELECT i.indexrelid::regclass AS index_name,
       x.ord AS key_position,
       x.collation_oid::regcollation AS collation
FROM pg_index AS i
CROSS JOIN LATERAL
  unnest(i.indcollation) WITH ORDINALITY AS x(collation_oid, ord)
WHERE x.collation_oid <> 0
ORDER BY 3, 1, 2;

1. inventory every affected derived object
2. REINDEX / rebuild each object under intended comparison rules
3. run amcheck and business/order invariants
4. ALTER COLLATION ... REFRESH VERSION
   or ALTER DATABASE ... REFRESH COLLATION VERSION
5. repeat checks on every role/replica after rollout

data checksum / verify_heapam on source
TOAST readability
row count and partition coverage
constraints and foreign-key validation
business aggregates/ledger/token
seqscan vs indexscan bounded equivalence
amcheck after rebuild
backup/replica cross-check

same transaction snapshot
same WHERE/ORDER BY/collation
stable deterministic projection
bounded result
explicit NULL and duplicate handling
same role/GUC/RLS context

source readable
new index validates
temporary disk/WAL capacity sufficient
unique conflicts understood
cutover and rollback defined