NAME

DBIO::PostgreSQL::Storage - PostgreSQL storage layer for DBIO

VERSION

version 0.900007

DESCRIPTION

DBIO::PostgreSQL::Storage is the DBI storage class for PostgreSQL. It extends DBIO::Storage::DBI with PostgreSQL-specific behaviour:

  • INSERT ... RETURNING for efficient last-insert-id retrieval (PostgreSQL 8.2+).

  • Atomic upsert ("upsert" in DBIO::ResultSet) via INSERT ... ON CONFLICT (PostgreSQL 9.5+), rendered by DBIO::PostgreSQL::SQLMaker. The supports_upsert capability is version-gated and additionally requires INSERT ... RETURNING to be in use, because that is what proves the returned row is the row that was written.

  • The conditional conflict branch, $rs->upsert(..., { update_where => ... }) rendered as DO UPDATE ... WHERE — the last-write-wins guard. Its own supports_upsert_where capability is declared, not derived from supports_upsert (ADR 0036 section 3): it is a plain fact about the conditional branch itself, and PostgreSQL has had the WHERE since 9.5, together with the clause it hangs off. Core probes supports_upsert first and throws there, so the declaration never sends a conditional branch to a driver whose conflict clause it cannot render.

  • Boolean-valued EXISTS queries ("results_exist" in DBIO::ResultSet and its two siblings) via the supports_exists_query capability, core ADR 0037. This driver declares the capability and does not override "_exists_value_sql" in DBIO::Storage::DBI: PostgreSQL documents both things the base rendering SELECT CASE WHEN EXISTS <inner> THEN 1 ELSE 0 END needs -- EXISTS yielding a boolean value, and a SELECT with no FROM. Core defaults the capability to 0 and all three methods throw without it, so the declaration is what makes them work here; it is not a formality.

  • Query plans ("explain" in DBIO::ResultSet) without ANALYZE. This driver takes core's base "_explain_query" in DBIO::Storage::DBI unchanged, so $rs->explain issues a plain EXPLAIN and returns PostgreSQL's estimated plan as rows. See "Query plans are planned, not run" for why ANALYZE is not the default and how to ask for it anyway.

  • Transactional DDL. PostgreSQL rolls CREATE TABLE and friends back with the transaction around them, so this class declares the transactional_ddl capability and DBIO::Deploy::Base brackets the DDL of an install or upgrade in $storage->txn_do: a failure part way through takes the earlier statements with it instead of leaving the schema half migrated. Many engines cannot do this — MySQL, MariaDB, Oracle, Sybase and Informix force an implicit COMMIT on DDL — which is why core defaults the capability to off and each driver states its own answer. Not a promise of one transaction per batch, though: the one statement this driver emits that PostgreSQL cannot run usefully inside a transaction, ALTER TYPE ... ADD VALUE, is taken out of the bracket by "_ddl_statement_is_transactional", and a batch containing one is split around it rather than being atomic as a whole.

  • Deferred foreign key checks via SET CONSTRAINTS ALL DEFERRED.

  • Sequence lookup via pg_catalog when RETURNING is not available.

  • BYTEA column binding through DBD::Pg native type constants.

  • Native savepoint support via pg_savepoint, pg_release, and pg_rollback_to.

  • Replication status for DBIO::Replicated::Pool: "is_replicating" and "lag_behind_master" answer for a streaming hot standby, so the pool takes a standby out that has lost its primary, was promoted, or is further behind than maximum_lag (PostgreSQL 10+).

  • Deployment through DBIO::PostgreSQL::Deploy and DBIO::PostgreSQL::DDL. This class names the deploy class in dbio_deploy_class, and $schema->deploy and $storage->deploy install through it whether or not the schema has the DBIO::PostgreSQL component loaded. See "Installing a schema".

  • The connect key schema: MyApp::Schema->connect($dsn, $user, $pass, { schema => 'tenant_a' }) makes tenant_a the namespace every connection of the schema works in, by its search_path ("_set_connection_schema"). One schema class serves any number of namespaces; see "ONE SCHEMA CLASS IN SEVERAL NAMESPACES" in DBIO::PostgreSQL.

  • Opt-in async via MyApp::Schema->connect($dsn, $user, $pass, { async => 'ev' }). The ev mode resolves (core ADR 0030 mode registry) to the native EV backend from the optional DBIO::PostgreSQL::EV dist, loaded lazily on first use. Without that dist installed, requesting the mode croaks with a clear install message — there is no silent fallback or degrade.

