Skip to content

Add find_duplicate_indexes helper to aid in partitioning index renames

Matt Kasa requested to merge database-partitioning-index-helper into master

What does this MR do and why?

In order to rename indexes between tables, which is needed when migrating a table to be it's own first partition, we need to match indexes by their definitions.

This does not work when there are duplicate indexes, same definition, different names.

This adds a helper that finds duplicate indexes so we can do things like:

  • Drop them in a migration
  • Prevent adding them in future
  • Prevent index renaming when a duplicate condition exists

The next MR in this series is !96812 (merged)

Related to #370275 (closed)

Database queries

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    i.relname AS index_name,
    regexp_replace(pg_get_indexdef(i.oid), 'INDEX .*? USING', '_') AS index_id
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_namespace n ON n.oid = c.relnamespace
WHERE (c.relkind = ANY (ARRAY['r'::"char", 'm'::"char", 'p'::"char"]))
    AND (i.relkind = ANY (ARRAY['i'::"char", 'I'::"char"]))
    AND n.nspname = 'public'
    AND c.relname = 'user_details';

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11852/commands/42090

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Matt Kasa

Merge request reports