pg_upgrade: Drop all indexes from partition tables
This commit is part of Add Partitioned Indexes
Until indexes can be upgraded drop all indexes. Drop from the root
partition instead of on intermediate/leaf partitions, since they now
cascade due to their INTERNAL_AUTO dependencies.
@@ -13,6 +13,7 @@ BEGIN
JOIN pg_catalog.pg_class c ON (p.parrelid = c.oid)
JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
UNION
+
SELECT n.nspname,
partitiontablename AS relname
FROM pg_catalog.pg_partitions p
@@ -23,8 +24,19 @@ BEGIN
relname,
indexname
FROM partitions
- JOIN pg_catalog.pg_indexes ON (relname = tablename
- AND nspname = schemaname)
+ JOIN (SELECT n.nspname AS schemaname,
+ c.relname AS tablename,
+ i.relname AS indexname
+ FROM pg_index x
+ JOIN pg_class c ON c.oid = x.indrelid
+ JOIN pg_class i ON i.oid = x.indexrelid
+ LEFT JOIN pg_inherits inh ON inh.inhrelid = x.indexrelid
+ LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+ LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
+ WHERE (c.relkind = ANY (ARRAY ['r'::"char", 'm'::"char"]))
+ AND i.relkind = 'i'::"char"
+ AND inh.inhrelid is Null) inx
+ ON (relname = inx.tablename AND nspname = inx.schemaname)
LOOP
EXECUTE 'DROP INDEX IF EXISTS ' || quote_ident(part_indexes.nspname) || '.' || quote_ident(part_indexes.indexname);
END LOOP;