This class is registered as the driver for the Pg DBD and is selected automatically when DBIO::PostgreSQL is loaded into a schema class. Schema-introspection and migration planning live in DBIO::PostgreSQL::Introspect, DBIO::PostgreSQL::Diff, and DBIO::PostgreSQL::Deploy; this class stays focused on the live DBI storage behaviour.

Query plans are planned, not run

"explain" in DBIO::ResultSet asks this storage for a plan through core's "_explain_query" in DBIO::Storage::DBI seam (core ADR 0040), and this driver does not override it. What you get is core's base operation, which is already the correct one for PostgreSQL:

EXPLAIN <the resultset's SELECT>

-- the planner's estimated plan (node types, cost estimates, estimated row counts), returned as an arrayref of row arrayrefs, one row per line of PostgreSQL's plan output. The query itself is not executed.

Earlier releases of this driver spelled the hook EXPLAIN ANALYZE. That is gone on purpose: explain must not have side effects. On PostgreSQL, ANALYZE does not merely add real timings to the output -- it runs the query. That costs the query's full runtime, and it executes everything the query touches: nextval() and other volatile functions advance, a data- modifying CTE writes, triggers fire. A caller asking what the planner intends to do does not expect its sequence counters to move, so real timings have to be asked for rather than handed out unasked.

There is no option for it, because the seam is a method, not a setting. A caller who genuinely wants measured runtimes overrides the hook on a storage subclass and connects through it:

package MyApp::Storage::PgAnalyze;
use base 'DBIO::PostgreSQL::Storage';

# WARNING: this EXECUTES the query being explained.
sub _explain_query {
  my ($self, $sql, $bind) = @_;
  my (undef, $sth) = $self->_execute_rendered("EXPLAIN ANALYZE $sql", $bind);
  return $sth->fetchall_arrayref;
}

MyApp::Schema->connect($dsn, $user, $pass,
  { storage_type => 'MyApp::Storage::PgAnalyze' });

Note _execute_rendered: core's contract requires every statement an override issues to go through the storage's statement path, never $self->dbh. That is what keeps tracing and therefore redact_bind_value (core ADR 0025), dbh_do retry over a dropped connection, and -- on a DBIO::Replicated::Storage -- the read handler rather than the master's.

Extensions and the async backends see the capability declarations

Neither the PostgreSQL extension dists (dbio-postgresql-age, dbio-postgresql-postgis) nor the async backends need to repeat a capability this class declares, and for two different reasons -- which is worth stating, because "the subclasses inherit it" is true of neither: neither is a subclass.

An extension is a storage layer, composed by DBIO::Storage::Composed into a class whose @ISA is (@layers, DBIO::PostgreSQL::Storage). The capability accessors are inherited-type class data, resolved along the linearised @ISA, so the composed class answers with this class's declaration -- as a class method, and without the layer knowing the capability exists.

The async backends -- DBIO::PostgreSQL::Storage::Async (the future_io transport) and DBIO::PostgreSQL::EV::Storage (the ev backend) -- descend from DBIO::Storage::Async, not from this class, and therefore have no capability accessors at all. They do not need them: async is a per-connection mode on this storage (ADR 0030), so "results_exist_async" in DBIO::ResultSet probes the capability and renders the statement here, and hands the backend only the finished SQL for its _query_async seam. One declaration, one engine fact, every transport.

Installing a schema

This class has no deploy and no deployment_statements of its own. Both are DBIO::Storage::DBI's (core ADR 0064).

$storage->deploy($schema) connects and installs the schema through the class "dbio_deploy_class" names: $class->new(schema => $schema)->install, which is what $schema->deploy does. The two end in the same install, whether or not the schema has the DBIO::PostgreSQL component loaded. A DBIO::Replicated schema arrives there as well: its storage hands deploy to the storage of its master. The DDL is generated from the schema, so $type, $sqltargs and $dir are not read, and a DDL file in the deploy directory is not executed.

