pg_indexes doesn't show indexes for partitioned tables - bug orintended?

Поиск
Список
Период
Сортировка
От Thomas Kellerer
Тема pg_indexes doesn't show indexes for partitioned tables - bug orintended?
Дата
Msg-id q8ko5q$3t72$1@blaine.gmane.org
обсуждение исходный текст
Ответы Re: pg_indexes doesn't show indexes for partitioned tables - bug or intended?
Список pgsql-general
In Postgres 11.2, indexes defined on partitioned tables do not show up in pg_indexes (the actual indexes for the
partitionshowever do show up).
 

E.g.: 

    CREATE TABLE base_table
    (
      column1 varchar(50) NOT NULL,
      column2 integer NOT NULL,
      column3 integer not null,
      part_key bigint NOT NULL
    )
    PARTITION BY HASH (part_key);

    CREATE UNIQUE INDEX idx_one ON base_table (column1, column2, part_key);

The following select returns nothing:

  select *
  from pg_indexes
  where tablename = 'base_table';

This is caused by the fact that pg_indexes only returns information for regular tables and materialized views ("relkind
in('r','m')") and regular indexes (relkind = 'i')
 

If the conditions on the relkind for the "table class" to include 'p' as well, and the relkind for the "index class" is
changedto return 'i' and 'I', then those indexes are listed in pg_indexes as well:
 

 SELECT n.nspname AS schemaname,
    c.relname AS tablename,
    i.relname AS indexname,
    t.spcname AS tablespace,
    pg_get_indexdef(i.oid) AS indexdef
   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
     LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
  WHERE (c.relkind in ('r','m','p')) --<< add 'p' to the list
    AND i.relkind in ('i', 'I') --<< add 'I' to the list



Is leaving out the indexes defined on the partitioned table intended or a bug? 

Regards
Thomas




В списке pgsql-general по дате отправления:

Предыдущее
От: Jess Wren
Дата:
Сообщение: Re: How to use full-text search URL parser to filter query results bydomain name?
Следующее
От: Олег Самойлов
Дата:
Сообщение: Re: PK and FK using Hash index