Greenplum
如何刪除所有分區上的索引?
如果我在分區表上創建索引,每個分區都有自己的索引。如果我刪除主索引,這些不會被刪除。
是否有捷徑可尋?
我編寫了一個函式,用於查找與頂級索引名稱和分區後綴匹配的所有索引,並且該函式適用於在分區上創建的索引。
但是,當 Greenplum 通過預設分區拆分添加新分區時,它會為該分區生成一個具有完全不同命名約定的新索引。
現有分區的名稱類似於 indexname_1_prt_partitionname
新分區的名稱類似於 tablename_1_prt_partitionname_indexcolumn
當名稱不匹配時,知道如何辨識新分區索引是父索引的一部分嗎?或者我只是用兩種不同的模式呼叫我的索引刪除器兩次來匹配?
我將使用下面的 Bell 查詢,或者適用於獲取分區索引的這個查詢(如果我們已經刪除了頭索引):
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
Greenplum 沒有(在版本 4.3.8 中)在將分區索引連結到基表索引的目錄中維護記錄。最好的選擇是跟踪分區並在與基本索引的定義匹配的分區上查找索引。
CREATE OR REPLACE FUNCTION drop_child_indexes (index_name varchar) RETURNS VOID AS $functionBody$ DECLARE child_index_name varchar; BEGIN FOR child_index_name IN SELECT child_index.indexrelid::regclass FROM pg_index AS parent_index -- Find the partitioning scheme for the table the index is on INNER JOIN pg_partition ON pg_partition.parrelid = parent_index.indrelid -- 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 = parent_index.indkey -- Using the same comparison operator AND child_index.indclass = parent_index.indclass -- Filtered for the index we're trying to drop WHERE parent_index.indexrelid = $1::regclass::oid -- Drop leaves first, even if it doesn't really matter in this case ORDER BY pg_partition.parlevel DESC LOOP RAISE NOTICE '%', child_index_name||' '; EXECUTE 'DROP INDEX '||child_index_name||';'; END LOOP; END $functionBody$ LANGUAGE plpgsql;
如果有另一個具有相同定義(欄位、比較運算符)的索引,它的分區索引也將被刪除。不是一個完美的答案,但比子字元串匹配更好。