$storage->deployment_statements($schema, $type, $version, $dir) and $schema->deployment_statements generate no SQL. They return the contents of a DDL file that is in $dir already -- named by "sqlt_type" where no $type is given -- and throw when there is none, for a schema with the component as for one without. Neither asks the server anything. The install DDL of a schema is what "pg_install_ddl" in DBIO::PostgreSQL and "install_ddl" in DBIO::PostgreSQL::DDL return.

METHODS

dbio_deploy_class

my $class = $storage->dbio_deploy_class;   # 'DBIO::PostgreSQL::Deploy'

Names this driver's native deploy class, DBIO::PostgreSQL::Deploy, and is the one place that does. Core asks for it: "deploy" in DBIO::Schema and "deploy" in DBIO::Storage::DBI load the class and call $class->new(schema => $schema)->install, and DBIO::DeploymentHandler::DeployMethod::Native hands its deploy, upgrade and diff to an object of it. All three do so whether or not the schema has the DBIO::PostgreSQL component loaded. "pg_deploy" in DBIO::PostgreSQL returns an object of the class named here. Only the name is given; the class is loaded when it is first used.

The driver storage is its one owner. An extension does not name a deploy class of its own: its rules reach the deploy through the list of extensions the schema declares ("EXTENSIONS" in DBIO::PostgreSQL::Deploy).

sql_maker

my $sql_maker = $storage->sql_maker;

The SQL maker of "sql_maker" in DBIO::Storage::DBI, a DBIO::PostgreSQL::SQLMaker, as the core builds it. Identifiers are quoted with " and separated by . unless the connection says otherwise: nothing given quotes, quote_names => 0 alone writes them unquoted, and so does an empty quote_char alone. An explicit quote_char or name_sep is used as given.

Quoting that was turned off stays off; PostgreSQL then folds an unquoted name to lower case. The future_io transport of the same connection takes its quote_char and name_sep from this maker ("_sql_maker_args" in DBIO::PostgreSQL::Storage::Async), so both paths quote alike.

_ddl_statement_is_transactional

my $bool = $storage->_ddl_statement_is_transactional($ddl_statement);

The per-statement half of the transactional_ddl capability (ADR 0026 addendum). False for ALTER TYPE ... ADD VALUE, which PostgreSQL cannot run usefully inside a transaction on any supported server version, and for ALTER TABLE ... VALIDATE CONSTRAINT, which is transactional but is kept out of the bracket on purpose: it runs after the COMMIT of the ADD CONSTRAINT ... NOT VALID before it, so the lock of the add is not held over the reading of the rows and a failed validation leaves the key NOT VALID. True for everything else this driver emits. "_execute_ddl" in DBIO::Deploy::Base groups a DDL batch by this answer, so these statements run on their own while the rest of the diff keeps its txn_do. Core warns once that the batch was split; that is expected here.

_ddl_sql_dialect

my $dialect = $storage->_ddl_sql_dialect;    # 'postgresql'

Names the SQL dialect this driver's DDL is written in, for the splitter "_execute_ddl" in DBIO::Deploy::Base cuts install and upgrade DDL with ("_ddl_sql_dialect" in DBIO::Storage::DBI::Capabilities). Declared, the splitter reads what only PostgreSQL has: the body of CREATE FUNCTION ... BEGIN ATOMIC ... END and of a CREATE RULE ... DO ALSO (...; ...) as one statement, block comments that nest, and a backslash in a string literal as a character (standard_conforming_strings), so 'a\' ends there. Undeclared, it takes both readings of the backslash and none of the rest.

_set_connection_schema

MyApp::Schema->connect($dsn, $user, $pass, { schema => 'tenant_a' });
# every connection: SET search_path TO tenant_a, public

What the connect key schema means on PostgreSQL (the seam of "_set_connection_schema" in DBIO::Storage::DBI, core ADR 0065): the namespace the connection works in is the first schema of its search_path. Core calls this on every new connection that names the key -- the sync one and each connection of an async pool -- ahead of on_connect_call and on_connect_do, and the one statement written here, SET search_path TO ..., goes to that connection.

