NAME

DBIO::PostgreSQL::Util - Shared PostgreSQL-specific helper functions for DBIO

VERSION

version 0.900007

SYNOPSIS

use DBIO::PostgreSQL::Util qw(
  quote_ident quote_qualified quote_literal quote_role quote_channel
);

quote_ident('users');                  # users
quote_ident('user');                   # "user"
quote_ident('MixedCase');              # "MixedCase"
quote_qualified('auth', 'Users');      # auth."Users"
quote_literal("it's");                 # 'it''s'
quote_role('PUBLIC');                  # PUBLIC

my $sql = 'LISTEN ' . quote_channel($channel);   # LISTEN "jobs"

use DBIO::PostgreSQL::Util qw(type_takes_modifier type_with_modifier);

type_takes_modifier('bit varying');                 # true
type_takes_modifier('integer');                     # false
type_with_modifier('numeric', [ 10, 2 ]);           # numeric(10,2)
type_with_modifier('timestamp with time zone', 3);  # timestamp(3) with time zone

use DBIO::PostgreSQL::Util qw(number_placeholders);

number_placeholders(q{SELECT * FROM t WHERE a = ? AND b = ?});
# SELECT * FROM t WHERE a = $1 AND b = $2
number_placeholders(q{SELECT $$ what? $$, 'why?', ? -- really?});
# SELECT $$ what? $$, 'why?', $1 -- really?

use DBIO::PostgreSQL::Util qw(quote_dsn_value);

my $dsn = 'dbi:Pg:dbname=' . quote_dsn_value($dbname);
quote_dsn_value('appdb');                # appdb
quote_dsn_value('my db');                # 'my db'
quote_dsn_value('app sslmode=disable');  # 'app sslmode\=disable'

use DBIO::PostgreSQL::Util qw(connection_search_path);

connection_search_path('tenant_a', [ 'vector' ], { vector => 'extensions' });
# ('tenant_a', 'extensions')

DESCRIPTION

Small helpers that capture PostgreSQL-specific truths shared across the driver and the transports built on it, so the same rule is not re-encoded in each of them. Nothing is exported by default.

This module depends on Carp and Exporter only. A transport outside this distribution -- the native ev backend in dbio-postgresql-ev -- can load it without pulling in the future_io transport (DBIO::PostgreSQL::Storage::Async), dbio-async or Future::IO.

METHODS

quote_ident

my $sql_ident = quote_ident($name);

Write a name as one PostgreSQL identifier, by the rule of the server's own quote_ident(): bare when the name is all lower-case ASCII letters, digits and underscores, does not start with a digit and is no key word other than an "unreserved" one; otherwise in double quotes with an embedded " doubled.

quote_ident('users')        # users
quote_ident('user')         # "user"        reserved
quote_ident('position')     # "position"    key word, not unreserved
quote_ident('name')         # name          key word, unreserved
quote_ident('MixedCase')    # "MixedCase"   bare it would fold to mixedcase
quote_ident('uuid-ossp')    # "uuid-ossp"
quote_ident('a.b')          # "a.b"         one identifier, not two

The name is taken exactly: its case is kept, and a . is part of it. For a name of several parts use "quote_qualified".

Throws for an undefined or empty name, a reference, and a name containing a NUL byte -- none of them can name anything, and writing them would produce a statement that fails for a reason nobody can read off it.

quote_qualified

my $sql_name = quote_qualified($schema_name, $table_name);
my $sql_name = quote_qualified($table_name);

Write a qualified name: every part quoted by "quote_ident" for itself, the parts joined with ..

quote_qualified('auth', 'users')          # auth.users
quote_qualified('Auth', 'user')           # "Auth"."user"
quote_qualified('my.schema', 'tbl')       # "my.schema".tbl

The caller says where the parts end. A name is never taken apart at its dots here, because a schema or a table may have one in its name.

Throws without a part, and for every part "quote_ident" refuses.

