NAME
DBIO::PostgreSQL::Diff - Compare two introspected PostgreSQL models
VERSION
version 0.900007
DESCRIPTION
DBIO::PostgreSQL::Diff compares two introspected PostgreSQL database models (as produced by DBIO::PostgreSQL::Introspect) and produces a list of structured diff operations. These operations can then be rendered to SQL or a human-readable summary.
my $diff = DBIO::PostgreSQL::Diff->new(
source => $current_model,
target => $desired_model,
);
if ($diff->has_changes) {
print $diff->as_sql;
print $diff->summary;
}
summary and as_sql give the operations in the same order, so review output and executable output stay aligned. That order is the subject of "ORDER OF OPERATIONS".
The argument extensions is the DBIO::Extension::Protocol of the schema's declared database extensions, which "diff" in DBIO::Deploy::Base passes when the schema declares any. The diff asks it about a change of a column's type and writes or refuses the change as it answers ("diff" in DBIO::PostgreSQL::Diff::Column). Without it nothing is asked.
METHODS
diff_options
my @names = DBIO::PostgreSQL::Diff->diff_options; # ('foreign_keys_not_valid')
The options this diff class takes in new beside source, target and extensions. "diff" in DBIO::Deploy::Base hands a caller's options on only when they are listed here.
register_diff_class
DBIO::PostgreSQL::Diff->register_diff_class(
model_key => 'spatial_refs',
class => 'DBIO::PostgreSQL::PostGIS::Diff::SpatialRef',
position => 'after:indexes',
);
Register a Diff class for a model key. Use position (after:KEY or before:KEY) to control ordering.
The position is a place in the order of dependency ("ORDER OF OPERATIONS"), so it holds for both halves: what the class creates or changes comes after the creates of KEY, what it removes comes before the removals of KEY. An operation is a removal when its action is drop; a class that drops an object only to create it again in the same diff gives that drop a true replaced, and it stays ahead of its create.
summary
One line per operation, then one line per foreign key kept because its referenced table is in neither model (=fk: ... -- kept (target outside the deploy)). Such a line is no operation: it has no statement, is not in operations and does not make has_changes true.
target_from_compiled
my $target = DBIO::PostgreSQL::Diff->target_from_compiled($compiled_model);
Translates the neutral model from DBIO::Schema::ModelCompiler into the PostgreSQL introspect-shaped model that diff consumes.
The compiled model uses native_type (as produced by DBIO::PostgreSQL::Adapter). This method translates that into the exact data_type strings that DBIO::PostgreSQL::Introspect::Columns returns (i.e. whatever pg_catalog.format_type reports), keys tables as "public.$table_name", and populates all top-level sections the Diff engine dereferences so that no spurious diffs arise from missing keys.
The model has no foreign_keys section: the compiled model carries no relationships, so it does not declare that there are none.
Auto-increment primary-key columns are mapped to identity = 'a' (GENERATED ALWAYS AS IDENTITY), which is the canonical DBIO convention for integer PKs in PostgreSQL.
Every entry of the compiled model becomes a table (kind => 'r'). The compiled model does not tell a view from a table -- a DBIO::ResultSource::View source arrives in it as one more table, without its definition -- so a schema with a deployed view cannot be diffed through this method: the view would be compared, and created, as a table. "diff" in DBIO::PostgreSQL::Deploy does not use it; its target is introspected from a temporary database, where a view is a view.
THE TWO MODELS
A column type, a default, the definition of an index, a trigger or a function are compared as the text the model has, and that text is what as_sql writes. PostgreSQL writes it for the session that asks, a name without its schema whenever that session's search_path finds it. So both models are read with "qualified_names" in DBIO::PostgreSQL::Introspect, which is on by default: then one object is one text in both, and a statement made of it reaches the objects it names in whatever session it is run.
Two models read with qualified_names => 0 are comparable only when they were read under the same search_path, and their statements run as meant only under that one.
ORDER OF OPERATIONS
The operations come in two halves, and the order within each follows what depends on what.
First everything that is created or changed, in the order of dependency: schemas, extensions, types, sequences, functions, tables, columns, what a sequence belongs to, views, indexes, foreign keys, triggers, policies. A schema comes before the extension that is installed into it -- age wants ag_catalog, postgis_topology wants topology, and CREATE EXTENSION uses a schema that is there instead of failing over it -- and an extension before the types, functions and tables that use what it brings. A sequence comes ahead of the tables and columns whose default calls it, and is given to its column (ALTER SEQUENCE ... OWNED BY) once that column is there; see DBIO::PostgreSQL::Diff::Sequence. A view comes after the tables it reads and after the columns that are added to them, and before the indexes of a materialized view and the triggers of a view. Within the tables, SET LOGGED and SET UNLOGGED go in the order of the foreign keys: going to unlogged the table that holds the key ahead of the one it references, going to logged the referenced one first, which is the only order PostgreSQL takes ("diff" in DBIO::PostgreSQL::Diff::Table).
Then everything that goes away, in the reverse order: policies, triggers, foreign keys, indexes, views, columns, tables, functions, types, extensions, schemas. No sequence is dropped by itself ("What is not done" in DBIO::PostgreSQL::Diff::Sequence), except one no column owns in a schema that is dropped; the one a column owns goes with the column or the table. What a sequence belongs to is changed in the first half, ahead of these drops, so a sequence that stays is not taken along. So a table is dropped before the schema it is in, a trigger before the function it calls, an index before its column, a view before the column and the table it reads, a column before its type, and whatever hangs on an extension before the extension.
The drops come after the creates and changes, not before, because a change is what takes the last user off an object: a column is altered to its new type before the old type is dropped, a trigger is pointed at the new function before the old one goes. DROP TYPE ahead of the ALTER would meet the column that is still of the type.
An operation counts as a removal when its action is drop. One kind of drop is none: the first half of a drop-and-recreate, as DBIO::PostgreSQL::Diff::Index, DBIO::PostgreSQL::Diff::Trigger and DBIO::PostgreSQL::Diff::View emit for a changed definition. Such an operation answers true to replaced and stays where its class put it, ahead of the create that follows.
Within one section the operations stay in the order their class returned them. That is by name, except for extensions, which DBIO::PostgreSQL::Diff::Extension orders by the dependencies between them, for views, which DBIO::PostgreSQL::Diff::View orders by the views they read, and for columns, where DBIO::PostgreSQL::Diff::Column puts a table behind the tables it has columns from.
What the order does not know is a dependency the model does not carry: a function that calls another, a function or a column default that reads a view, a new object that wants the name of one dropped in the same diff, a foreign key between a logged and an unlogged table. PostgreSQL refuses such a statement, apply dies on it, and the transaction it ran in is rolled back ("_execute_ddl" in DBIO::Deploy::Base).
A column whose type changes while a policy, a trigger (its WHEN or UPDATE OF), a generated column or a view outside the declared schemas reads it is not in that list: the model carries these dependencies (depends_on_columns of DBIO::PostgreSQL::Introspect::Policies, DBIO::PostgreSQL::Introspect::Triggers and DBIO::PostgreSQL::Introspect::Columns, and the outside_readers section, DBIO::PostgreSQL::Introspect::OutsideReaders), and the diff is refused ("WHAT IS REFUSED"). What a trigger's function or the body of any function reads is still not known.
Where the model carries the dependency and a plain order cannot satisfy it, the diff takes the views out of the way: a column that changes its type while a view reads it is altered between the DROP VIEW of every view that reads it (and of the views on those) and their CREATE VIEW, because PostgreSQL does not alter the type of a column a view uses ("diff" in DBIO::PostgreSQL::Diff::View). The drops of the views that are created again therefore stand ahead of the changes to tables and columns, not behind the columns. Only the target's views are created again; a view only the database has is refused, and so is a materialized view that reads the column or stands on a view that does ("WHAT IS REFUSED").
A column a partition has from the partitioned table, or a table from one it inherits from, is changed there and nowhere else: PostgreSQL carries the change down, and what is the table's own is changed after that ("Partitions and tables that inherit" in DBIO::PostgreSQL::Diff::Column).
An index or trigger on a partitioned table is the same: the server carries it down to every partition as a copy, and the copy is not in the model (DBIO::PostgreSQL::Introspect::Indexes, DBIO::PostgreSQL::Introspect::Triggers), so the diff writes a trigger, and the drop of an index, at the partitioned table alone and never one at a partition for a copy. An index or trigger a partition has of its own is in the model and is diffed.
The create of an index on a partitioned table is the exception: the text of the catalog, ON ONLY, makes an index that is invalid until every partition has an index attached, so the diff writes besides it a CREATE INDEX at each partition and ALTER INDEX ... ATTACH PARTITION ("diff" in DBIO::PostgreSQL::Diff::Index).
No drop is written with CASCADE: a schema, a table and a type are dropped the way PostgreSQL drops them by default (RESTRICT), which refuses while anything depends on the object. The order above means that by then everything the model knows in or on the object is gone already: a table's foreign keys and the ones that point at it, the views that read it, the types and functions of a schema, its tables and the sequences no column owns, the columns that are of a type. What a table owns outright -- its indexes, triggers, policies, its own constraints and the sequences its columns own -- goes with it and is not dropped by itself ("diff" in DBIO::PostgreSQL::Diff::Index). Two tables that reference each other lose their keys first, a table that inherits from another goes ahead of it, and a type goes ahead of the types built on it.
What depends on the object and the model does not hold is named before any statement is written: a view or materialized view, a foreign key, a partition or an inheriting table of a schema outside pg_schemas on a dropped table; a column, a routine or a domain there on a dropped type; a domain, operator, collation or the like in a dropped schema. The diff is refused with their names (DBIO::PostgreSQL::Diff::Dependents, read into the outside_dependents section by DBIO::PostgreSQL::Introspect::OutsideDependents). Without that section -- a model built by hand -- nothing is asked, and whatever the server refuses stops the statement: apply dies on it and its transaction is rolled back ("_execute_ddl" in DBIO::Deploy::Base). Where the diff would drop a schema that holds an extension it keeps, it is refused too: see "diff" in DBIO::PostgreSQL::Diff::Schema.
WHAT APPLY ASKS FOR
DBIO::PostgreSQL::Deploy inherits apply and upgrade from DBIO::Deploy::Base. Before the first statement the base reads this diff: every operation whose action is drop and that is not the first half of a drop and create (replaced) is destructive, and so is a column operation that answers true to lossy -- a change of type that may alter or lose a stored value ("lossy" in DBIO::PostgreSQL::Diff::Column). Such a diff runs with allow_destructive => 1, given to apply or upgrade or set on the deploy; without it the exception names each operation and no statement has run. dry_run => 1 executes nothing. A widening of smallint, integer, bigint or of the limit of a varchar runs without the release. See "apply" in DBIO::Deploy::Base.
FOREIGN KEYS
A foreign key is compared and written by DBIO::PostgreSQL::Diff::ForeignKey, after the indexes (the key it points at exists) and dropped before them. The target is what the install declared (pg_fk_constraints_from_rels), so:
a key the target has and the database lacks is created, also on a table the diff creates, with the name and the
ON DELETE/ON UPDATEof the install;a key with other columns, target or actions is dropped and created again under its name;
a key the schema does not declare is dropped, and the summary says
not declared by the schema, dropped. A target model without aforeign_keyssection (a model built by hand, "target_from_compiled") says nothing of them, and none is dropped;a key whose referenced table is in neither model (a schema outside
pg_schemas) is not the deploy's: it is neither compared nor dropped, and the summary names itkept (target outside the deploy). A table only one model has is inside the deploy, so a key into it is dropped as any other;the keys of a table that is dropped go with it.
The option foreign_keys_not_valid (set by "foreign_keys_not_valid" in DBIO::PostgreSQL::Deploy, or Diff->new(foreign_keys_not_valid => 1)) adds the key of an existing table NOT VALID and a VALIDATE CONSTRAINT after all of them. A key left NOT VALID (a step run alone, or an upgrade that stopped) is validated by the next diff, which reads convalidated; rows that break it fail that upgrade until they are cleaned up. A key the database holds NOT VALID that the target holds valid gets a VALIDATE CONSTRAINT, and MATCH FULL and the column list of ON DELETE SET NULL (columns) are compared like the actions. DEFERRABLE and INITIALLY DEFERRED of a foreign key and of a UNIQUE constraint are compared and written.
An index or constraint a foreign key stands on is still refused when it is to go while the key stays ("WHAT IS REFUSED"). The index drop of a changed index runs ahead of the drop of a foreign key; a layout this order cannot express is a migration by hand in two steps.
WHAT IS REFUSED
A change the diff cannot make without losing what the database holds, or without guessing at what the model does not carry, is not made at all. Building the operations dies with refusing to diff: ..., a message that names the object and the reason and says what can be done about it, and no statement is generated -- for nothing else in the diff either.
dropping a schema an extension the diff keeps is installed in ("diff" in DBIO::PostgreSQL::Diff::Schema)
dropping a table, a type or a schema that objects outside the model depend on -- the views, foreign keys, partitions and inheriting tables, the columns, routines and domains, the domains, operators and the like of DBIO::PostgreSQL::Introspect::OutsideDependents. The message names each of them (DBIO::PostgreSQL::Diff::Dependents)
a relation that is of another kind in the target -- a table where the target has a view, a view where it has a materialized view; creating a foreign table, a partitioned table, a partition or a table that inherits; a table that is a partition, or inherits, on one side only or of another table; a partitioned table or a materialized view that is unlogged on one side only ("diff" in DBIO::PostgreSQL::Diff::Table)
a materialized view with another definition, or one the target keeps on a view that has another definition ("diff" in DBIO::PostgreSQL::Diff::View)
a change to how an existing column is generated; a column that changes its type while a view that only the database has, or any materialized view, reads it or stands on a view that does; a column that changes from a composite type to one of other attributes; a column to add, to drop or to alter the type of at a partition, or at a table that inherits it, and not at the table it comes from ("diff" in DBIO::PostgreSQL::Diff::Column)
a change of a column's type that an extension of the schema refuses (a verdict
{ refuse => ... }); the message carries the refusal of every column and extension ("diff" in DBIO::PostgreSQL::Diff::Column)a column that changes its type while a policy, a trigger (its
WHENorUPDATE OF), a generated column, or a view or materialized view in a schema outside the declared ones reads it. The message names the column, the types and each object, and gives the statement that takes it out of the way (DROP POLICY,DROP TRIGGER,ALTER TABLE ... DROP COLUMN,DROP VIEW), after which the diff alters the column and creates again what the target has ("A column that changes its type while something else reads it" in DBIO::PostgreSQL::Diff::Column)an extension the database has in another schema than the one
pg_extensionsdeclares, when it is not relocatable ("diff" in DBIO::PostgreSQL::Diff::Extension)a function, a trigger, an index or a view to create, or to create again, that the target model has no definition for -- see "An object without a definition"
an index that stands behind a PRIMARY KEY, UNIQUE or EXCLUDE constraint and whose constraint cannot go with it (foreign keys of other tables point at it and stay, or it is dropped and added again), or a constraint to create that the diff cannot write (EXCLUDE; a key with an expression, a sort order or an operator class; a second primary key under another name; an index of the database that
ADD CONSTRAINT ... USING INDEXdoes not take: partial, not btree, not valid, on a partitioned table). The message names theALTER TABLEstatements that settle it ("diff" in DBIO::PostgreSQL::Diff::Index)an aggregate or a window function to create, or one that is another kind or has another definition in the target: the model holds no definition of one, because
pg_get_functiondefraises an error for it. One that is the same on both sides is no operation, and one only the database has is dropped (DROP AGGREGATEfor an aggregate). A function that is a procedure in the target, or the other way round, is not refused where the two signatures pair (they differ at most in the modeIN): it is dropped and created again ("diff" in DBIO::PostgreSQL::Diff::Function)a function that is a procedure in the target, or the other way round, whose signatures do not pair (an
INOUTargument, anOUTargument one side has, another argument name) while their input argument types are the same: PostgreSQL keeps one routine per name and input types, so the create would meet the old routine and the server would refuse it when the statements run. The message names both and theDROPto run by hand first ("diff" in DBIO::PostgreSQL::Diff::Function)a type both sides have that is of another kind in the target, an enum that has lost a value or has its values in another order, an enum or composite type the target declares empty (no values, no attributes) where the database has some (a drop would take the columns of the type with their data; a type the target does not declare at all is dropped), a composite type with other attributes (also the collation of an attribute), a range type with another subtype, collation, operator class, canonical or subtype_diff function. Only enum values added to the ones the database has are written, each at its place in the target's order (
BEFORE/AFTER) ("diff" in DBIO::PostgreSQL::Diff::Type)an object to create whose name differs only in upper and lower case from one to drop -- see "One name in two spellings"
a sequence of its own to create under the name the database holds as the sequence of an identity column, which is what an identity column made
serialcomes to; the message names the column and the way out,ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY("diff" in DBIO::PostgreSQL::Diff::Sequence)
An object without a definition
A function, a trigger, an index and a view are written as the text the catalog gives for them (pg_get_functiondef, pg_get_triggerdef, pg_get_indexdef, pg_get_viewdef), and compared by it. Where the target model has one of them without that text and the diff would have to write it -- because the database does not have it, or has it with another definition -- the diff is refused (DBIO::PostgreSQL::Diff::NoStatement), by "diff" in DBIO::PostgreSQL::Diff::Function, "diff" in DBIO::PostgreSQL::Diff::Trigger, "diff" in DBIO::PostgreSQL::Diff::Index and "diff" in DBIO::PostgreSQL::Diff::View. No comment and no placeholder is written in the place of the statement, and an index is not written from its columns: that would make a plain index of a unique or a partial one.
The catalog has no definition for a function, a trigger or an index that is gone by the time it is asked -- one another session dropped while the model was read ("When the catalog has no definition" in DBIO::PostgreSQL::Diff::NoStatement). The model then still has the object, under its name. Reading the model again is the way out. A view without a definition is in a model built by hand only: for one that is gone the reading of the model dies.
Only the target's definition is asked for. An object the diff drops needs none, and where only the model of the database lacks it the target's is written, as for any object whose definition differs.
The same holds for an operation nobody diffed: one built with new whose as_sql has no statement -- no definition, an action or a kind its class does not know, an alter with nothing to alter, a column to add without a data_type, a range type without a subtype -- dies there, and returns neither a comment nor an empty string ("throw" in DBIO::PostgreSQL::Diff::NoStatement).
One name in two spellings
PostgreSQL reads a name written without quotes in lower case, so a database may hold useraccount where the schema declares UserAccount -- one that was deployed before this distribution wrote every name exactly, or one whose tables somebody typed. The diff compares names as they are and finds a table to create and another to drop. It does not know whether the two are one table; if they are, DROP TABLE takes the rows.
So where an object to create and an object to drop have the same name but for upper and lower case, in the same place -- two tables of one schema, two columns of one table -- and dropping it loses something or cannot be done, the diff is refused. The rule is DBIO::PostgreSQL::Diff::CaseOnly's; the message names both spellings, for every such object of that kind, and the RENAME that makes the one into the other. The diff does not write the RENAME itself.
a schema -- the drop is preceded by that of everything in it ("diff" in DBIO::PostgreSQL::Diff::Schema). Schemas are looked at first, so a schema in another spelling is reported as that and not table by table.
a table, a foreign table, a partitioned table, a materialized view -- the drop takes the rows, or the data and the indexes ("diff" in DBIO::PostgreSQL::Diff::Table)
a column ("diff" in DBIO::PostgreSQL::Diff::Column)
a type -- a drop of the old type meets the columns that are still of it ("diff" in DBIO::PostgreSQL::Diff::Type)
an index that stands behind a PRIMARY KEY, UNIQUE or EXCLUDE constraint (the model says which, see "fetch" in DBIO::PostgreSQL::Introspect::Indexes) -- PostgreSQL does not drop it by
DROP INDEX, and dropping the constraint and creating another under the new spelling builds the index again, where a rename keeps it ("diff" in DBIO::PostgreSQL::Diff::Index)
For everything else such a pair is dropped and created like any other, which PostgreSQL 18 runs through and which loses nothing the model holds: a view (as with every view that is created again, the privileges granted on it and its comment are not in the model and go), an index that is not unique, a function, a trigger, a policy. An extension cannot be such a pair -- its name is the one its control file has, in any database.
A name both sides have is never part of a pair. A target with users and Users has two tables, and the diff creates the one that is missing.
SEE ALSO
DBIO::PostgreSQL::Deploy - orchestrates introspection and diff
DBIO::PostgreSQL::Introspect - produces the models being compared
DBIO::PostgreSQL::Diff::CaseOnly - the rule for a name in two spellings
DBIO::PostgreSQL::Diff::NoStatement - the rule for an operation without a statement
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.