The path is "_connection_search_path". See "ONE SCHEMA CLASS IN SEVERAL NAMESPACES" in DBIO::PostgreSQL for what the key does to a deploy.

PostgreSQL takes a search_path that names a schema it does not have and skips that schema when it looks a name up. A connection whose namespace is not there yet therefore connects, and an unqualified name is looked for -- and an unqualified object created -- in the schemas behind it. The install of such a connection creates the namespace before it creates anything else ("install_ddl" in DBIO::PostgreSQL::DDL).

_connection_search_path

my @schemas = $storage->_connection_search_path('tenant_a');
# ('tenant_a', 'public')

The schemas of the search_path of a connection that works in the given namespace, by the rule of "connection_search_path" in DBIO::PostgreSQL::Util: the namespace, then the schemas of the extensions the schema class declares ("pg_extensions" in DBIO::PostgreSQL), for an extension that names none the schema its control file fixes (asked of the server over "_pool_connect_rows" in DBIO::Storage::DBI, so on the sync handle and on a pool connection alike, only for the extensions without a declared schema; a runner that answers no rows, as the ev one, leaves them at public), else public, and public when none is declared, then the search_path entries of the extensions' activation ("_extension_activation"; given as the second argument by "_apply_connection_schema", else asked for). A storage whose schema class does not load the DBIO::PostgreSQL component, and one that has no schema class, declares no extension. "pg_search_path" in DBIO::PostgreSQL is not read.

_declared_search_path

my @schemas = $storage->_declared_search_path($activation);

The schemas of the search_path of a connection without the connect key whose schema class declared "pg_search_path" in DBIO::PostgreSQL, by the rule of "declared_search_path" in DBIO::PostgreSQL::Util: the declared entries, the schemas the declared extensions name, the entries of the extensions' activation (the second argument, else asked for). Empty -- nothing is set -- for a schema that declared no path, one that does not load the DBIO::PostgreSQL component, and a storage that has no schema class.

_extension_activation

my $activation = $storage->_extension_activation($namespace);
# { load => [ 'age' ], search_path => [ 'ag_catalog' ] }

What the declared extensions want of a new connection (ADR 0066, activation), merged by core in the name order of the extensions ("activation" in DBIO::Extension::Protocol): the libraries to LOAD and the schemas for the search_path. Core refuses what it cannot compose -- two libraries for one name. The list is resolved here, at the first use of a connection and so after the driver is resolved, never at connect: the classes are loaded, their interface version and their driver assertion checked ("resolve_list" in DBIO::PostgreSQL::Extension). A schema that declares no extension has an empty activation and nothing is loaded.

The extensions see the state of the connection as far as it is known when it opens: $ctx->{peers}{$name}{installed} is false here, the installed state is read by the deploy, not by a connect. $ctx->{restricted} is false likewise.

_apply_connection_schema

The one place a new connection is set up for the extensions and the namespace, for the sync connection and for every connection of an async pool alike (core runs it ahead of on_connect_call and on_connect_do on both paths). It replaces core's, which only sets the namespace, and does what that does:

  1. LOAD 'library' for every library of "_extension_activation", in the order of the extensions' names.

  2. With the connect key schema: "_set_connection_schema", the search_path of "_connection_search_path".

  3. Without it, when the schema class declared "pg_search_path" in DBIO::PostgreSQL: SET search_path TO ... of "_declared_search_path", in one statement -- the declared entries, the schemas the declared extensions name, the entries of the activation. It replaces the path the DSN or the role gave; on_connect_call and on_connect_do run behind it and can replace it in turn.

  4. Without it and without a declaration: the entries of the activation, if any, are appended to the search_path the connection has (the server's default or the role's), once each, in one statement. Without extensions with an activation nothing is sent.

An extension's activation does not change the search_path of a connection of a schema that declares none beyond that; the schemas of pg_extensions stay what they were: part of the path of a connection that names a namespace, and of a declared path when they name a schema.

_connect_params_dsn

my $dsn = $storage->_connect_params_dsn('Pg', { dbname => 'my db', host => 'db1', port => 5433 });
# dbi:Pg:dbname='my db';host=db1;port=5433