quote_literal

my $sql_literal = quote_literal($value);

Write a value as a SQL string literal: in single quotes, an embedded ' doubled.

quote_literal('draft')      # 'draft'
quote_literal("it's")       # 'it''s'
quote_literal('')           # ''

This is the standard form, the one the server writes itself when it shows a constant (pg_get_expr). A backslash is an ordinary character in it, which is how PostgreSQL reads a literal with standard_conforming_strings on -- the default since 9.1.

Throws for an undefined value, a reference and a value containing a NUL byte.

trim_end

my $text = trim_end($text);          # trailing whitespace off
my $text = trim_end($text, ';');     # trailing whitespace and semicolons off

Cut the whitespace at the end of a string, and with a second argument also each of the characters it names, in any order and number. The string is read once from its end, so the time is linear in the length of what is cut, however long a run of whitespace stands inside the text. Used for the texts the catalog hands over (view and function definitions).

create_schema_if_missing

my $sql = create_schema_if_missing($name);   # a DO block, no closing semicolon

Write the creation of a schema (namespace) for a role that may not have CREATE on the database. CREATE SCHEMA checks that privilege before it looks at IF NOT EXISTS, so CREATE SCHEMA IF NOT EXISTS fails for a role that may use an existing namespace but has no CREATE on the database. This statement asks pg_catalog.pg_namespace first and runs a plain CREATE SCHEMA only where the name is missing. It is a DO block, so it is one piece wherever it stands: in the install script, which is written without a connection, in the SQL of a diff and in the temporary database of a diff. The name is a literal in the lookup (exactly as given) and an identifier in the statement ("quote_ident"). The block is dollar-quoted with a tag the name does not contain.

mask_detail_values

my $text = mask_detail_values('Key (email)=(a@b.c) already exists.');
# Key (email)=(***) already exists.

The values of a server's detail text are replaced by ***. A message names keys, columns and options, never values; the column names, the constraint and the table stay, and so does the rest of the text. The text is the detail field with or without its DETAIL: label, and may hold line breaks. The forms:

Key (a, b)=(***) already exists.
Key (id)=(***) is not present in table "users".
Key (r)=(***) conflicts with existing key (r)=(***).
Failing row contains (***).
Partition key of the failing row contains (k) = (***).
Token "***" is invalid.
Expected string, but found "***".
Escape sequence "***" is invalid.
Unexpected "***" character.
Character with value *** must be escaped.
line 1: ***

The columns of a key stay where they are a list of plain identifiers or an expression of names and balanced parentheses; a key text that could hold a part of its value in what looks like a column (a quoted name, an =) is masked whole, Key *** already exists.. A libxml detail (line N: ...) is masked after its line number, it repeats the input. A text without any of these forms is returned as it is.

The forms above are the words of the server's English. A server that reports in another language (lc_messages) writes the same detail in its own words (Schlüssel »(name)=(ab)« existiert bereits.), so the masking cannot hang at the wording. With the SQLSTATE of the error as the second argument (mask_detail_values($text, $sqlstate), "error_sqlstate"), a detail of class 22 (data exception) or 23 (integrity constraint violation) that none of the forms above read is masked by what holds in every language: the (columns)=(value) of a key (the columns stay where they are readable), a bracket (value, value) of a row, and what stands between the marks a language quotes a value in ("...", »...«, « ... », „...“, “...”, 「...」; the blanks inside them stay). A detail that has a bracket or a mark left over once masked is not told from its value: the whole text, its label kept, is ***. Without the state, and for a state of another class, only the forms above are read.

mask_error_details

my $message = mask_error_details($message);

Mask the values in the text of a server's error. The message is cut into the fields of the server (the primary text, DETAIL: , HINT: , CONTEXT: , QUERY: , LINE n: , STATEMENT:, LOCATION:; a field runs to the next one) and each is read by its own rules. A prefix (DBD::Pg::st execute failed: ) and the [for Statement ...] DBI appends are no part of any rule.

