Revision history for DBIO-PostgreSQL
0.900007 2026-11-17
* Fixed
- No bound or quoted value is in the text of a server error, whatever
language the server reports in (lc_messages). The masking holds on
the SQLSTATE and the quoting marks of the text, not on the English
wording: a short value, the key or row of a DETAIL and the quoted
value of a data exception are masked in German, French and any
other language, on the sync storage and on the future_io transport.
mask_error_details and mask_detail_values take the SQLSTATE as an
optional second argument, and error_sqlstate reads it from a
handle; with one argument they work as before.
- The bytes of an invalid or untranslatable character sequence
(SQLSTATE 22021, 22P05) are in no language in the text of a server
error, the name of the encoding stays. The masking cuts no
character through, whether the text arrives as characters or as
UTF-8 bytes.
* Documentation
- The skills dbio-postgresql and dbio-postgresql-database state the
not-diffed list as CHECK constraints, pg_settings, FORCE RLS and a
changed policy under an unchanged name, and name no live-test
variable the suite does not read.
* Dependencies
- Requires DBD::Pg 3.19.0, which hands a quoted DSN value to libpq
as given, with a ';' or a quote inside it; the DSN model of the
driver and the broker relies on that. It also provides
pg_error_field, which the error fields are read through.
* Tests
- The test suite passes on an install with only the required
modules: the tests of the future_io transport skip when DBIO::Async
is not installed, the storage half of the search_path test skips
when DBIO-PostgreSQL-Age is not installed.
- The tests that send a Unix-domain socket directory through libpq
(the connection-parameter DSN test and the live-server helper test)
skip when the libpq behind DBD::Pg has no Unix-domain sockets, and
the helper test reads the reason of a failed connect by its
meaning there. Its child-process output is read with CRLF line
endings folded, as a pipe delivers them on Windows.
- The tests that compare libpq's own wording run in any locale: they
set the message locale to C, since libpq translates its messages
by the locale of the process. On a perl without POSIX::LC_MESSAGES
(Windows) that helper goes on through the environment alone; the
error-text tests read libpq's reason of a failed connect by its
meaning where libpq has no Unix-domain sockets; and where perl
cannot read the mode of a socket (Windows: no fcntl), the checks
on the mode are skipped in the future_io tests and everything
else runs.
0.900004 2026-11-08
* Storage
- $rs->upsert renders INSERT ... ON CONFLICT ... DO UPDATE|NOTHING
with RETURNING. update_where is the last-write-wins guard, with {
-attempted => $col } for the value the INSERT tried (core ADR 0036).
- key => $constraint in an upsert names the constraint's columns;
conflict_target => 'any' is DO NOTHING and throws with DO UPDATE.
- FOR UPDATE and FOR SHARE take skip_locked => 1 or nowait => 1.
- Session and transaction advisory-lock helpers: pg_advisory_lock,
pg_try_advisory_lock, pg_advisory_unlock and their _shared and xact
variants.
- The jsonpath operator { '@?' => $path } and the JSONB key-existence
operators ?, ?| and ?& are written as operators, so a GIN index
answers them.
- transactional_ddl is declared: install and upgrade DDL runs in one
transaction, and ALTER TYPE ... ADD VALUE runs on its own.
- explain issues a plain EXPLAIN and returns the planner's estimate; a
storage subclass overrides _explain_query for measured runtimes.
results_exist and its async twins work.
- is_replicating and lag_behind_master let DBIO::Replicated::Pool take
out a standby that lost its primary, was promoted or lags.
- A refused statement is a DBIO::Exception::Server naming the violated
constraint, the table and the detail.
- Connection parameters given by value reach libpq as given, on the
sync storage and on future_io alike. Parameters or a DSN that name
no target (dbname, host, port, service=, hostaddr=) are refused.
- Identifier quoting follows the connection: quote_names => 0 or an
empty quote_char write unquoted identifiers, an explicit quote_char
or name_sep is used as given, nothing given quotes with " and . .
- A declared pg_search_path is the search_path of every connection,
sync and future_io, ahead of on_connect_do.
- The postgresql SQL dialect is declared, so the DDL splitter reads
BEGIN ATOMIC bodies, nested comments and backslash-ended literals.
- deploy and deployment_statements are core's: they install through
dbio_deploy_class, with or without the component, and
deployment_statements asks no server.
- The error text of a refused statement names types, columns,
constraints and tables, never values: the quoted value of the
message (invalid input syntax, out of range, enum, array, record,
range, tsvector literals, bytes of an invalid encoding), the key and
row of the DETAIL, the bound parameters of the CONTEXT and the JSON
excerpt read ***; short values, pieces of a value and row values are
masked by the form of the server's text, on future_io too. The
statement excerpt (LINE n) stays.
* Async
- deploy_async works on future_io and emits the sync install DDL,
statement for statement.
- Connection pools take the connect options given beside an
AccessBroker and merge the fifth element of DBI-form connect info.
- LISTEN/NOTIFY on future_io: listen, unlisten, notify, listen_alive,
listen_reconnect and the error and reconnect hooks, on a dedicated
connection outside the pool.
- Pool connections are fork-safe, dead or idle-dead connections are
retired, and a borrow starts and ends with its own session (DISCARD
ALL, then the connect actions again).
- A COMMIT answered with ROLLBACK and a cancelled statement (SQLSTATE
57014) fail their Future. Statements on a transaction context run
one at a time.
- Statements fail with their own text, refusals are DBIO::Exception
objects naming the caller, and secrets are masked in traces and
connect errors (see ERRORS in DBIO::PostgreSQL::Storage::Async).
- The error text of a statement on future_io is netted with its bound
values, as on the sync storage: a value of four characters or more
is taken out of the text whole, also where the server reads it as a
name (relation, type, role, function, configuration parameter),
then the forms of the server's text are masked. The values stand at
the connection's handle only while the statement is in flight.
- Every built-in AccessBroker works on future_io, with connect options
(pool_size, DBD attributes) beside it; an empty answer is refused
and a parameter value reaches libpq as given.
- DBIO::PostgreSQL::Util::number_placeholders numbers a ? only where
PostgreSQL reads SQL, not in dollar quoting, comments or E''
strings; \? is a literal question mark.
* DDL
- Every name is written as an identifier by the server's rule and
every value as a literal (DBIO::PostgreSQL::Util). A mixed-case name
is created exactly as written.
- A column's size is written for every type that takes a modifier
(character, varchar, bit, numeric, time, timestamp, interval and the
pgvector types). A size beside a type without one is not written.
(varchar and pgvector size contributed by John Napiorkowski,
Codeberg #2.)
- A missing is_nullable deploys as NOT NULL.
- pg_fk_constraints_from_rels is on by default: every plain belongs_to
writes a named FOREIGN KEY with its ON DELETE / ON UPDATE actions
and DEFERRABLE settings; pg_fk_constraints_from_rels(0) or
is_foreign_key_constraint => 0 switches it off. (Opt-in key
generation contributed by John Napiorkowski, Codeberg #2.)
- pg_index takes per-column opclasses, INCLUDE, using, with, where and
expressions; key columns are quoted. UNIQUE constraints take
pg_unique_options and get no duplicate index.
- pg_search_path entries and pg_settings keys are names by the same
rule: MySchema stays MySchema, a key word is quoted, an entry
already in double quotes is refused.
- pg_rls takes restrictive => 1; a view declared twice or in a cycle
of deploy_depends_on is refused.
- The install script is a script to its end: every piece is a
statement the server takes, pg_settings run as ALTER DATABASE, and
no SET search_path ends it.
- install_ddl and Deploy work on a schema that does not load the
DBIO::PostgreSQL component.
* PgSchema
- pg_schemas answers in declaration order; a pg_schema_class binding
never restricts the deploy, only pg_schemas does.
* Tenant
- The connect key schema, connect($dsn, $user, $pass, { schema =>
'tenant_a' }), installs and queries one schema class in that
namespace; the deploy is restricted to it and the namespace is
created only where missing (core ADR 0065).
* Extensions
- Extensions are a declared list, pg_extensions('pgcrypto', vector =>
{ schema => 'extensions' }), that the driver runs through the
protocol of core ADR 0066: claims, USING for column type changes,
refusals.
- Every connection loads the declared extensions and puts their
schemas on the search_path.
- CREATE EXTENSION names its schema: the declared one, else the one
the control file fixes (age: ag_catalog), else public. A relocatable
extension is moved when the declaration names another schema.
- A deploy names missing extensions with the CREATE EXTENSION that
fixes them. A deploy restricted by pg_schemas drops none;
manage_extensions_when_restricted => 1 drops the undeclared ones.
* Deploy
- The temporary database of a diff is made from template0 with the
live database's locale, checked to be empty, and refuses an open
transaction.
- apply and upgrade ask for allow_destructive before the first
statement for a drop or a lossy column change, and refuse a NOT NULL
column that existing rows cannot fill.
- foreign_keys_not_valid adds keys NOT VALID and validates them in
transactions of their own.
* Diff
- Statements come in the order of dependency: created objects first,
dropped ones in reverse.
- Views, materialized views and foreign tables are created and dropped
as what they are; a changed view is rebuilt with the views on it.
- Partitions, inheritance and UNLOGGED tables are diffed as the model
knows them; the copies the server makes for partitions are not part
of it.
- Drops are RESTRICT; objects outside the model that a drop would meet
are named and refused.
- What the diff cannot write is refused with the object and the way
out, never written as a comment or a stand-in (see Diff, WHAT IS
REFUSED).
- A column type change converts enum and composite types with USING
and rebuilds the views that read the column.
- Enum values are added at their declared place; procedure and
function swaps, serial sequences, aggregates and window functions
are handled (Diff::Sequence, Diff::SequenceOwner).
- PRIMARY KEY and UNIQUE are created and repaired as constraints, and
a renamed key is renamed rather than rebuilt.
- Foreign keys (Diff::ForeignKey) are compared, created, validated and
dropped with the install's names; keys into tables outside the
deploy are kept and named in the summary.
- A name that differs from the one to drop only in case is refused
with the RENAME that settles it, instead of creating one object and
dropping the other with its data.
- A default the declaration does not carry is dropped.
* Introspect
- The model is read under an empty search_path, so every text names
its schema (qualified_names, on by default), and the caller's
session stays as it was.
- The model leaves out what an installed extension owns and what the
server makes as part of another object; index entries come from the
catalog.
- Catalog text is read in linear time.
- Aggregates, window functions, empty enums and composite types, and
the dependencies between objects are part of the model.
* pgvector
- vector($col)->l2_distance($v) and the other distance operators
render a bind-valued expression for as_order, as_select and
comparisons; the cast option is checked against the pgvector type
names.
- nearest_neighbors on DBIO::PostgreSQL::ResultSet orders by distance
and selects neighbor_distance; declaring a vector column enables it.
- vector and tsvector columns inflate and deflate by default, opt-out
with inflate_pgvector => 0 and inflate_tsvector => 0.
* Security
- Values in the DETAIL of a server exception read ***, on the sync
storage and on future_io; column names, constraint and table stay.
- Sequence names, jsonb paths and savepoint names are binds or
checked; identifier, size and option declarations are refused when
malformed, naming the option.
- sslmode=verify-full with sslrootcert is the documented setting for a
connection over a network.
* Test
- DBIO::PostgreSQL::Test::Conformance answers core's shared DDL, Diff,
Bind and Names case tables against a server, one schema per case.
* Documentation
- The README describes the implemented state and Perl 5.20+;
DBIO::PostgreSQL documents the stacked use base form and a PGVECTOR
guide; every public method has a POD entry.
* Dependencies
- Requires DBIO 0.900004, DBD::Pg 3.7.1 and Crypt::URandom. Every
module carries a literal $VERSION in step with the distribution.
0.900001 2026-07-12
* Storage
- Add DBIO::PostgreSQL::Storage::Async — the first REAL future_io async
transport (core ADR 0030/0031, karr #25). connect(..., { async =>
'future_io' }) now resolves it BY CONVENTION (ref($storage).'::Async',
no registration) and runs genuinely non-blocking SELECT / INSERT /
txn_do over DBD::Pg's pg_async binding driven through Future::IO. The
Model-B orchestration is inherited from DBIO::Async::Storage; this
class fills only the DB-specific transport seams (submit via
{pg_async => PG_ASYNC}, collect via pg_ready/pg_result, socket fd via
pg_socket, ?->$N placeholders, RETURNING * for the returned-columns
hashref). dbio-async is an optional recommends -- only the future_io
mode needs it, resolved by convention per ADR 0030, so a sync-only
install never pulls the async stack; the live roundtrip test
additionally wants a Future::IO::Impl (IO::Async recommended).
- Register the 'ev' async mode (core ADR 0030): connect(..., { async => 'ev' })
now resolves DBIO::PostgreSQL::EV::Storage via the core mode registry;
this shadows the generic modes registered on DBIO::Storage::DBI and
replaces the obsolete async_backend() / load_components('PostgreSQL::EV')
opt-ins. Requires the optional dbio-postgresql-ev dist at first use;
absent → the canonical "install DBIO::PostgreSQL::EV::Storage" croak
(ADR 0030). The dist stays optional — no cpanfile dependency is added.
- Reference-driver compliance for the storage-layer composition model
(karr #28, core #70): storage_type is written once in the Schema
component's connection(); extensions register plain storage LAYERS
(composed via DBIO::Storage::Composed) instead of subclassing
storage_type. _transform_sql stays the single ?->$N rewrite, run once
inside the transport (no double-shaping); transport_capabilities =>
(on_connect_replay), the ev transport carries LISTEN/NOTIFY/COPY/
pipeline and future_io fails loud when a layer needs them. No
behavioural change -- the driver's role is unchanged.
* Diff
- Fix DROP INDEX / DROP TABLE CASCADE collision in full-schema sync
(karr #32). When diff reconciled a live schema containing a table
absent from the target (removed from the app's DBIO classes, or an
unmanaged leftover), it emitted both DROP TABLE <t> CASCADE and a
later standalone DROP INDEX <t>_pkey; since CASCADE already dropped
the table's own indexes, the standalone DROP INDEX died with
"index ... does not exist" and aborted apply()/upgrade().
DBIO::PostgreSQL::Diff::Index now receives the tables sections (via
the aux-section wiring in DBIO::PostgreSQL::Diff) and suppresses a
standalone DROP INDEX for any index whose owning table is itself
being dropped in the same pass. Indexes of surviving tables are
still dropped as before. Scoped to this driver; the same class of
bug likely exists in sibling drivers' Diff/Index equivalents.
* Documentation
- Add docs/adr/0030-async-mode-registration.md — per-repo operationalisation
of the core async mode registry mechanism for the PostgreSQL driver
- Document the { async => 'ev' } opt-in in DBIO::PostgreSQL::Storage POD
* Security
- Add SECURITY.md following the CPAN Security Group author guidelines
(v1.5.0): email-first private reporting to the maintainer with CPANSec
escalation, and a note that Codeberg has no confidential issue channel.
* Dependencies
- requires DBIO 0.900001 (family-aligned).
- DBIO::Async, the shared future_io backend base, is deliberately
NOT declared in cpanfile (karr #27): the sync driver loads
without it; only { async => 'future_io' } pulls it in, resolved
lazily by convention (ADR 0030). Core's future_io resolver now
names the missing module and the distribution that provides it
when the adapter can't load (core karr #78), so a separate
recommends pointer is no longer needed.
* Tests
- Drop references to removed modules so the suite passes on fresh
installs: DBIO::PostgreSQL::Loader/::Loader::Model (removed with
the generation contract) and DBIO::AccessBroker::Credentials
(replaced in core by DBIO::AccessBroker::Static); the
access-broker test now uses Static (Codeberg #1)
- Make the generated-code assertions in both t/25 variants
(-via-introspect and -live) quote-agnostic to match the
B::perlstring double-quoted emitter output; the live variant's
table/set_primary_key/pg_schema checks were missed in the earlier
consolidation and failed against a real database (karr #30)
- Expand t/35-async-mode.t: assert the 'ev' registration does not
shadow the base 'immediate' mode, unknown modes resolve to undef,
and an absent dbio-postgresql-ev croaks with the install hint
- Add t/36-future-io-async.t (offline): the core resolver resolves
future_io to DBIO::PostgreSQL::Storage::Async by convention, the
adapter isa DBIO::Async::Storage, every transport seam is overridden
(none left croaking), and the ?->$N / RETURNING * shaping is correct
- Add t/37-future-io-live.t (gated on DBIO_TEST_PG_*): a live
non-blocking future_io roundtrip — select_async / insert_async /
txn_do_async (commit + rollback), insert_async yielding the
returned-columns hashref with the autoinc PK via RETURNING, a raw
->then Future chain, and the high-level create_async / all_async API
- Add t/38-storage-layer-composition.t (offline structural, 19
assertions) and t/39-storage-layer-composition-live.t (live-gated,
skips cleanly): a storage LAYER composes over DBIO::PostgreSQL::Storage
and its ::Async mirror over DBIO::PostgreSQL::Storage::Async under
{ async => 'future_io' } — the walk resolves the driver transport off
the composition base, not the layer mixin (karr #28)
- xbin/dbio-pg-k8s: use the official postgres:18 image (src.ci retired)
- Add t/40-diff-drop-table-index-cascade.t and extend t/05-diff-index.t:
full-diff regression for the DROP INDEX / DROP TABLE CASCADE collision
(karr #32) plus Diff::Index unit cases for the new tables-aware drop
suppression, including an over-suppression guard (a surviving table's
index is still dropped standalone)
0.900000 2026-06-23
First release. The PostgreSQL driver for DBIO: PostgreSQL-native
storage, SQL generation, schema introspection, diff, DDL and
desired-state deployment.
* Storage
- DBIO::PostgreSQL::Storage, registered for dbi:Pg: DSNs and
autodetected by core via the driver registry
- Loadable as a schema component (load_components('PostgreSQL'))
or via the use DBIO Schema => -pg shortcut, which pins
+DBIO::PostgreSQL::Storage as the storage type
- RETURNING on INSERT/UPDATE/DELETE, last_insert_id via sequences,
AccessBroker connect support
- DBIO::PostgreSQL::Result component and the -pg Result shortcut
for PostgreSQL-native column/source features (schemas, indexes)
* SQL Generation
- DBIO::PostgreSQL::SQLMaker with native JSONB operators:
@> / <@ containment (auto-serializes hashref/arrayref to JSON),
@? / @@ JSONPath (PG 12+), and ? / ?| / ?& key existence
(rewritten as jsonb_exists*() to avoid the DBI placeholder clash)
- DBIO::PostgreSQL::JSONB jsonb() path-expression DSL:
eq/ne/lt/le/gt/ge/like/ilike/is_null/is_not_null/as_order
* Introspection
- DBIO::PostgreSQL::Introspect builds a live model from pg_catalog:
schemas, tables, columns, indexes, sequences, foreign keys,
check constraints, functions, triggers, types/enums, extensions
and row-level-security policies
* Diff and DDL
- DBIO::PostgreSQL::Diff compares two introspected models per
object class (Schema/Table/Column/Index/Type/Function/Trigger/
Policy/Extension) and emits the ALTER/CREATE/DROP statements
- DBIO::PostgreSQL::DDL generates PostgreSQL-native DDL from the
schema, including a CREATE TYPE enum pre-pass, schema-qualified
CREATE VIEW for non-virtual views, and physical-table dedupe
* Deploy
- DBIO::PostgreSQL::Deploy orchestrates test-deploy-and-compare:
introspect live, deploy desired to a temporary schema,
re-introspect, diff, then drop the temp schema; supports
install, diff, apply and upgrade
- Storage declares dbio_deploy_class so DBIO::Schema->deploy()
routes through DBIO::PostgreSQL::Deploy rather than
SQL::Translator
Keyboard Shortcuts
Global
s
Focus search bar
?
Bring up this help dialog
GitHub
gp
Go to pull requests
gi
Go to GitHub issues (only if GitHub is preferred repository)