The DSN seam of "_connect_params_dsn" in DBIO::Storage::DBI (core ADR 0057), asked when a schema is connected with connection parameters instead of a DSN -- a connect-info hash with a dbname and no dsn, or a DBIO::AccessBroker::Static. The form is core's default, dbi:Pg:dbname=...;host=...;port=... with the parts not given left out; it is overridden for what goes into it. Core's default writes a value as it comes, and DBD::Pg and libpq both read that text again: a blank ends the value, an apostrophe takes the parameters behind it along, a backslash disappears, and key=value behind a name is a second parameter. Here every value is written by "quote_dsn_value" in DBIO::PostgreSQL::Util, so libpq reads back what was given:

MyApp::Schema->connect({ dbname => 'my db', host => 'db1', user => ..., password => ... });
# the database "my db"

A value is a value, whatever is in it. dbname => '"app"' is the database "app", quotes included, and dbname => 'app sslmode=require' is a database of that name -- neither the DBD::Pg nor the libpq way of quoting, and no further parameter, can be put into one. What the three parts cannot say goes in as the whole DSN: as dsn, or as a dbname that starts with dbi:Pg:, which core takes as the DSN before this seam is asked.

The future_io transport writes a broker's parameters through the same function ("_async_broker_conninfo" in DBIO::PostgreSQL::Storage::Async), so the sync connection of a schema and its future_io backend hold the same DSN for the same dbname, host and port, and open the same database.

The DSN names no sslmode, and without it libpq connects with prefer: a server that does not offer TLS is then connected to in the clear. For a connection over a network give the whole DSN with sslmode=verify-full and sslrootcert=/path/to/ca.crt, which checks the certificate and the host name.

A part that is undefined or empty is as none given. When none of dbname, host and port is given the DSN would be dbi:Pg:, and libpq would take the target from its environment (PGHOST, PGDATABASE, a service file): that is refused, naming the three parameters. One of them is enough.

Refused, naming the parameter and never its value: a value that is a reference, and one containing a NUL character -- no connection string can carry it. A value containing ; does not get here: core refuses it first, for every driver.

_refuse_dsn_without_target

$storage->_refuse_dsn_without_target($dsn);

Throws when $dsn is a whole dbi:Pg: DSN that names no dbname, host or port (nor hostaddr or service, which name a target themselves): dbi:Pg:, dbi:Pg:;, dbi:Pg:sslmode=require. Such a DSN leaves the target to libpq's environment (PGHOST, PGDATABASE, a service file), so the credentials would go wherever the process environment says -- the same refusal as for connection parameters that name none ("_connect_params_dsn"). The message names the parameters, never the DSN, which may carry a password. A connection coderef, an undefined or an empty DSN are not its business (core refuses those). The rule is "dsn_names_target" in DBIO::PostgreSQL::Util.

It is asked where a connection is made, not where connect info is given: a schema that only generates SQL connects with connect('dbi:Pg:') and never opens one ("_connect").

_connect

Opens the connection as core does, after refusing a static DSN that names no target ("_refuse_dsn_without_target"). A storage with an AccessBroker asks the broker's answer at "_access_broker_dbi_args" instead, where the DSN of this very connection is known.

_access_broker_dbi_args

The arguments of DBI->connect from the broker's answer, as core forms them; for the connection itself (connecting => 1) a DSN that names no target is refused first ("_refuse_dsn_without_target"). A plain read of the answer is not a connection and refuses nothing.

_bind_host_into_dsn

my $dsn = $storage->_bind_host_into_dsn('Pg', 'dbi:Pg:dbname=app;hostaddr=10.0.0.1', 'replica1', 5433);
# dbi:Pg:dbname=app;host=replica1;port=5433

my ($none, $form) = $storage->_bind_host_into_dsn('Pg', 'dbi:Pg:service=prod', 'replica1', undef);
# ( undef, "the key 'service' (a service file may name hostaddr)" )

