1--
2-- Check that system tables can be reindexed.
3--
4-- Note that this test currently is not included in the default
5-- schedules, as currently reindexing catalog tables can cause
6-- deadlocks:
7--
8-- * The lock upgrade between the ShareLock acquired for the reindex
9--   and RowExclusiveLock needed for pg_class/pg_index locks can
10--   trigger deadlocks.
11--
12-- * The uniqueness checks performed when reindexing a unique/primary
13--   key index possibly need to wait for the transaction of a
14--   about-to-deleted row in pg_class to commit. That can cause
15--   deadlocks because, in contrast to user tables, locks on catalog
16--   tables are routinely released before commit - therefore the lock
17--   held for reindexing doesn't guarantee that no running transaction
18--   performed modifications in the table underlying the index.
19--
20--   This is particularly problematic as such conflicts can be
21--   triggered even when run in isolation, as a previous session's
22--   temporary table cleanup might still be running (even when the
23--   session ended from a client perspective).
24
25
26-- Check reindexing of whole tables
27REINDEX TABLE pg_class; -- mapped, non-shared, critical
28REINDEX TABLE pg_index; -- non-mapped, non-shared, critical
29REINDEX TABLE pg_operator; -- non-mapped, non-shared, critical
30REINDEX TABLE pg_database; -- mapped, shared, critical
31REINDEX TABLE pg_shdescription; -- mapped, shared non-critical
32
33-- Check that individual system indexes can be reindexed. That's a bit
34-- different from the entire-table case because reindex_relation
35-- treats e.g. pg_class special.
36REINDEX INDEX pg_class_oid_index; -- mapped, non-shared, critical
37REINDEX INDEX pg_class_relname_nsp_index; -- mapped, non-shared, non-critical
38REINDEX INDEX pg_index_indexrelid_index; -- non-mapped, non-shared, critical
39REINDEX INDEX pg_index_indrelid_index; -- non-mapped, non-shared, non-critical
40REINDEX INDEX pg_database_oid_index; -- mapped, shared, critical
41REINDEX INDEX pg_shdescription_o_c_index; -- mapped, shared, non-critical
42