NAME
DBIO::PostgreSQL - PostgreSQL-specific schema management for DBIO
VERSION
version 0.900007
SYNOPSIS
Schema class
package MyApp::Schema;
use DBIO 'Schema';
__PACKAGE__->load_components('PostgreSQL');
__PACKAGE__->pg_schemas(qw( public auth ));
Or use the -pg shortcut, which pins +DBIO::PostgreSQL::Storage as the storage type directly:
package MyApp::Schema;
use DBIO Schema => -pg;
Result classes
package MyApp::Schema::Result::Artist;
use DBIO;
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(
id => { data_type => 'integer', is_auto_increment => 1 },
name => { data_type => 'varchar', size => 100 },
);
__PACKAGE__->set_primary_key('id');
For result classes with PostgreSQL-native features, use -pg to load DBIO::PostgreSQL::Result:
package MyApp::Schema::Result::User;
use DBIO -pg;
__PACKAGE__->pg_schema('auth');
__PACKAGE__->table('users');
__PACKAGE__->add_columns(
id => { data_type => 'uuid', default_value => \'gen_random_uuid()' },
name => { data_type => 'varchar', size => 100 },
tags => { data_type => 'text[]' },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->pg_index('idx_users_name' => { columns => ['name'] });
__PACKAGE__->pg_index('idx_users_tags' => { using => 'gin', columns => ['tags'] });
Connecting
my $schema = MyApp::Schema->connect($dsn, $user, $pass);
The classic use base form is still supported:
package MyApp::Schema;
use base 'DBIO::Schema';
__PACKAGE__->load_components('PostgreSQL');
This driver component may equally be named as a base class alongside extension components in a single use base, with DBIO::Schema last:
package MyApp::Schema;
use base qw( DBIO::PostgreSQL DBIO::PostgreSQL::Age DBIO::Schema );
The order of the components among themselves is free (this component before or after an extension component both linearise), as long as no component in the list inherits another one. An extension component that inherits a driver component makes the merge unsatisfiable: the driver would have to both precede and follow the extension, so one stacking order dies with Inconsistent hierarchy during C3 merge while the other still loads. That is a rule about how extension components are written, not a limit of this form; see ADR 0032 section 7 (addendum 2026-08-07) in the dbio distribution.
load_components does not expose that conflict: it de-duplicates through Class::C3::Componentised, so a driver component inherited by an extension is loaded once and the ordering never becomes contradictory. Stacking with use base has no such step. Both forms are in use across the family; the load_components one is the more forgiving of a mis-declared extension.
DESCRIPTION
DBIO::PostgreSQL is the PostgreSQL driver component for DBIO.
When this component is loaded into a schema class, connection() sets "storage_type" in DBIO::Schema to +DBIO::PostgreSQL::Storage, which enables PostgreSQL-specific storage behavior automatically.
This distribution also provides PostgreSQL-native DDL/deploy helpers and introspection/diff tooling.
METHODS
pg_schemas
Get or set the list of PostgreSQL schema names tracked by this schema class.
Declaring any restricts DBIO::PostgreSQL::Deploy to these schemas: its diff introspects only them, and -- extensions being database-level, outside every schema -- it creates declared extensions that are missing but drops none it does not declare. With no schema declared the deploy owns the whole database and drops every extension not declared via "pg_extensions". A restricted deploy built with manage_extensions_when_restricted => 1 drops them too ("manage_extensions_when_restricted" in DBIO::PostgreSQL::Deploy).
The names are kept, and answered, in the order they were declared; a name given twice stands once, at its first place. The install script creates the schemas and the types of their "pg_schema_class" bindings in that order.
Only this list restricts the deploy of the class. A "pg_schema_class" binding never does. A connection that names its namespace restricts the deploy made for it to that namespace, whatever this list holds ("ONE SCHEMA CLASS IN SEVERAL NAMESPACES").
Call it before "pg_schema_class": it croaks, naming the bound schemas, when a binding already exists. Setting it again while nothing is bound replaces the list; reading it is always allowed.
pg_schema_class
Get or set the class mapped to a specific PostgreSQL schema name.
__PACKAGE__->pg_schemas(qw( public auth ));
__PACKAGE__->pg_schema_class('auth' => 'MyApp::DB::PgSchema::Auth');
This binding is the one source of the namespace DBIO::PostgreSQL::DDL installs the PgSchema class's enums, types and functions into.
It does not restrict the deploy; only "pg_schemas" does. With a "pg_schemas" list declared, the name must be on it: a binding for another name croaks, naming it. With none declared, a bound class is installed, its namespace after the declared ones in name order, and the deploy stays unrestricted.
Call it after "pg_schemas".
pg_extensions
Get or set PostgreSQL extensions to include during deploy/DDL operations.
__PACKAGE__->pg_extensions('vector');
# an extension in a schema of its own
__PACKAGE__->pg_extensions('pgcrypto', vector => { schema => 'extensions' });
Declared extensions are emitted as CREATE EXTENSION IF NOT EXISTS before any schema or table DDL, so extension-backed types (e.g. vector(N) from pgvector) deploy into the temporary database used by test-deploy-and-compare. The extension must be installed on the server; DBIO::PostgreSQL::Deploy probes pg_available_extensions before deploying and fails with an actionable error naming the extension if it is not.
A name may be followed by a hashref with the keys schema and class:
schema-
The schema the extension is installed in (
CREATE EXTENSION ... WITH SCHEMA), a plain non-empty string. class-
The extension class (a DBIO::Extension) of an extension outside the family; it wins over the driver's table. See "Extension classes".
Any other key, an empty hashref, a second hashref for one name, a hashref without a name before it, a schema that is not a plain non-empty string and a class that is not a class name croak. The list in list context is the names only, in the order they were declared; the schemas are "pg_extension_schemas" and the classes "pg_extension_classes".
Extension classes
The declaration is the list of the extensions a schema has (ADR 0066 of DBIO core): an extension is not pulled in by loading a component, and a schema that does not declare age has no AGE rules, whatever is loaded in the process. The driver resolves each declared name to its class when the deploy needs the list:
A name the family has a class for --
age,postgis,postgis_topology-- gets it from the fixed table of DBIO::PostgreSQL::Extension. A distribution that is not installed refuses, naming the distribution.name => { class => 'My::Extension' }names the class of an extension outside the family, and wins over the table.Any other name (
citext,pgcrypto,vector, ...) gets DBIO::PostgreSQL::Extension, which has no rules: the extension is installed and kept, nothing else is done for it.
What a class may do, the addresses it names, and what a deploy does with an extension: "EXTENSIONS" in DBIO::PostgreSQL::Deploy.
A name without a hashref declares no schema, and every CREATE EXTENSION is written WITH SCHEMA all the same (unqualified it would land in the first schema of the session's search_path, ag_catalog after the activation of age). The schema is, in this order: the declared one; the one the extension's control file fixes (age in ag_catalog, postgis_topology in topology, postgis_tiger_geocoder in tiger, pg_cron and plpgsql in pg_catalog), which DBIO::PostgreSQL::Deploy reads from the server (pg_available_extension_versions) for the install, the diff and its temporary database, and so does deploy_async of the future_io transport (over a pooled connection, before the DDL is built) -- a declared schema other than the fixed one is refused, naming extension and both schemas, before any statement; public. For an extension with a fixed schema the statement carries no clause: the server installs it there whatever the search_path, and creates the schema. "pg_install_ddl" and "install_ddl" in DBIO::PostgreSQL::DDL called directly ask no server and do not know a control file: an extension with a fixed schema has to declare that schema on that way, or its WITH SCHEMA public is refused by the server. A deploy takes an installed extension for correct wherever the database has it. Only a declared schema is compared.
What a declared schema does:
The schema, unless it is
public, has to be declared by "pg_schemas"; otherwise the install script is refused, naming extension and schema. That holds without a list as well: a schema only the extension names is neither created nor guessed at. Declaring "pg_schemas" makes the deploy restricted -- it then drops no extension it does not declare, unless built with "manage_extensions_when_restricted" in DBIO::PostgreSQL::Deploy. The install creates the schema, ahead of the extension.An extension the database has in another schema is moved there by
ALTER EXTENSION ... SET SCHEMAwhen it is relocatable (pgcrypto,vector,pg_trgm,citext,hstore,uuid-ossp,btree_gist). Its types, functions and operators go along, and so do the columns and indexes that use them. A function body that names the old schema (public.gen_random_uuid()) is text for the server and is not rewritten: it breaks after the move and has to be written again.An extension that is not relocatable (
postgis,postgis_topology,age) is never moved: the diff is refused, naming the extension, both schemas and the way out, before a statement is written.
An extension present in the database but not declared here is dropped by a DBIO::PostgreSQL::Deploy diff only when the deploy is unrestricted; a deploy restricted by "pg_schemas" leaves it in place, unless it is built with "manage_extensions_when_restricted" in DBIO::PostgreSQL::Deploy.
pg_extension_schemas
my $schemas = __PACKAGE__->pg_extension_schemas; # { vector => 'extensions' }
The schemas "pg_extensions" declared, as a hashref extension name to schema name. Only the extensions that named one are in it. A copy: changing it declares nothing.
pg_extension_classes
my $classes = __PACKAGE__->pg_extension_classes; # { vector => 'MyApp::DB::Extension::Vector' }
The classes "pg_extensions" declared with the option class, as a hashref extension name to class name. Only the extensions that named one are in it; the others get the class of the driver's table or the plain class ("Extension classes"). A copy: changing it declares nothing.
pg_search_path
__PACKAGE__->pg_search_path(qw( auth public ));
Get or set the PostgreSQL search_path list the connections of the schema work with. Without a declaration the getter answers public and nothing is set on a connection.
A declared path is the search_path of every connection of the schema: the sync connection and each connection of an async pool (async => 'future_io') are sent SET search_path TO ..., by "_apply_connection_schema" in DBIO::PostgreSQL::Storage, ahead of on_connect_call and on_connect_do.
Order. The declared entries come first, in their order. Behind them stand the schemas the extensions of "pg_extensions" name with a schema (an extension that names none adds nothing: write
publicinto the list where it is wanted), then thesearch_pathentries of the extensions' activation (for exampleag_catalog). Each schema stands once, at its first place.The connect key
schemawins: such a connection has its namespace first, then the schemas of the extensions ("ONE SCHEMA CLASS IN SEVERAL NAMESPACES"), and this list is not read there (ADR 0008).Nothing declared: nothing is sent; the connection has the path the server, the role or the DSN (
options=-csearch_path=...) give it, plus the entries of an extension's activation. Declaringpublicis a declaration.The application's own statements win.
on_connect_doandon_connect_callrun after the declared path, so aSET search_paththere replaces it. The declared path replaces one the DSN gave.
The install script does not write the list: "install_ddl" in DBIO::PostgreSQL::DDL no longer closes with SET search_path TO ..., which changed the path of the session that ran it. A table or a view without a pg_schema is created in the first existing schema of the path the installing session has -- on a connection of this schema, the declared one.
The entries are schema names and are quoted where they need it, so MySchema stays MySchema; $user is written as the server wants it, "$user". Write a name without quotes: an entry already in double quotes is refused, naming the unquoted form (see "NAMES AND VALUES" in DBIO::PostgreSQL::DDL), when the install script is written and when a connection is set up.
pg_search_path_declared
if ($schema_class->pg_search_path_declared) { ... }
True when "pg_search_path" was given a list, public included; false for the default. The storage sets a path on a connection only for a declared one.
pg_settings
__PACKAGE__->pg_settings({
default_text_search_config => 'pg_catalog.german',
});
Get or set, as a hashref, the configuration parameters the install stores on the database.
"install_ddl" in DBIO::PostgreSQL::DDL writes one statement per entry that runs ALTER DATABASE ... SET key = 'value' for the database the script runs in -- a DO block that asks the server for the name (current_database()), since the script is written without one and also runs in the temporary database of "diff" in DBIO::PostgreSQL::Deploy. The value is written as one string literal. The key is a name, quoted where it needs it like an entry of "pg_search_path"; a dotted key is a dotted name, each part for itself, so app.current_user_id is written as it is and DateStyle as "DateStyle". Write a key without quotes: one with a part already in double quotes is refused, naming the unquoted form, and so is an empty key or one with an empty part (see "NAMES AND VALUES" in DBIO::PostgreSQL::DDL).
What that means on the server:
The setting is a default for new sessions of that database. The session that runs the install does not get it.
The role that installs has to be allowed to set the parameter on the database -- PostgreSQL asks for the owner of the database or a superuser, depending on the parameter. Otherwise the install fails with the server's error, as it does for a parameter the server does not know.
A value is one literal, so a parameter that takes a list gets one element:
search_path => 'auth, public'names a single schema calledauth, public.
Install only. The settings of a database are not part of the model "diff" in DBIO::PostgreSQL::Deploy compares, so an upgrade neither sets a value that changed in the declaration nor resets one that is no longer declared.
connection
Overrides "connection" in DBIO to force +DBIO::PostgreSQL::Storage as storage_type.
It also registers, with "register_storage_layer" in DBIO::Schema, the storage layer (ADR 0032) that the class of a declared extension names in its storage_layer (AGE: its storage with cypher and create_graph), ahead of the composition core makes in this method; core composes the async mirror of the layer over a pool transport. The classes of the list are loaded and checked for their interface version here, since the layer is chosen before the storage exists; the assertion of their driver waits for the first use of the connection. An extension chooses a layer, never the driver.
What the declared extensions want of every new connection -- LOAD of a library, schemas for the search_path -- is applied by the storage, for the sync connection and for each connection of an async pool ("_apply_connection_schema" in DBIO::PostgreSQL::Storage); the search_path is "connection_search_path" in DBIO::PostgreSQL::Util.
pg_deploy
my $deploy = $schema->pg_deploy;
my $deploy = $schema->pg_deploy(temp_db_prefix => '_myapp_tmp_');
Returns a deploy object for the schema: an instance of the class the schema's storage names in "dbio_deploy_class" in DBIO::PostgreSQL::Storage. That is DBIO::PostgreSQL::Deploy -- the class $schema->deploy installs through. The arguments go to its constructor beside schema.
A schema that has no storage yet, and one whose storage names no deploy class (DBIO::Replicated), gets the class DBIO::PostgreSQL::Storage names.
pg_install_ddl
Generates PostgreSQL-native DDL statements for the schema. Called on a schema connected with the connect key schema, it is the script of that connection's namespace ("A CONNECTION THAT NAMES ITS NAMESPACE" in DBIO::PostgreSQL::DDL).
ONE SCHEMA CLASS IN SEVERAL NAMESPACES
A schema class that names no namespace -- no "pg_schema_class", no pg_schema in a result class, table names without a schema -- is installed into, and read from, whichever namespace a connection names with the connect key schema:
my $tenant_a = MyApp::Schema->connect($dsn, $user, $pass, { schema => 'tenant_a' });
my $tenant_b = MyApp::Schema->connect($dsn, $user, $pass, { schema => 'tenant_b' });
$tenant_a->pg_deploy->install; # creates tenant_a, installs into it
$tenant_b->pg_deploy->install; # the same classes, in tenant_b
$tenant_a->resultset('User')->all; # tenant_a.users
The key is one name, and it is the connection's: nothing of it is kept in the class, so the same class is connected for any number of namespaces at once. Every connection of such a schema has the namespace first in its search_path -- the sync connection and each connection of an async pool (async => 'future_io') -- set before on_connect_call and on_connect_do run ("_set_connection_schema" in DBIO::PostgreSQL::Storage).
The search path is the namespace, then the schemas of the extensions "pg_extensions" declares, each once, in the order of the extensions: for an extension that names no schema the one its control file fixes (the server answers:
ag_catalogfor age,topologyfor postgis_topology), elsepublic;publicwhen no extension is declared. So the types, functions and operators of the extensions are found without their schema. "pg_search_path" is not read on such a connection (a connection without the key has it as its path).Extensions belong to the database, not to a namespace. The install creates each
WITH SCHEMA-- the schema it names, else the one its control file fixes,publicotherwise -- and refuses one whose schema is the namespace of the connection.The install creates the namespace when it is not there (looked up in
pg_namespacefirst, so a role withoutCREATEon the database can install into a namespace that is there) and sets thesearch_pathitself, ahead of the first object. See "A CONNECTION THAT NAMES ITS NAMESPACE" in DBIO::PostgreSQL::DDL.The deploy of such a connection is restricted to its namespace: its diff compares that namespace alone, and drops no extension. See DBIO::PostgreSQL::Deploy.
What a declaration writes as SQL -- a view's definition, a default, a
pg_extra_ddlstatement -- names its objects without a schema, like the tables, and reaches the namespace through thesearch_path.
A schema class that manages several namespaces of its own stays with "pg_schemas" and "pg_schema_class" and is connected without the key: on a connection that names its namespace, the install refuses a "pg_schema_class" binding, a result class with a pg_schema and a table name that carries its schema.
Types and functions on such a connection
Declare them without a schema; they land in the namespace like the tables, through the search_path the install script sets. Two declarations do that today: an enum column with extra => { list => [...] } (its type {table}_{column}_enum is created, and used, unqualified) and raw SQL in "pg_extra_ddl" in DBIO::PostgreSQL::Result (written as given, after the table; a function there lands in the namespace, a type only for what comes after the table). DBIO::PostgreSQL::PgSchema (pg_enum, pg_type, pg_function) is reached only through a "pg_schema_class" binding, which such a connection refuses, so there is no schema-level declaration of enums, composite types or functions for a connection with the key. The SQL of a function is written as given and not read: a body or a CREATE FUNCTION other.f that names a schema reaches that schema, not the namespace.
PGVECTOR
pgvector support covers declaring vector columns, querying them by distance, indexing them, and converting their values. The extension must be installed on the server and declared via "pg_extensions":
__PACKAGE__->pg_extensions('vector');
CREATE EXTENSION IF NOT EXISTS vector is emitted before any schema or table DDL, so vector(N) columns deploy into the temporary database used by test-deploy-and-compare. DBIO::PostgreSQL::Deploy probes pg_available_extensions first and fails with an actionable error naming the extension if it is missing.
Declaring columns
__PACKAGE__->add_columns(
embedding => { data_type => 'vector(3)' }, # dense vector
half => { data_type => 'halfvec(3)' }, # half-precision dense
sparse => { data_type => 'sparsevec(1000)' }, # sparse vector
);
vector(N) and halfvec(N) are dense vectors; sparsevec(N) is sparse. Declaring a pgvector column promotes the source's resultset_class to DBIO::PostgreSQL::ResultSet (so nearest_neighbors is available) and enables value inflation/deflation (see "Value handling").
Querying
Distance queries go through the vector() DSL from DBIO::PostgreSQL::Vector:
use DBIO::PostgreSQL::Vector qw(vector);
# KNN — nearest neighbors by cosine distance
$rs->search(
{},
{
order_by => vector('me.embedding')->cosine_distance([0.1, 0.2])->as_order,
rows => 5,
},
);
# Distance range filter
$rs->search( vector('me.embedding')->l2_distance($v)->lt(5) );
The query vector travels as a bind placeholder cast to the column's type (?::vector by default, or ?::sparsevec automatically for a sparsevec hashref query vector; cast => 'halfvec' overrides for halfvec/bit columns). For the common KNN shape, use "nearest_neighbors" in DBIO::PostgreSQL::ResultSet:
$rs->nearest_neighbors(
embedding => [0.1, 0.2, 0.3],
distance => 'cosine',
rows => 10,
);
Indexing
hnsw has no default operator class for vector, so an hnsw index on a vector column must name one:
__PACKAGE__->pg_index('idx_items_embedding' => {
using => 'hnsw',
columns => ['embedding'],
opclasses => { embedding => 'vector_cosine_ops' },
});
ivfflat does have a default for vector, vector_l2_ops, and uses it when no operator class is named; any other distance has to be named there too.
vector_l2_ops serves <->, vector_cosine_ops serves <=>, vector_ip_ops serves <#> (with halfvec_* / sparsevec_* / bit_* families for the other column types). hnsw and ivfflat support vector columns up to 2000 dimensions. Ordering by an operator no index covers is not an error — PostgreSQL falls back to a sequential scan; EXPLAIN is the only real confirmation that an index serves a query.
Value handling
pgvector columns inflate/deflate by default: an ArrayRef deflates to [0.1,0.2] and inflates back on fetch; a sparsevec HashRef deflates to {i:v,...}/N and inflates to { dims => N, values => {...} }. The conversion is idempotent — a plain string passes through verbatim. Opt out per column with inflate_pgvector => 0. See DBIO::PostgreSQL::InflateColumn::Vector.
MIGRATION NOTES
The PostgreSQL storage class was split out of the historical DBIx::Class monolithic distribution:
Old:
DBIx::Class::Storage::DBI::PgNew:
DBIO::PostgreSQL::Storage
A schema declares the driver with this component or use DBIO 'Schema', -pg; core picks no storage class from the DSN (core ADR 0067).
TESTING
Integration tests in this distribution use:
DBIO_TEST_PG_DSN
DBIO_TEST_PG_USER
DBIO_TEST_PG_PASS
SQLMaker-focused tests can run offline via DBIO::Test with:
storage_type => 'DBIO::PostgreSQL::Storage'
Replicated-path tests can reuse the same harness with:
replicated => 1,
storage_type => 'DBIO::PostgreSQL::Storage'
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.