The seam of "_bind_host_into_dsn" in DBIO::Storage::DBI (core ADR 0013, addendum 2026-10-09): what a DBIO::AccessBroker::HostBound view -- a replicant of DBIO::Replicated -- asks to put its own host (and port) into the DSN a broker answered, so one answer serves many servers. Core's default binds host= and port= and declines hostaddr=. libpq reads a hostaddr= before the host=, so a replicant would have gone to the server the answer named; this driver therefore

  • removes every hostaddr= token, whatever the case of its key and however often it stands, and lets core bind host= and port= into what is left. A view host that is an IP address is then connected to directly, as hostaddr= would have done, and a name is resolved by libpq. The server list host=a,b is replaced by the one host of the view, as core does;

  • declines a service= -- a service file may name hostaddr, and no DSN says what is in the file: the key 'service' (a service file may name hostaddr);

  • declines a dbname=, db= or database= that begins with postgresql:// or postgres:// or contains a =: libpq reads that as a whole connection string, which carries a host of its own and goes on top of the keys around it;

  • declines a port= that is a comma list when the view has no port of its own, since a replicant then has no way to name the one port of its host. A view that has a port replaces the whole token.

Whatever else the DSN holds goes through core's default, which declines the forms it does not know the same way. A declined form is named and no value of the DSN is in it.

The environment is not read: a PGHOSTADDR or PGSERVICE in the process that connects is libpq's own and applies to every connection of it.

_dbi_error_fields

my $fields = $storage->_dbi_error_fields($handle);

Core's seam ("_dbi_error_fields" in DBIO::Storage::DBI) for the machine-readable part of a refused statement, the fields of the DBIO::Exception::Server core raises for it. The base answers sqlstate; this adds, from DBD::Pg's pg_error_field, the constraint the statement violated, the table and the detail (Key (name)=(***) already exists.: the values in it are replaced by *** and the column names stay, as they do in the DETAIL: line of the message, which "_mask_error_values" masks; "mask_detail_values" in DBIO::PostgreSQL::Util) -- the server's own PG_DIAG_CONSTRAINT_NAME, PG_DIAG_TABLE_NAME and PG_DIAG_MESSAGE_DETAIL, not parsed out of the text, which depends on the server's lc_messages. The detail is masked with the sqlstate of the same error, so that it holds in the server's language as well ("mask_detail_values" in DBIO::PostgreSQL::Util).

pg_error_field is a method of the database handle. DBI hands HandleError the handle the error occurred on, a statement handle for a failed execute, and the database handle is then read from its Database attribute. A field the server did not report, or reported empty, is left out. Nothing here connects or queries: pg_error_field only reads the result the failure left on the handle, which is why it is asked from within HandleError, before anything else runs on the connection.

The future_io transport (DBIO::PostgreSQL::Storage::Async) raises a plain string and has no such seam; its errors do not carry these fields (it masks the values of the text by its own route, see "ERRORS" in DBIO::PostgreSQL::Storage::Async).

_mask_error_values

my $text = $storage->_mask_error_values($text, \@values, $statement);

Core's seam ("_mask_error_values" in DBIO::Storage::DBI) through which the text the server wrote into an error passes before it is thrown: from the error handler of every handle, the statement _dbh_execute runs and the statements of the connection actions. The base replaces the values bound to the statement in it, each one whole, wherever it stands. This replaces, behind it, the values the server quotes, by the form of its text ("mask_error_details" in DBIO::PostgreSQL::Util): the value of an invalid input syntax for type integer: "...", of a DETAIL: line (Key (a)=(...), Failing row contains (...)), of the CONTEXT: of a bound parameter and of a JSON excerpt. That covers what the base does not know: a value shorter than four characters, a piece of a value (invalid value "gehe" for "YYYY"), a value the statement did not bind (one of the row a constraint names) and every error that reaches the handler with no values at hand. A value that is a name in the text (relation "x" does not exist) is replaced by the base only, which knows the bound values.

The server reports in the language of its lc_messages, so the forms are not read by their English words alone: the SQLSTATE of the error, read from the connection's database handle ("error_sqlstate" in DBIO::PostgreSQL::Util), is passed on, and for a data exception (class 22) or an integrity constraint violation (class 23) the text is masked by the marks it quotes a value in and the form (columns)=(value), in any language. A German Schlüssel »(name)=(ab)« existiert bereits. reads Schlüssel »(name)=(***)« existiert bereits..

