If I create an index on a partitioned table, each partition gets its own index. These are not dropped if I drop the main index.
Is there an easy way to do this?
I have written a function that looks for all the indexes that match the top level index name with a partition suffix, and that works fine for indexes that were created on partitions.
However, when Greenplum adds a new partition through a default partition split, it generates a new index for that partition with a totally different naming convention.
Existing partitions get a name like indexname_1_prt_partitionname
New partitions get a name like tablename_1_prt_partitionname_indexcolumn
Any idea how I can identify that the new partition index is part of the parent index, when the name doesn't match? Or do I just call my index deleter twice with two different patterns to match?
I will be using either Bell's query below, or this one which is adapted to take a partition index (in case we have already deleted the head index):
SELECT child_index.indexrelid::regclass FROM pg_index AS partition_index -- Find the partition that the partition index is on INNER JOIN pg_partition_rule parindex_rule ON parindex_rule.parchildrelid = partition_index.indrelid -- Follup up to the partitioning scheme INNER JOIN pg_partition ON pg_partition.oid = parindex_rule.paroid -- Follow the links through to the individual partitions INNER JOIN pg_partition_rule ON pg_partition_rule.paroid = pg_partition.oid -- Find the indexes on each partition INNER JOIN pg_index AS child_index ON child_index.indrelid = pg_partition_rule.parchildrelid -- Which are on the same field as the named index AND child_index.indkey = partition_index.indkey -- Using the same comparison operator AND child_index.indclass = partition_index.indclass -- Filtered for the index we're trying to drop WHERE partition_index.indexrelid = 'schema.partitionindexname'::regclass