The DETAIL: field is "mask_detail_values". The primary text keeps its sentence and masks the quoted value:

ERROR:  invalid input syntax for type integer: "***"
ERROR:  invalid input value for enum mood: "***"
ERROR:  date/time field value out of range: "***"
ERROR:  value "***" is out of range for type smallint
ERROR:  malformed array literal: "***"
ERROR:  invalid hexadecimal digit: "***"
ERROR:  invalid byte sequence for encoding "UTF8": ***
ERROR:  time zone "***" not recognized
ERROR:  invalid value for parameter "TimeZone": "***"

The CONTEXT: field loses the values the server quotes into it: the bound parameters of the portal ($1 = '***', whose number and NULL stay), the excerpt of a JSON text and the data of a COPY line. The LINE n: ... excerpt of the statement, its caret line, the hint, the query and the position fields are the statement's text and stay as they are, as does a text of no known form, and the names of a relation, a column, a function or a constraint. Each rule gives the same text when it is applied to its own result.

The rules above are the server's English. The message of a server that reports in another language (lc_messages) quotes its values in its own words, so with the SQLSTATE of the error as the second argument (mask_error_details($message, $sqlstate), "error_sqlstate") the masking holds on what is the same in every language and not on the wording. For class 22 (data exception) a primary text that no rule above read has the span from its first quoting mark to its last ("...", »...«, « ... », „...“, “...”, 「...」; the blanks inside the marks stay) replaced by ***, the marks kept (ungültige Eingabesyntax für Typ integer: »***«), else what follows its last colon. For class 22 and 23 a detail that no rule above read is "mask_detail_values"' second stage, and the parameters of a portal ($1 = '***') are masked in any field but the statement's. A label of any language starts a field (capital letters, ZEILE 1: , DÉTAIL : ): one with a number is the statement excerpt and stays; a known English label is read as before; another one is a context where it holds a line words 1: rest (JSON-Daten, Zeile 1: ***) or a portal parameter, else a detail. The second stage runs only on a field where the English rules found nothing, and not at all in a message whose severity label is English (ERROR: ): a sentence of an English server that no rule knows holds no value of its own. Without the state, and for a state of another class, the message is read as described above, English only.

error_sqlstate

my $state = error_sqlstate($dbh);    # 23505, or undef

The SQLSTATE of the error that just happened on a database handle, which "mask_error_details" and "mask_detail_values" take as their second argument. The handle is a DBD::Pg database handle, or a statement handle (its Database is read). The state is the handle's state, which DBI gives in every language of the server and keeps through the call; where that is empty, DBD::Pg's pg_error_field('sqlstate') (it clears the error DBI holds, which is why it is asked second). undef where there is none: no handle, an empty state, 00000.

quote_role

my $sql_role = quote_role($role);

Write one entry of a role list, as it follows TO in CREATE POLICY. A role name is an identifier and is quoted by "quote_ident". PUBLIC, CURRENT_USER, CURRENT_ROLE and SESSION_USER are not names but key words standing for a role; they are recognised in any case and written as given, because quoted they would name a role that is called that (TO "PUBLIC" fails with role "PUBLIC" does not exist).

quote_role('app_user')      # app_user
quote_role('AppUser')       # "AppUser"
quote_role('PUBLIC')        # PUBLIC
quote_role('current_user')  # current_user

deferrable_clause

deferrable_clause(1, 1)   # " DEFERRABLE INITIALLY DEFERRED"
deferrable_clause(1, 0)   # " DEFERRABLE INITIALLY IMMEDIATE"
deferrable_clause(0, 0)   # ""