A text with no known form is returned as it is, undef and a reference as they are. It never throws: a text the rules cannot read stays as the base left it.

with_deferred_fk_checks

$storage->with_deferred_fk_checks(sub { ... });

Wraps the given coderef in a transaction with SET CONSTRAINTS ALL DEFERRED, restoring immediate constraint checking afterwards. Use this when bulk-loading data that temporarily violates referential integrity.

pg_advisory_lock

$storage->pg_advisory_lock(42);         # SELECT pg_advisory_lock(?)
$storage->pg_advisory_lock(10, 20);     # SELECT pg_advisory_lock(?, ?)

Acquires a session-level exclusive advisory lock, blocking until it is available. The lock is identified either by a single bigint key or by a pair of int keys; the keys are bound as placeholders, never interpolated. Returns true once the lock is held. Session-level locks are held until explicitly released (see "pg_advisory_unlock") or the session ends.

pg_advisory_lock_shared

$storage->pg_advisory_lock_shared(42);

Like "pg_advisory_lock" but acquires a shared session-level advisory lock (pg_advisory_lock_shared). Returns true once the lock is held.

pg_try_advisory_lock

my $got = $storage->pg_try_advisory_lock(42);

Non-blocking variant of "pg_advisory_lock" (pg_try_advisory_lock): returns true if the lock was acquired, false if it could not be obtained immediately.

pg_try_advisory_lock_shared

my $got = $storage->pg_try_advisory_lock_shared(42);

Non-blocking shared variant (pg_try_advisory_lock_shared): returns true if the shared lock was acquired, false otherwise.

pg_advisory_unlock

my $released = $storage->pg_advisory_unlock(42);

Releases a session-level exclusive advisory lock previously acquired with "pg_advisory_lock" (pg_advisory_unlock). Returns true if a lock held by this session was released, false if no such lock was held.

pg_advisory_unlock_shared

my $released = $storage->pg_advisory_unlock_shared(42);

Releases a session-level shared advisory lock (pg_advisory_unlock_shared). Returns true if a matching lock was released, false otherwise.

pg_advisory_unlock_all

$storage->pg_advisory_unlock_all;

Releases every session-level advisory lock held by the current session (pg_advisory_unlock_all). Takes no key arguments. Returns true.

pg_advisory_xact_lock

$storage->pg_advisory_xact_lock(42);
$storage->pg_advisory_xact_lock(10, 20);

Acquires a transaction-level exclusive advisory lock (pg_advisory_xact_lock), blocking until it is available. Transaction-level locks are released automatically at the end of the current transaction; there is no corresponding manual unlock. Returns true once the lock is held.

pg_advisory_xact_lock_shared

$storage->pg_advisory_xact_lock_shared(42);

Shared transaction-level advisory lock (pg_advisory_xact_lock_shared), released automatically at transaction end. Returns true once the lock is held.

pg_try_advisory_xact_lock

my $got = $storage->pg_try_advisory_xact_lock(42);

Non-blocking transaction-level advisory lock (pg_try_advisory_xact_lock): returns true if the lock was acquired, false otherwise. Released automatically at transaction end.

pg_try_advisory_xact_lock_shared

my $got = $storage->pg_try_advisory_xact_lock_shared(42);

Non-blocking shared transaction-level advisory lock (pg_try_advisory_xact_lock_shared): returns true if the shared lock was acquired, false otherwise. Released automatically at transaction end.

last_insert_id

my @ids = $storage->last_insert_id($source, @cols);

Returns the last inserted value(s) for the given column(s) by querying the associated sequence via pg_catalog. Only called when INSERT ... RETURNING is unavailable (PostgreSQL older than 8.2). The sequence is determined from the column default expression.

sqlt_type

my $type = $storage->sqlt_type;   # 'PostgreSQL'

The name of this engine, PostgreSQL, as a constant: unlike "sqlt_type" in DBIO::Storage::DBI it asks no connection for the DBD's name. The inherited "deployment_statements" in DBIO::Storage::DBI names a DDL file by it, so reading that file needs no server (see "Installing a schema").

