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