The timing clause of a FOREIGN KEY or UNIQUE constraint, from the model keys deferrable and initially_deferred, with the space that leads it. A constraint that is not deferrable has none (it is the server's default). initially_deferred without deferrable is no constraint PostgreSQL has (it refuses it), so it throws here too.

type_takes_modifier

if (type_takes_modifier($type)) { ... }

True when $type names a PostgreSQL type that takes a type modifier: the built-in ones, under each of their names, and the pgvector types.

character  char  bpchar  nchar  national character  national char
character varying  char varying  varchar  nchar varying
national character varying  national char varying
bit  bit varying  varbit
numeric  decimal  dec
time  time without time zone  timetz  time with time zone
timestamp  timestamp without time zone  timestamptz  timestamp with time zone
interval  interval second  interval {day|hour|minute} to second
vector  halfvec  sparsevec

The name is compared in lower case and with single spaces between its words. A name that already carries a modifier (character(10)), an array type (varchar[]) and a quoted name ("char", which is another type) are not on the list.

False for every other name. That covers two cases this function does not tell apart: a built-in type without a modifier, for which the server refuses one (integer(11) is a syntax error, text(255) gives type modifier is not allowed for type "text"), and a type this distribution does not know -- an extension's, a domain -- which may take one or not.

type_with_modifier

my $sql_type = type_with_modifier($type, $size);

Write $type with $size as its type modifier, where the server's grammar takes it: behind the name, and for the ... with time zone and ... without time zone forms behind their first word.

type_with_modifier('character', 10)                # character(10)
type_with_modifier('numeric', [ 10, 2 ])           # numeric(10,2)
type_with_modifier('numeric', 10)                  # numeric(10)
type_with_modifier('timestamp with time zone', 3)  # timestamp(3) with time zone
type_with_modifier('time without time zone', 0)    # time(0) without time zone
type_with_modifier('interval day to second', 3)    # interval day to second(3)
type_with_modifier('character', undef)             # character

$size is a column's size: one value, or an arrayref of them in the order the type takes them. Undefined, an empty string and an empty arrayref are no size, and $type is returned as it is. The name itself is written as given.

This function writes; it does not ask whether $type takes a modifier. A caller that may find a size beside a type without one asks "type_takes_modifier" first.

Throws for a size that is any other reference: written out it would be HASH(0x...). Throws for a size that is not N or N,M in digits (a list of one or two numbers is written the same way): a size is a number, never SQL, and the message does not repeat it.

The optional third argument $context names the column the size belongs to, as the caller writes it (users.name). A refusal then ends in for column users.name, so the message points at the column and still not at the value. Without a context the message is the same without that ending.

type_with_modifier('character', 'x', 'users.name')
# dies: a size must be digits, ... comma for column users.name

quote_channel

my $sql_ident = quote_channel($channel);   # "jobs"

Validate a LISTEN / NOTIFY channel name and quote it as one PostgreSQL identifier: wrapped in double quotes, embedded " doubled. Returns the quoted identifier, ready to follow LISTEN or UNLISTEN; the argument is not modified.

Quoting makes the name exact -- case included -- which is also how pg_notify takes it, so LISTEN on Jobs and pg_notify('Jobs', ...) meet. A . stays part of the name: app.events becomes "app.events", not the schema-qualified "app"."events".

Refused, by throwing: an undefined, empty or reference channel, one containing a NUL byte, and one longer than 63 bytes in UTF-8 (PostgreSQL's NAMEDATALEN - 1). The limit counts bytes, not characters: 32 characters of U+00E9 are 64 bytes and refused. It is a refusal, not a truncation, because a longer name can never deliver: LISTEN would silently truncate it and then report notifications under the truncated name, which matches no registered channel, and pg_notify rejects it outright ("channel name too long").

It is the channel rule of the future_io transport, whose "_pg_quote_channel" in DBIO::PostgreSQL::Storage::Async delegates here, and the native ev transport applies the same rule.

number_placeholders

my $positional = number_placeholders($sql);

Number every ? placeholder of one statement as PostgreSQL's positional $N, left to right, so that they line up with the bind values in the order the SQL maker gives them. Returns the numbered statement; the argument is not modified.

number_placeholders(q{UPDATE t SET a = ? WHERE id = ?})
# UPDATE t SET a = $1 WHERE id = $2

A ? is a placeholder where PostgreSQL reads SQL. Text it reads as something else is passed over as a whole, delimited as its scanner delimits it:

  • a string '...' and a quoted identifier "...", a doubled quote inside staying inside. The prefixes B, X, N and U& change nothing about where the string ends.

  • an escape string E'...' / e'...', in which a backslash takes the next character with it, so \' does not end it. Its continuation on a following line (E'a', newline, 'b') is an escape string as well.

  • a dollar-quoted body, $$...$$ or $tag$...$tag$, closed by the very same delimiter only. A tag starts with a letter or an underscore, so $1 is a parameter and opens nothing; a $ inside an identifier (a$b$) opens nothing either.

  • a -- comment up to the end of the line, and a /* ... */ comment, which nests.

number_placeholders(q{SELECT $body$ what? $body$, ?})   # SELECT $body$ what? $body$, $1
number_placeholders(q{SELECT /* a /* b? */ c? */ ?})    # SELECT /* a /* b? */ c? */ $1
number_placeholders(q{SELECT E'it\'s ?', ?})            # SELECT E'it\'s ?', $1

A string, body or comment that is never closed runs to the end of the statement; the server reports it.

Outside of these a ? is a placeholder wherever it stands, behind an operator character as well: no operator is known here. tags <@?::int[] becomes tags <@$1::int[], @@? becomes @@$1 and @? ? becomes @$1 $2 -- as DBD::Pg reads the same statement at the dbh.

A \? outside a string, a body and a comment is a literal question mark and no placeholder, as DBD::Pg reads it (since 3.5.0): the way to write the operators ?, ?|, ?& and @?, and how the SQL maker writes the jsonpath operator ("_where_op_jsonb_path" in DBIO::PostgreSQL::SQLMaker). The backslash is dropped, so the statement that comes back holds a bare ? -- what the server reads, and what DBD::Pg leaves standing under pg_placeholder_dollaronly:

number_placeholders(q{SELECT a @\? ?::jsonpath})   # SELECT a @? $1::jsonpath
number_placeholders(q{SELECT a \?| ARRAY[?, ?]})   # SELECT a ?| ARRAY[$1, $2]

Inside a string, a body or a comment the backslash is text and stays. Only the one backslash directly before the ? goes. Because it goes, a statement with a \? is numbered once: the ? it left is a placeholder to a second pass, which refuses the statement where a $N stands beside it and numbers the ? where none does.

A statement that has $N beside a ? placeholder is refused, as DBD::Pg refuses it (Cannot mix placeholder styles): numbering both would give one parameter two meanings and run the statement with one value twice. A $N in a string, a body or a comment is text and counts as none; so does a \?.

standard_conforming_strings is taken to be on, the server's default: a backslash in a plain '...' string is text. DBD::Pg's :name placeholders are not read. The statement is not otherwise validated: what the server would refuse reaches it and is refused there.

Throws for an undefined statement and a reference, as "quote_literal" does for a value: neither is a statement anybody wrote, and numbered they would come back as an empty query and as the reference's address, which the server reads as SQL.

This is the interface the future_io transport and the ev backend of dbio-postgresql-ev share (see "PLACEHOLDERS"): it is public and stable.

quote_dsn_value

my $dsn = "dbi:Pg:dbname=" . quote_dsn_value($dbname)
        . ";host="         . quote_dsn_value($host);

Write the value of one libpq connection parameter as it goes into a DBD::Pg DSN, so that libpq reads back exactly the value given. Bare when nothing on the way touches it; otherwise in single quotes, with ', \ and = escaped by a backslash.

quote_dsn_value('appdb')                 # appdb
quote_dsn_value('2001:db8::1')           # 2001:db8::1
quote_dsn_value('my db')                 # 'my db'
quote_dsn_value(q{it's})                 # 'it\'s'
quote_dsn_value('back\slash')            # 'back\\slash'
quote_dsn_value('"app"')                 # '"app"'
quote_dsn_value('app sslmode=disable')   # 'app sslmode\=disable'
quote_dsn_value('')                      # ''

Quoted is every value that is empty or contains whitespace, ;, ', \, = or ". Two readers make that list:

  • libpq (conninfo_parse) ends a bare value at whitespace and reads a backslash as an escape, bare or quoted. Behind a = it skips whitespace before the value starts, so an empty value written bare would take the next parameter for its value.

  • DBD::Pg edits the DSN as text before libpq sees it (DBD::Pg::dr::connect, dbd_db_login6; measured on 3.20.2). A ; outside single quotes separates parameters. The first db= or database= anywhere becomes dbname=, and the first dbname="..." becomes dbname='...' -- both inside a quoted value as well. Both rewrites need a = to match, and the second a " straight behind it: with every = of a value written as \= neither finds one inside a value, and a quoted dbname starts with ' where the second looks for ". libpq reads \= as =.

So the double quotes of "app" are part of the value -- the database "app", not app. The DBD::Pg way of quoting a name belongs into a DSN somebody writes, not into a value. For the same reason a parameter cannot be put in behind a value: app sslmode=disable is a database of that name.

Only the value is written. The keyword in front of it, and the ; between two parameters, are the caller's.

Throws for an undefined value, a reference and a value containing a NUL byte -- no connection string can carry one: the DSN is a C string from DBD::Pg on and would end there, the parameters behind it lost. The message does not repeat the value. A caller that knows which parameter it is writing checks these first and names it.

dsn_names_target

dsn_names_target('dbi:Pg:dbname=app');            # true
dsn_names_target('dbi:Pg:service=prod');          # true
dsn_names_target('dbi:Pg:sslmode=require');       # false
dsn_names_target('dbi:Pg:');                      # false

Whether a whole dbi:Pg: DSN names where to connect: at least one of dbname (db, database), host, port, hostaddr or service with a value. A DSN without one leaves the target to libpq's environment (PGHOST, PGDATABASE, a service file), so the credentials would go wherever the process environment says; the storages refuse to connect with it ("_refuse_dsn_without_target" in DBIO::PostgreSQL::Storage, DBIO::PostgreSQL::Storage::Async), as they refuse connection parameters that give none. The parameters of a DSN are read as "quote_dsn_value" describes the two readers: ; outside single quotes separates them like whitespace, a value is bare or in single quotes with a backslash as the escape, and an empty value is no value. The part dbi:Pg(attributes): in front is skipped, and a libpq URI (postgresql://..., postgres://...) in place of the parameters counts as a target -- it is libpq's to read. It is a function of the text alone: nothing is connected, and it does not judge a value.

connection_search_path

my @schemas = connection_search_path($namespace, \@extensions, \%extension_schemas, \@activation, \%control_schemas);

The schemas of the search_path of a connection that works in $namespace, in order: the namespace, then the schema of each declared extension -- @extensions are the names in the order they were declared, %extension_schemas the schema of those that name one, as "pg_extensions" in DBIO::PostgreSQL and "pg_extension_schemas" in DBIO::PostgreSQL answer them. An extension that names no schema stands for the one its control file fixes (%control_schemas, the optional fifth argument: extension name to schema, as "control_schemas_sql" in DBIO::PostgreSQL::DDL reads it from the server), else for public -- the rule of the CREATE EXTENSION of the install, so the path finds where the extension was put. Without a server, and therefore without the fifth argument, it is public. With no extension declared, public follows the namespace. Behind them stand the search_path entries the extensions' activation gives (@activation: "_extension_activation" in DBIO::PostgreSQL::Storage, already in name order of the extensions), so the order is: the namespace, the schemas of the declaration, the entries of the activation. Every schema stands once, at its first place.

connection_search_path('tenant_a', [], {})
# tenant_a, public
connection_search_path('tenant_a', [ 'vector' ], {})
# tenant_a, public
connection_search_path('tenant_a', [ 'vector' ], { vector => 'extensions' })
# tenant_a, extensions
connection_search_path('tenant_a', [ 'pgcrypto', 'vector' ], { vector => 'extensions' })
# tenant_a, public, extensions
connection_search_path('tenant_a', [ 'age' ], {}, [ 'ag_catalog', 'public' ])
# tenant_a, public, ag_catalog
connection_search_path('tenant_a', [ 'age' ], {}, [], { age => 'ag_catalog' })
# tenant_a, ag_catalog

The answer is names, not SQL: each goes into a statement through "quote_ident".

declared_search_path

my @schemas = declared_search_path(\@declared, \@extensions, \%extension_schemas, \@activation);

The schemas of the search_path of a connection that names no namespace and whose schema class declared "pg_search_path" in DBIO::PostgreSQL, in order: the declared entries, then the schema of each declared extension that names one (an extension without a schema adds nothing -- unlike "connection_search_path", which stands public for it: here the list is the user's, and public is written into it where it is wanted), then the entries of the extensions' activation. Every schema stands once, at its first place.

declared_search_path([ 'a', 'b' ], [ 'pgcrypto', 'vector' ], { vector => 'extensions' })
# a, b, extensions

quote_search_path_entry

my $sql = quote_search_path_entry($entry);

One entry of a search_path as SQL: a name by "quote_ident", so MySchema becomes "MySchema", and $user -- no name, the schema of the session's role -- as "$user", the one way the server reads it. An entry already in double quotes would be quoted twice into another schema, and one that is empty, not a plain string or holds a NUL byte is no name: both are refused, naming the entry.

NAMES AND VALUES IN GENERATED SQL

Everything in this distribution that writes a name or a value into a DDL statement -- DBIO::PostgreSQL::DDL, the DBIO::PostgreSQL::Diff operation classes, DBIO::PostgreSQL::Deploy -- goes through "quote_ident", "quote_qualified", "quote_literal" and "quote_role", and through nothing else. A name is whatever a declaration or the catalog says an object is called; it becomes an identifier here, once, by the server's own rule.

What a declaration documents as a SQL fragment is not a name and is written as given: an index where or expression, a policy's using and with_check, a trigger's when, event and execute, a default_value => \'...', a type string (data_type, pg_enum_type, pg_type_name), pg_extra_ddl and a function's SQL. So is what the server hands back ready-made (pg_get_indexdef, pg_get_triggerdef, pg_get_functiondef, pg_get_expr, format_type).

TYPES WITH A MODIFIER

A column's size is PostgreSQL's type modifier: the length of a character(10), the precision and scale of a numeric(10,2), the fractional seconds of a timestamp(3) with time zone. Which types take one, and where it is written, are two facts about the server. They are kept here, once -- "type_takes_modifier" and "type_with_modifier" -- and DBIO::PostgreSQL::DDL, DBIO::PostgreSQL::Adapter, DBIO::PostgreSQL::Diff::Column and "target_from_compiled" in DBIO::PostgreSQL::Diff write a type with a size through them and through nothing else.

PLACEHOLDERS

DBIO::PostgreSQL::SQLMaker writes a bind as ?: the sync driver hands its statements to DBD::Pg, which takes that form. PostgreSQL itself takes only $1, $2, ..., so a transport that sends a statement with its binds beside it has to number them first -- and has to know which ? is one. A ? is a placeholder where PostgreSQL reads SQL; inside a string, a dollar-quoted body or a comment it is text. That is one fact about the server's scanner (src/backend/parser/scan.l). It is kept here, once, in "number_placeholders": the future_io transport numbers through it ("_transform_sql" in DBIO::PostgreSQL::Storage::Async), and the native ev transport in dbio-postgresql-ev reads a statement by the same rule.

"number_placeholders" is the interface the two transports share: public and stable, called by its name and with one statement, by this distribution's future_io transport and by the ev backend of dbio-postgresql-ev (maintainer's decision 2026-10-16). What it answers for a statement changes only together with the server's reading or DBD::Pg's.

Where DBD::Pg reads less than the server

The sync driver does not number through "number_placeholders": DBD::Pg finds the placeholders of a statement with a scanner of its own, and that one reads less than the server's. This cannot be mended here; it is a fact of DBD::Pg (measured against DBD::Pg 3.x and PostgreSQL 18). Four forms are read differently, each with a ? or a $ the server takes as text or as no delimiter:

  • a nested comment, /* a /* b ? */ c ? */: the server nests, so the whole is one comment; DBD::Pg ends it at the first */ and counts the ? behind it as a placeholder.

  • a lower case escape string, e'it\'s ?': the server reads it as an escape string and the \' does not end it; DBD::Pg takes it for a plain string, ends it at the \' and reads on from there. (The upper case E'...' is read as the server reads it.)

  • a carriage return as the end of a -- comment: the server ends the comment at a \r as well as at a \n; DBD::Pg only at the \n, so a ? behind the \r is lost to it.

  • a $ inside an identifier, a$b$: the server reads a word as a whole and opens no dollar quote in it; DBD::Pg opens one at $b$ and passes over the placeholders up to the next $b$.

The count of placeholders then differs from the count of binds, or a ? that is text is bound. The future_io transport (and ev) numbers by the server's rule, so the same statement can run over the sync dbh and not over the transport, or the other way round. What to do: do not write these four forms into SQL that has placeholders -- close a nested comment into one level, write E'...' in upper case, end a --comment with \n, and keep a $ out of identifiers (or quote them, "a$b$") -- or send what the text has to carry as a bind value instead of a literal. A ? that must stay a question mark is \?, which both read the same.

CONNECTION PARAMETERS IN A DSN

A connection may be given by its parameters instead of a DSN -- dbname, host, port, as DBIO::AccessBroker::Static answers them. This distribution then writes the dbi:Pg: DSN itself, and a parameter's value has to arrive at libpq as it was given: a database is called what it is called, blanks and quotes included, and a value is never read as a second parameter. How a value is written so that it does is one fact about the two readers between the DSN and the value, DBD::Pg and libpq. It is kept here, once, in "quote_dsn_value": the sync storage writes through it ("_connect_params_dsn" in DBIO::PostgreSQL::Storage), and so does the future_io transport ("_async_broker_conninfo" in DBIO::PostgreSQL::Storage::Async) -- which is what makes the two connections of one schema open the same database.

THE SEARCH PATH OF A CONNECTION

A connection opened with the connect key schema works in one namespace ("ONE SCHEMA CLASS IN SEVERAL NAMESPACES" in DBIO::PostgreSQL). Which schemas its search_path holds is one rule. It is kept here, once, in "connection_search_path": the storage sets the path of every connection by it ("_set_connection_schema" in DBIO::PostgreSQL::Storage), and the install script of such a connection sets the same one ("install_ddl" in DBIO::PostgreSQL::DDL).

SEE ALSO

DBIO::PostgreSQL::DDL, DBIO::PostgreSQL::Diff, "_set_connection_schema" in DBIO::PostgreSQL::Storage, "_connect_params_dsn" in DBIO::PostgreSQL::Storage, "_async_broker_conninfo" in DBIO::PostgreSQL::Storage::Async, "_transform_sql" in DBIO::PostgreSQL::Storage::Async, "LISTEN / NOTIFY" in DBIO::PostgreSQL::Storage::Async, "LISTEN / NOTIFY" in DBIO::Storage::Async

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.