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 SCHEMA when 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 public into the list where it is wanted), then the search_path entries of the extensions' activation (for example ag_catalog). Each schema stands once, at its first place.

  • The connect key schema wins: 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. Declaring public is a declaration.

  • The application's own statements win. on_connect_do and on_connect_call run after the declared path, so a SET search_path there 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 called auth, 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_catalog for age, topology for postgis_topology), else public; public when 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, public otherwise -- 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_namespace first, so a role without CREATE on the database can install into a namespace that is there) and sets the search_path itself, 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_ddl statement -- names its objects without a schema, like the tables, and reaches the namespace through the search_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::Pg

  • New: 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.