bind_attribute_by_data_type

my $attr = $storage->bind_attribute_by_data_type($data_type);

Returns { pg_type => DBD::Pg::PG_BYTEA() } for binary LOB types so that DBD::Pg handles BYTEA columns correctly.

is_replicating

my $streaming = $storage->is_replicating;

Whether the connected server is a standby that is streaming from its primary. This is what "validate_replicants" in DBIO::Replicated::Pool asks every replicant, and it tells three answers apart: a true one goes on to "lag_behind_master", a false but defined one deactivates the replicant, and no answer at all leaves the replicant as it is.

  • A server that is not in recovery answers 0. That is a primary, and it is a standby that has been promoted: neither follows a primary, so neither is a replica to read from, and PostgreSQL can say so (pg_is_in_recovery()). DBIO::MySQL::Storage answers nothing for a server that is not a replica; here that would keep a promoted standby in the pool for good. A primary listed as a replicant by mistake loses nothing by the 0: with no replicant active the balancer reads from the master.

  • A standby without a WAL receiver answers 0, and so does one whose WAL receiver is in any state but streaming (waiting, restarting, stopping). pg_stat_wal_receiver has its one row only while the receiver process is alive and has connected to its upstream, so a standby that lost its primary has no row. This also deactivates a standby that is fed by restore_command alone: it never starts a WAL receiver, and is replicating by shipped files, not by a stream this method can see.

  • A WAL receiver whose state the role may not read counts as streaming. Without membership in pg_read_all_stats (which pg_monitor includes) a role sees the row with its pid and every other column NULL. The row alone says the receiver is alive and connected; reading the missing state as "not streaming" would deactivate every replicant of an ordinary application role. What such a role cannot see is a receiver that is connected but momentarily waiting, restarting or stopping.

  • No answer comes only from a server older than PostgreSQL 10, which lacks the functions the query uses. The replicant is then managed by hand, as it was before this method existed.

lag_behind_master

my $seconds = $storage->lag_behind_master;

How far the standby's data is behind, in whole seconds -- the unit DBIO::MySQL::Storage reports, so that maximum_lag of DBIO::Replicated::Pool means the same on both. Never negative. Answers nothing when "is_replicating" is not true: a server that follows no stream has no lag that means anything, even if it has replayed all it ever received.

  • 0 when everything received has been replayed (pg_last_wal_replay_lsn() >= pg_last_wal_receive_lsn(); replay runs ahead of the stream while WAL comes from the archive). This is what keeps an idle primary from looking like lag: the time since the last replayed transaction grows for as long as nothing is written, although nothing is missing.

  • Otherwise the age of the last replayed transaction, clock_timestamp() - pg_last_xact_replay_timestamp(), cut to whole seconds. clock_timestamp() because now() stands still inside a transaction. A standby under steady load is always some milliseconds behind; reading that as 0, as MySQL's whole seconds do, is what lets the default maximum_lag of 0 keep it active. A standby whose clock is behind its primary's would give a negative age, which is reported as 0. The same age is used when the standby has never received anything by streaming in this run and there is no position to compare.

  • Nothing when WAL is outstanding and no transaction has been replayed yet -- a standby just started. There is WAL to apply and no timestamp to date it by, so no number of seconds exists; the pool leaves such a replicant as it is. The window closes with the first commit or abort record replayed.

Two things this number cannot see. It is measured against what has arrived on the standby: WAL the primary has written but not yet sent is not in it, and a silent link shows as lag only once wal_receiver_timeout has ended the WAL receiver and "is_replicating" turns false. And it overstates once: after a long quiet period the first new WAL arrives while the last replayed transaction is still the old one, so the age reads as the whole quiet period until that WAL is applied.

cake_defaults

Returns PostgreSQL-optimized defaults for DBIO::Cake. Activated via use DBIO::Cake '-Pg'.

  • inflate_jsonb — on (PostgreSQL standard is jsonb, not json)

  • inflate_datetime — on

  • retrieve_defaults — on (PostgreSQL generates UUIDs, serials, NOW(), etc.)

SEE ALSO

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.