Как я могу удалить все индексы таблицы в Postgres?

Question

Я продолжаю сталкиваться с этой проблемой: у меня около 20 индексов в таблице, которые мне нужно отбросить для проведения тестирования. Удаление таблицы не удаляет все эти метаданные.

Там, кажется, не подстановочный знак drop index ix_table_* или любая полезная команда. Кажется, вокруг psql есть несколько циклов bash, которые вы можете написать.
Там должно быть что-то лучше! Мысли?

Answer 1

Предполагая, что вы хотите отбросить только простые индексы:

DO
$$BEGIN
   EXECUTE (
   SELECT 'DROP INDEX ' || string_agg(indexrelid::regclass::text, ', ')
   FROM   pg_index  i
   LEFT   JOIN pg_depend d ON d.objid = i.indexrelid
                          AND d.deptype = 'i'
   WHERE  i.indrelid = 'your_table_name_here'::regclass  -- possibly schema-qualified
   AND    d.objid IS NULL                                -- no internal dependency
   );
END$$;

Не затрагивает индексы, созданные как детали реализации ограничений (UNIQUE, PK, EXCLUDE).
Документация:

DEPENDENCY_INTERNAL (i)

Зависимый объект был создан как часть создания ссылочного объекта и на самом деле является лишь частью его внутренней реализации.

Вы можете обернуть это в функцию для повторного выполнения.
Связанные с:

• Имя таблицы как параметр функции PostgreSQL

---

В сторону: это недоразумение

Удаление таблицы не удаляет все эти метаданные.

Удаление таблицы всегда происходит каскадно по всем индексам таблицы.

Answer 2

Вот так я удаляю все индексы из postgres, кроме всех pkey.

CREATE OR REPLACE FUNCTION drop_all_indexes() RETURNS INTEGER AS $$
DECLARE
  i RECORD;
BEGIN
  FOR i IN 
    (SELECT relname FROM pg_class
       -- exclude all pkey, exclude system catalog which starts with 'pg_'
      WHERE relkind = 'i' AND relname NOT LIKE '%_pkey%' AND relname NOT LIKE 'pg_%')
  LOOP
    -- RAISE INFO 'DROPING INDEX: %', i.relname;
    EXECUTE 'DROP INDEX ' || i.relname;
  END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

Выполнить:

SELECT drop_all_indexes();

Перед фактическим выполнением "DROP INDEX xxx" я бы закомментировал строку "EXECUTE ..." с помощью "-" и раскомментировал строку "RAISE INFO", запустив ее с помощью "select func_name();" и перепроверьте, я не уронил что-то, что я не должен.

Для нашего приложения у нас есть все операторы схемы, включая создание индексов в одном файле app.sql. Прежде чем весь этот проект перейдет в производство, мы хотим очистить все исторически созданные индексы, а затем воссоздать их, используя:

psql -f /path/to/app.sql

Надеюсь это поможет.

Answer 3

Приведенный ниже запрос удаляет все пользовательские индексы, не связанные с каким-либо ограничением (первичный ключ, уникальный ключ).

SELECT
    format('DROP INDEX %I.%I;', n.nspname, c_ind.relname)
  FROM pg_index ind
  JOIN pg_class c_ind ON c_ind.oid = ind.indexrelid
  JOIN pg_namespace n ON n.oid = c_ind.relnamespace
  LEFT JOIN pg_constraint cons ON cons.conindid = ind.indexrelid
  WHERE
    n.nspname NOT IN ('pg_catalog','information_schema') AND 
    n.nspname !~ '^pg_toast'::TEXT AND
    cons.oid IS NULL

Ты можешь использовать \gexec Функция метакоманды psql для выполнения оператора

Answer 4

Ради KISS я бы рассмотрел подход, упомянутый в официальной реализации https://www.postgresql.org/docs/7.2/sql-altertable.html#:~:text=For%20example%2C%20to% 20drop%20все%20ограничения%20на%20а%20таблица

      -- store table structure 
-- into temp table 
create temporary table t$ as 
select * from table_name limit 0;
-- drop the table, 
-- 'cascade' ensures it will not 
-- complain about any dependencies
drop table table_name cascade;
-- recreate the original table
create table table_name as 
select * from t$;
-- drop temporary table
drop table t$;