NAME
DBIO::PostgreSQL::Diff::Index - Diff operations for PostgreSQL indexes
VERSION
version 0.900007
DESCRIPTION
Represents an index-level diff operation: CREATE INDEX or DROP INDEX, and for an index that stands behind a PRIMARY KEY or UNIQUE constraint the constraint: ALTER TABLE ... ADD CONSTRAINT and DROP CONSTRAINT -- the server refuses DROP INDEX for such an index. When an index definition changes, it generates a DROP followed by a CREATE. The full definition string from pg_get_indexdef is used for comparison, so partial indexes, expression indexes, and storage parameters are all detected correctly; the constraint behind the index, which the string does not say, is compared beside it.
ATTRIBUTES
table_key
The schema.table key for the table this index belongs to. It is where the model files the table, and what "summary" prints; it is not SQL.
schema_name
The schema of that table -- and of the index: an index lives in the schema of its table.
table_name
The name of that table. "as_sql" writes names from schema_name, table_name and index_name, each quoted for itself -- the key cannot be taken apart again, a schema or a table may have a dot in its name. "diff" takes both from the index entry, or from the tables sections. An operation built by hand without them falls back to the key ("key_names" in DBIO::PostgreSQL::Introspect).
index_name
The index name.
index_info
Index metadata hashref (definition, access_method, columns, etc.).
replaced
True on the drop of a changed index: the index is not removed, the create that follows puts it back with its new definition. It tells DBIO::PostgreSQL::Diff to leave the two together instead of moving the drop to the removals ("ORDER OF OPERATIONS" in DBIO::PostgreSQL::Diff).
via
What a create writes: undefined for the index from its definition, constraint for ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY|UNIQUE (columns), using_index for ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY|UNIQUE USING INDEX name, which puts the constraint on the index that is there. "diff" sets it; an operation built by hand without it writes the index.
renamed_from
On a rename: the name the index has in the database. index_name is the name it gets, the one of the constraint that is put on it. With "via" constraint on a rename it is the name of the constraint (and of its index) that is renamed: ALTER TABLE ... RENAME CONSTRAINT.
partitions
On a create of an index of a partitioned table: the indexes to put at its partitions, a list of hashes schema_name, table_name, index_name, definition (the text of the index at that partition) and partitions (the same, for a partition that is itself partitioned; its definition is ON ONLY). "as_sql" writes them behind the index.
METHODS
diff
my @ops = DBIO::PostgreSQL::Diff::Index->diff(
$source, $target, $source_tables, $target_tables,
$source_foreign_keys, $target_foreign_keys);
Compares index sets across all tables. An index is identified by its schema and its name -- PostgreSQL keeps index names unique per schema, not per database, so two schemas may each have an orders_created_idx (every tenant schema of a multi-tenant layout does) and the two are different indexes. Within a schema the name alone identifies it, whichever table it is on. A changed definition produces a drop-then-create pair.
The optional $source_tables / $target_tables hashrefs are the tables sections of the two models (threaded in by DBIO::PostgreSQL::Diff via its aux-section wiring). They are used to detect tables that are being dropped in this same diff pass: DROP TABLE removes that table's own indexes, so no standalone DROP INDEX is emitted for them. The same holds for a materialized view, which is filed among the tables: DROP MATERIALIZED VIEW takes its indexes. Left in, such a drop comes ahead of the table's ("ORDER OF OPERATIONS" in DBIO::PostgreSQL::Diff) and fails for every index that carries a constraint -- the primary key's first of all -- with cannot drop index ... because constraint ... requires it. When these arguments are absent (e.g. direct unit-test calls) the set is empty and no drops are suppressed, preserving the original two-argument behaviour.
The index of a constraint. The model says which index a constraint stands behind: constraint_name and constraint_type (p, u or x) of its entry ("fetch" in DBIO::PostgreSQL::Introspect::Indexes). The diff takes that index away as ALTER TABLE schema.table DROP CONSTRAINT name;, and the summary says -constraint:. A unique index no constraint stands on stays a DROP INDEX. A changed index of a constraint goes the same way when the target has the name as an index: the constraint is dropped and the index of the target created.
The constraint of a key is created as a constraint. pg_get_indexdef writes the same text for the index of a PRIMARY KEY or UNIQUE constraint and for a unique index no constraint stands on, so a constraint created from that text would be an index, and the comparison by definition would never find it missing. Where the target says a constraint stands behind the index, the diff writes the constraint:
ALTER TABLE schema.t ADD CONSTRAINT name PRIMARY KEY (a, b);
ALTER TABLE schema.t ADD CONSTRAINT name UNIQUE [NULLS NOT DISTINCT] (a)
[INCLUDE (...)] [WITH (...)];
(via constraint, the summary says +constraint:). The columns are written in the order of the key. Besides the definition, the comparison looks at the kind of constraint behind the index, the key columns in their order, the INCLUDE columns and the storage parameters (ADR 0003, addendum 2026-10-20). A UNIQUE constraint also carries its timing: the model keys deferrable and initially_deferred of the entry (pg_constraint.condeferrable, condeferred) are compared, and the constraint is written DEFERRABLE INITIALLY IMMEDIATE or DEFERRABLE INITIALLY DEFERRED behind the column list, INCLUDE and WITH, also in the USING INDEX form. A primary key's timing is not read. A class declares it with "pg_unique_options" in DBIO::PostgreSQL::Result. A plain RENAME CONSTRAINT keeps the timing; a constraint of the database that has the target's definition under another name but another timing is not renamed, it is dropped (replaced) and the target's created. A constraint that differs from the database's under its name is dropped and added again, as one change (the drop is replaced); the target's plain index of a database's constraint is the way back, and stays an index, never a second primary key.
The database that has the key only as an index -- what an upgrade made before this -- is repaired in place. The source has the same index, the target a constraint on it:
ALTER TABLE schema.t ADD CONSTRAINT name PRIMARY KEY USING INDEX name;
(via using_index), no second index, no rebuild, the index keeps its name and its oid. The form without USING INDEX fails with relation "name" already exists. Measured on PostgreSQL 18.6, the server takes this for an index that is unique and btree, without predicate or expression, with the default sort order and valid, on a table that is not partitioned and has no primary key of another name; INCLUDE columns and NULLS NOT DISTINCT are kept; a primary key over a nullable column sets it NOT NULL. The constraint takes its name from the index: a constraint named otherwise would rename the index.
So these cases are refused, with refusing to diff: ..., no statement, and the ALTER TABLE that settles it:
the constraint to create is an EXCLUDE constraint, whose elements are not in the model; the message names
ADD CONSTRAINT n EXCLUDE (...). A key with an expression, a sort order or an operator class cannot be a PRIMARY KEY or UNIQUE constraint either.the table has a primary key of the database under another name than the one of the target and with another definition. The diff creates before it drops, and the server takes one primary key per table (
multiple primary keys). The message names theDROP CONSTRAINTto run first. The same name is no case: it is dropped and added again; the same definition under another name is renamed.the index of the database is one
USING INDEXdoes not take (not unique, not btree, partial, an expression, a sort order, not valid, on a partitioned table, named otherwise than the constraint). The message namesDROP INDEXand theADD CONSTRAINTthat put it right.foreign keys of other tables point at the constraint (
referenced_by) and are still there whenDROP CONSTRAINTruns, so it would fail. The message namesALTER TABLE other DROP CONSTRAINT fkfor each. A key is not there any more when the target model declares foreign keys (the optional fifth and sixth argument, theforeign_keyssections) and the key is not in it, or is a key to other columns: the diff drops it (DBIO::PostgreSQL::Diff::ForeignKey), the drops of the keys come ahead of the drops of the indexes, and the constraint goes without a refusal. That holds for a constraint that goes, not for one that is dropped and added again: the first half of such a change stands ahead of the keys' ("ORDER OF OPERATIONS" in DBIO::PostgreSQL::Diff), so a key that points at it is refused whatever the target does with the key. Without the sections -- a model built by hand that says nothing of foreign keys -- every key that points at a constraint to drop is refused. A key of a table the target drops, or one the source model does not list (the copy at a partition), cannot be shown to be gone before the constraint is, and is refused as well.an index to create and one to drop, on the same table, whose names differ only in upper and lower case, when the one to drop stands behind a constraint (DBIO::PostgreSQL::Diff::CaseOnly). This is where the index of a primary key ends up once its table has been renamed from
useraccounttoUserAccount: it is still calleduseraccount_pkey. The message names both and the way out,ALTER INDEX public.useraccount_pkey RENAME TO "UserAccount_pkey", which renames the constraint with the index.
The index under another name. The database has a unique index that no constraint stands on, the target a PRIMARY KEY or UNIQUE constraint on the same table with the same definition but for the index's name -- a database an older upgrade made, or one made by hand, where the index is called parent_code_uidx and the target's constraint parent_code_key. A second index would leave the foreign keys on the old one, and the old one could not be dropped (cannot drop index ... because other objects depend on it), so the diff renames the one that is there and puts the constraint on it:
ALTER INDEX schema.parent_code_uidx RENAME TO parent_code_key;
ALTER TABLE schema.parent ADD CONSTRAINT parent_code_key UNIQUE USING INDEX parent_code_key;
(an operation of the action rename, then the create of "via" using_index; the summary says ~index: old -> new). The reasons that refuse USING INDEX above hold here as well, but for the name. An index serves one constraint, and an index the target keeps under its own name is not taken. Any other index -- a unique one no constraint stands on included -- is created under the one name and dropped under the other, as any index that is renamed.
The name is taken, or the constraint has another name. If the target's name is held by another index of the database -- one that is no key of the same shape, say a nonunique index parent_code_key beside the unique parent_code_uidx -- that index is dropped first and the rename above follows; the old index is not dropped after the key was put on it. If the database has the constraint itself under another name, of the same kind and definition (parent_code_uniq against parent_code_key, a primary key parent_id_pk against parent_pkey), it is renamed with its index and keys:
ALTER TABLE schema.parent RENAME CONSTRAINT parent_code_uniq TO parent_code_key;
(a rename of "via" constraint; the summary says ~constraint: old -> new), where ADD and DROP would fail on the foreign keys that stand on it.
An index the run drops while a key stands on it is refused ahead of the apply: a foreign key of the database on an index that is not renamed or kept, and a foreign key the target creates on the columns of such an index -- the server binds a new key to the first (the oldest) unique index over its columns, not to the one the target adds, and cannot drop that one at the end of the run. The message names the index, the key and the statement that settles it (drop the key, or the index, and diff again).
An index that is new, and a target index no constraint stands on, is created as the index.
The index of a partitioned table. pg_get_indexdef writes it CREATE INDEX name ON ONLY schema.p USING ..., and an index made from that text is invalid (indisvalid false) until every partition has an index attached to it. So the diff writes the text, and then, for each partition of the table that both models have (partition_of in the tables section of the source, where the statements run; a partition the target drops gets none), the same index at the partition and ALTER INDEX ... ATTACH PARTITION:
CREATE INDEX p_tag ON ONLY public.p USING btree (tag);
CREATE INDEX p1_tag_idx ON public.p1 USING btree (tag);
ALTER INDEX public.p_tag ATTACH PARTITION public.p1_tag_idx;
A partition that is partitioned itself gets ON ONLY, its own partitions (deeper first) and then its attach. The name of a partition's index is the one the server gives an index it names itself, partition_columns_idx (an expression is expr), shortened to 63 bytes by the server's rule (the longer of table and column part loses a character at a time); a name the schema already holds in the source model, or that this run has given, gets a number (_idx1, _idx2), as the server numbers. The copies the server made of an index at partitions are not in the model, so a name one of them holds is reserved from the names the copies of the indexes of the source would have (the same rule), not from what the catalog holds; where the catalog differs the server refuses the CREATE INDEX for the existing name. The copies are not compared (DBIO::PostgreSQL::Introspect::Indexes), and a changed index is dropped, which takes its copies, and created again. A PRIMARY KEY or UNIQUE constraint is created by ALTER TABLE ... ADD CONSTRAINT, which the server carries down to the partitions itself.
An index to create -- a new one or a changed one -- that the target model has no definition for is refused as well (DBIO::PostgreSQL::Diff::NoStatement), whether or not the model has its columns. The definition is what the diff writes an index from and compares it by. The columns are not the index: that it is unique, its predicate, its access method, the order and the operator class of each column would be lost, and a unique partial index would come back as a plain one. pg_get_indexdef gives no definition for an index another session dropped while the model was read, and the model then still has its columns. An index only the source lacks the definition of is dropped and created like any other whose definition differs, and a drop needs none.
as_sql
Returns the SQL for this operation. For create, the full pg_get_indexdef definition string of its index_info -- or, when "via" is set, the constraint: ALTER TABLE schema.table ADD CONSTRAINT name PRIMARY KEY (a, b); (constraint) or ... PRIMARY KEY USING INDEX name; (using_index), written from the constraint_name, constraint_type and the columns of the index_info; dies without them ("throw" in DBIO::PostgreSQL::Diff::NoStatement). For rename, ALTER INDEX schema.old RENAME TO new;. For drop, returns DROP INDEX schema.name; -- schema-qualified, because an unqualified DROP INDEX looks only along the search path and reports an index in any other schema as not existing -- or, for the index of a constraint, ALTER TABLE schema.table DROP CONSTRAINT name;.
A definition from pg_get_indexdef is the server's own text and is written as given. Every name this method writes itself is an identifier by the server's rule ("quote_ident" in DBIO::PostgreSQL::Util).
A create without a definition dies, whether or not its index_info has columns: the columns are not the index (that it is unique, its predicate, its access method, the order and the operator class of each column stand in the definition only), and "diff" builds no such operation either ("throw" in DBIO::PostgreSQL::Diff::NoStatement). So does an action this class does not know; no placeholder is written.
summary
Returns a one-line description such as +index: idx_users_tags (on auth.users). A constraint -- created ("via") or dropped -- reads +constraint: users_pkey (on public.users), -constraint: ...; an index that is renamed ~index: old -> new (on public.users).
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.