NAME
DBIO::PostgreSQL::Introspect - Introspect a PostgreSQL database via pg_catalog
VERSION
version 0.900007
DESCRIPTION
DBIO::PostgreSQL::Introspect reads the live state of a PostgreSQL database via pg_catalog and returns a unified model hashref. It is the source side of the test-deploy-and-compare strategy used by DBIO::PostgreSQL::Deploy.
my $intro = DBIO::PostgreSQL::Introspect->new(
dbh => $dbh,
schema_filter => [qw( public auth api )],
);
my $model = $intro->model;
# $model->{schemas}, $model->{tables}, $model->{columns}, ...
The model is built lazily on first access and covers schemas, extensions, types (enums/composites/ranges), tables, columns, indexes, triggers, functions, RLS policies, and sequences, and the views outside the schema filter that read a column inside it (outside_readers, DBIO::PostgreSQL::Introspect::OutsideReaders), and what stands outside the filter on a table, a type or a schema of the model (outside_dependents, DBIO::PostgreSQL::Introspect::OutsideDependents). What an installed extension owns is not in it, only the extension itself: see "extension_member_filter". Nor is a function PostgreSQL made as part of another object, such as the constructors of a range type: see "internal_part_filter". The same model shape is consumed by DBIO::PostgreSQL::Diff and by the test-deploy workflow in DBIO::PostgreSQL::Deploy. On top of it, this class implements the normalized generation contract from DBIO::Introspect::Base so it can act as a DBIO::Generate source.
ATTRIBUTES
schema_filter
Optional ArrayRef of PostgreSQL schema names to restrict introspection to. When undef, all non-system schemas are introspected.
qualified_names
my $intro = DBIO::PostgreSQL::Introspect->new(
dbh => $dbh,
qualified_names => 1,
);
Whether the whole model is read under an empty search_path ("under_empty_search_path"). Default: true.
PostgreSQL does not keep the text of a definition, it writes it for whoever asks, and writes a name without its schema whenever the search_path of the asking session finds the object. That goes for every text of the model: a column type, a composite attribute, a range's subtype and a function's return type (format_type), a default, an index predicate and expression, a policy (pg_get_expr), and the definitions of an index, a trigger, a function and a CHECK constraint, down to the arguments a function is filed under (app.label(s status) or app.label(s app.status)) and the table a sequence belongs to. So read under the session's own search_path, the model of one database is a different text in two sessions, and a text of it means something else, or nothing, when it is run in a session with another search_path.
With it, every name outside pg_catalog comes with its schema, one in public too: public.mood, public.vector(3), nextval('public.thing_id_seq'::regclass), EXECUTE FUNCTION public.touch(). The model is then the same text whoever asks, and each of its texts names the same objects wherever it is run. The diff compares these texts and runs them; DBIO::PostgreSQL::Deploy reads both of its databases this way, and so does DBIO::Generate (DBIO::PostgreSQL::Introspect::Normalize takes public.vector(3) apart as it did vector(3)).
A false value reads under the session's own search_path: the names are as that session finds them, and the model is comparable only with one read under the same path. The one text that does not follow the attribute is view_definition, which is read under an empty search_path in every model (DBIO::PostgreSQL::Introspect::Tables).
preserve_case
Whether the methods of the "NORMALIZED CONTRACT" return column names in the case the catalog has them. False by default: they are lowercased ("name" in DBIO::PostgreSQL::Introspect::Normalize). The native model keeps every name as the catalog has it either way.
METHODS
table_keys
Returns sorted ArrayRef of schema-qualified table keys (schema.name).
identity_kind
my $kind = DBIO::PostgreSQL::Introspect->identity_kind($id);
Maps the attidentity opcode from pg_catalog.pg_attribute to the SQL keyword that follows GENERATED in CREATE TABLE: 'a' becomes 'ALWAYS', 'd' becomes 'BY DEFAULT', anything else (including undef and '') returns undef. The same map is needed by every site that emits identity syntax (Diff::Column, Diff::Table, DDL).
generated_kind
my $kind = DBIO::PostgreSQL::Introspect->generated_kind($generated);
Maps the attgenerated mark from pg_catalog.pg_attribute to the key word that closes GENERATED ALWAYS AS (...): 's' becomes 'STORED', 'v' (PostgreSQL 18) becomes 'VIRTUAL'; undef and '' -- a column that is not generated -- return undef.
Any other mark dies. For a generated column the catalog keeps the expression where it keeps a default, so a mark that is passed over turns the column into an ordinary one with that expression as its DEFAULT.
qualified_key
my $key = DBIO::PostgreSQL::Introspect->qualified_key($schema, $name);
my $key = DBIO::PostgreSQL::Introspect->qualified_key($row);
Builds the canonical "schema.name" key used to index introspected models. Accepts either two positional args ($schema, $name) or a single hashref that contains both schema_name and one of table_name, type_name, function_name, sequence_name, index_name, or policy_name. Centralising the construction here keeps every section key in lockstep — if the separator ever changes, only this helper moves.
key_names
my ($schema, $name) = DBIO::PostgreSQL::Introspect->key_names(
$key, table_name => @entries,
);
The way back from a "qualified_key": the names the key stands for, the schema first.
A key is where a model files an object, not what the object is called. It cannot be taken apart again -- my.schema.tbl is as much the table tbl in the schema my.schema as the table schema.tbl in the schema my -- so the names come from an entry that carries them: the first of @entries that is a hashref with a schema_name and the field named second (table_name, type_name, ...). Every entry this introspector files has both, and what it files under a table's key (an index, a trigger, a policy) names that table. Entries that are not hashrefs, or lack either name, are passed over.
Only when no entry names the object is the key split, at its first dot. That is right for every key whose schema name has no dot, and is what a model built by hand without the names gets. A key without any dot is returned as the one name it is.
under_empty_search_path
my $rows = DBIO::PostgreSQL::Introspect->under_empty_search_path($dbh, sub {
$dbh->selectall_arrayref($sql);
});
Runs the code with the search_path of $dbh empty and returns what it returns (one value). Under an empty search_path PostgreSQL writes every name outside pg_catalog with its schema -- pg_dump reads definitions the same way. This is the one place that says how: "qualified_names" runs the whole model through it, DBIO::PostgreSQL::Introspect::Tables the view definitions of a model read without it.
The session is afterwards what it was, whether the code returns or dies. The search_path is not set and set back: it is set for a transaction that is then rolled back, and goes with it.
Outside a transaction, one is opened for the code and rolled back.
Inside a transaction of the caller's --
AutoCommitoff,begin_work, or aBEGINsent as a statement -- it is a savepoint that is rolled back to and released. The caller's transaction stays open with everything it did, also when the code dies: the error is the caller's to handle, the transaction is not aborted by it. ASET LOCAL search_pathof the caller's is in force again as well, and still ends with the transaction (setting the value back would have made it the session's).
The code is meant to read. What it changes in the database is rolled back with the rest.
A call inside the code of another, for the same handle, runs its code where it stands. RaiseError is on for the handle while the code runs.
system_schema_filter
my $sql = DBIO::PostgreSQL::Introspect->system_schema_filter($alias);
Returns a two-line SQL fragment for the pg_catalog namespace filter that excludes the PostgreSQL reserved namespaces (pg_* and information_schema). The optional $alias is the alias used in the query for the pg_namespace row (defaults to n). Every introspect helper concatenates this fragment into its WHERE clause so the set of excluded namespaces is centralised.
extension_member_filter
my $sql = DBIO::PostgreSQL::Introspect->extension_member_filter('pg_class', 'c.oid');
Returns a SQL fragment for a WHERE clause that leaves out what an installed extension owns: the rows whose object has a pg_depend entry of deptype e. $catalog is the pg_catalog table the object lives in (pg_class, pg_type, pg_proc, pg_namespace), $oid the SQL expression for its oid in the query.
CREATE EXTENSION / DROP EXTENSION own such objects, so they are not the schema's to create or drop one by one. Every introspect helper that reads a kind of object an extension can own appends this fragment after "system_schema_filter". The helpers that read what hangs on a relation (columns, indexes, constraints, triggers, policies) append it for the relation's oid: none of those is an extension member in its own right, and they leave the model together with the relation. So does the sequence behind a serial or identity column (DBIO::PostgreSQL::Introspect::Sequences), for which the fragment is appended a second time, for the oid of the table the sequence belongs to.
internal_part_filter
my $sql = DBIO::PostgreSQL::Introspect->internal_part_filter('pg_proc', 'p.oid');
Returns a SQL fragment for a WHERE clause that leaves out what PostgreSQL made as part of another object: the rows whose object has a pg_depend entry of deptype i (internal). $catalog and $oid are as for "extension_member_filter".
PostgreSQL's own words for that dependency: the object "was created as part of creation of the referenced object, and is really just a part of its internal implementation. A direct DROP of the dependent object will be disallowed outright". Such an object is not the schema's to create or drop: it comes and goes with the object it is part of. The constructor functions of a range type are the case this is here for -- CREATE TYPE ... AS RANGE makes them, for the range and for its multirange type, DROP TYPE takes them, and DROP FUNCTION on one fails with cannot drop function ... because type ... requires it.
DBIO::PostgreSQL::Introspect::Functions appends it. It is not a rule for every section: an index that carries a PRIMARY KEY or UNIQUE constraint is an internal part of that constraint by the same deptype, and stays in the model as the only trace of the constraint the diff has.
column_reads
my $reads = DBIO::PostgreSQL::Introspect->column_reads(
$dbh, $filter, 'pg_policy', 'polrelid', 'polname',
);
# { 'public.t' => { p => { 'public.t' => [ 'v' ] } } }
The columns an object that hangs on a relation reads, from the pg_depend rows PostgreSQL keeps for it (the column is refobjsubid): no text is taken apart. $catalog is the object's catalog (pg_policy, pg_trigger), $relation its column for the relation it is on, $name its name column, and an optional $where a further condition on the object o. The result is keyed by the key of the relation the object is on, then by the object's name, and gives for each the relations it reads, by key, with the column names, sorted. An object that reads no column has no entry. $filter restricts the relation the object is on, as for every section.
DBIO::PostgreSQL::Introspect::Policies and DBIO::PostgreSQL::Introspect::Triggers use it, for depends_on_columns.
normalize_array
my $list = DBIO::PostgreSQL::Introspect->normalize_array($value);
A PostgreSQL array as a Perl ArrayRef, in whichever of its two forms the handle returned it: "array" in DBIO::PostgreSQL::Introspect::Normalize. The section classes call it for the arrays their queries return.
table_columns
my \@names = $intro->table_columns($key);
Ordered list of column names for $key.
table_columns_info
my \%info = %{ $intro->table_columns_info($key) };
Hashref of column metadata.
table_pk_info
my \@pk_cols = @{ $intro->table_pk_info($key) };
Ordered list of primary key column names.
table_uniq_info
my \@constraints = @{ $intro->table_uniq_info($key) };
List of [ $constraint_name, \@col_names ] pairs.
table_fk_info
my \@fks = @{ $intro->table_fk_info($key) };
Each FK is a hashref.
table_is_view
Returns true if $key is a view.
view_definition
SQL text of the view definition, or undef.
table_comment
Comment string for the table, or undef.
column_comment
Comment string for a column, or undef.
result_class_extra_statements
my @stmts = $intro->result_class_extra_statements($key);
Driver-specific PostgreSQL statements for the generated Result class. Emits pg_schema, pg_index, pg_trigger, pg_rls, pg_check_constraint.
NORMALIZED CONTRACT
The generation contract defined in DBIO::Introspect::Base is implemented by "table_keys", "table_columns", "table_columns_info", "table_pk_info", "table_uniq_info", "table_fk_info", "table_is_view", "view_definition", "table_comment", "column_comment" and "result_class_extra_statements". They build on the native model.
The other methods are class methods the native model is built and read with: the section classes under DBIO::PostgreSQL::Introspect::, DBIO::PostgreSQL::Diff and DBIO::PostgreSQL::DDL call them.
SEE ALSO
DBIO::PostgreSQL::Deploy, DBIO::PostgreSQL::Diff.
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.