NAME

DBIO::PostgreSQL::Introspect::Indexes - Introspect PostgreSQL indexes

VERSION

version 0.900007

DESCRIPTION

Fetches index metadata from pg_catalog.pg_index, including access method, uniqueness, primary key flag, validity, the full index definition string from pg_get_indexdef, partial index predicates, and expression index expressions. Relations an installed extension owns are left out by DBIO::PostgreSQL::Introspect::Tables, and their indexes with them: an index is no extension member in its own right, so it would otherwise stay behind as an index of a table the model does not have, for the diff to drop.

An index on a partitioned table is carried down to every partition by the server: the partition has a copy of its own (pg_class.relispartition, tied to the parent's by pg_inherits). The copy is part of the parent's index, created and dropped with it and by no statement at the partition, so it is left out of the model. An index a partition has by its own declaration is no copy and is in it.

METHODS

fetch

my $indexes = DBIO::PostgreSQL::Introspect::Indexes->fetch($dbh, $filter);

Returns a hashref keyed by schema.table. Each value is a hashref keyed by index name. Each index entry has: schema_name and table_name (the table it is on, each name for itself -- the key cannot be taken apart again; the schema is the index's own as well, an index lives in its table's schema), index_name, access_method, is_unique, is_primary, is_valid, definition (full pg_get_indexdef output), predicate, expressions, columns (see below), include_columns, storage_params, opclasses, sort_options, constraint_name, constraint_type and referenced_by. All of it is read from the catalog, none of it out of the text of the definition.

columns is an ArrayRef with one entry for each key position of the index (the first pg_index.indnkeyatts positions of indkey), in that order: the name of the column, or undef where the position is an expression and no column. An index on an expression alone therefore has columns => [undef], not an empty list, and one on a column and an expression ['name', undef]; the expression itself is in expressions and in definition. The columns of an INCLUDE clause are not key positions: they are in include_columns and in no other entry. The shape is the same on a handle that returns arrays as strings (pg_expand_array off) as on one that returns ArrayRefs, as DBD::Pg does by default.

opclasses is a HashRef of column name to a non-default operator class, as SQL: the class is named with its schema, quoted as the server quotes it, where the session's search_path does not find it ("Ex Schema".my_ops), and the parameters of a class (PostgreSQL 13 and later) follow its name (tsvector_ops(siglen='32')). A class that is the default for its access method is left out unless it has parameters: pg_get_indexdef does not print it either. An expression position has no column to key it by and is left out.

sort_options is a HashRef of column name to the sort of that key column where it is not the default, from pg_index.indoption: DESC, DESC NULLS LAST or NULLS FIRST. Ascending with the nulls last, and descending with the nulls first, are the defaults and are not listed. Only an access method that orders (btree) has any. There is no key of pg_index to declare a sort with; it is in the model so that nothing reads it out of the definition string again.

constraint_name and constraint_type say whether a constraint stands behind the index: the name of the PRIMARY KEY, UNIQUE or EXCLUDE constraint and its kind, p, u or x; both undef for an index without one (a unique index created by CREATE UNIQUE INDEX is such an index). The server drops such an index only with its constraint. deferrable and initially_deferred are pg_constraint.condeferrable and condeferred of that constraint (1 or 0; 0 without a constraint). referenced_by is an ArrayRef of { schema_name, table_name, constraint_name }, one for each foreign key of a table that points at that constraint, empty when none does; the copies the server makes of the key of a partitioned table for its partitions are not named, as in DBIO::PostgreSQL::Introspect::ForeignKeys. Only the constraint's own index is named by a foreign key.

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.