PostgreSQL connections

Part of aner.db, the native database module. The module reference covers shared credentials, connection lifetime, result conversion and all four connectors.

aner.db.postgres connects through the official native PostgreSQL libpq client. Sessions are reusable, SQL parameters travel separately from SQL text, and results stream into bounded Aner storage. See the database module overview for the other native connectors.

Availability

Use a compatible installed Aner executable with optional PostgreSQL support and a libpq 14 or newer native client runtime. This minimum is for the client library; it does not require a PostgreSQL 14 server. db.available("postgres") reports support in the executable without contacting a server. It does not establish that a connection is healthy.

import aner.db;
print(db.available("postgres"));

If the connector is unavailable, use a native Aner package containing PostgreSQL support and its compatible client runtime. Checking an imported program makes no connection; running its factory connects to the server configured privately.

Private settings and credentials

Configure a credential outside your program. The system store prompts privately:

aner secrets set example-postgres-password --store system
aner secrets status example-postgres-password --store system

Use secrets.stored("example-postgres-password"), or secrets.env("ANER_POSTGRES_PASSWORD") when your environment supplies the value. Where the system store is unavailable, the existing explicit file store is an alternative: aner secrets set example-postgres-password --store file and secrets.file("example-postgres-password"). Linux system storage is unavailable. That explicit POSIX file fallback stores plaintext with owner only permissions, using directory mode 0700 and file mode 0600; it is not an encrypted vault. See Credentials for storage choices and rotation.

The connection accepts a SecretRef, never a password String. Resolution happens when connecting. An empty resolved password is rejected, so libpq cannot silently replace it with an ambient password or password file value. Aner wipes its temporary resolved String; libpq retains its own authentication material until the session closes. Keep real hosts, addresses, users, database names and secrets outside public source, notebook output and websites. Every setting below is fictional.

import aner.db.postgres;
import aner.secrets;
import aner.data;
import aner.tensor;

let connection: DBConnection = postgres.connect(
    host: "postgres.example.invalid", port: 5432, database: "example_science",
    user: "example_reader", password: secrets.stored("example-postgres-password"),
    sslmode: "verify-full", timeout_seconds: 10);

db.postgres.connect(...) is an equivalent qualified spelling. Importing aner.db.postgres enables common db operations too. Arguments, in order: host, port, database, user, password, optional sslmode, optional timeout_seconds. Optional arguments follow that prefix order; supply sslmode when setting the final timeout. Defaults are verify-full and 10 seconds.

TCP, TLS and connection timeout

The host is one DNS name, IPv4 address or IPv6 literal (brackets are optional for IPv6). Socket directories, comma separated host lists, URIs and connection string fragments are rejected. Aner resolves the host itself and gives libpq explicit numeric addresses while retaining the original name for certificate identity checking. Ambient PGHOSTADDR and service routing cannot redirect that selection. DNS may return multiple addresses for this one host; they share one polling deadline. A system DNS lookup may itself block beyond that deadline before Aner can check elapsed time.

Encrypted modes require TLS 1.2 or newer. GSS encryption is explicitly disabled so it cannot replace the requested TLS policy. There is no plaintext downgrade. For a private CA, set libpq's PGSSLROOTCERT to the trusted PEM bundle privately; its standard root certificate location also works. Libpq's require mode can add CA verification when a root bundle is present, but does not become verify-full. Use verify-full for server identity. Unspecified native options, such as client certificates and CA trust settings, can still come from trusted libpq environment/service configuration. Aner pins endpoint selection, user, database, password, TLS mode/minimum, GSS mode, UTF8 and ordinary session mode.

timeout_seconds must be 1 to 300. It bounds native connection polling, including startup/authentication after DNS resolution. A stalled server raises a sanitized connection timeout error. The session sets a 30 second server statement timeout. That server timeout does not impose a client side wall clock deadline on queries, network reads or draining a rejected result; a network stall can still block them. Set an appropriate server statement timeout explicitly when needed, for example:

let timeout_changed = db.execute(connection, "SET statement_timeout = '15s'");

Bound queries, NULLs and exact types

PostgreSQL uses $1, $2, ... placeholders. A parameter List contains String values or null; use SQL casts to establish types. Do not concatenate untrusted values into SQL. Each operation uses the extended protocol with one SQL statement.

let result: DBResult = db.query(connection,
    "SELECT sample_id, feature_a, feature_b, feature_c, label FROM aner_demo.samples WHERE label = $1::integer ORDER BY sample_id",
    List("2"), max_rows: 1000, max_bytes: 1048576);
print(db.rows(result));
if db.rows(result) > 0 {
    let table: DataTable = db.to_table(result, 1048576);
    print(data.integer(table, 0, "label"));
}

let nullable = db.query(connection,
    "SELECT $1::text AS note, $2::numeric AS exact_decimal", List(null, "1234567890.123456789"));
print(db.is_null(nullable, 0, 0));
print(db.text(nullable, 0, 1));

All indexes are zero based. db.column_name and db.column_type describe columns. Check db.is_null before reading nullable cells; SQL NULL is distinct from empty text and zero. Accessing NULL directly raises an error.

Decimals remain text to preserve their exact digits. Bytea remains PostgreSQL's textual representation; it is not silently decoded into a language String of raw bytes. db.text can also retrieve a numeric column's native textual value.

db.query(connection, sql) uses no parameters. Add a List to bind them, then optionally max_rows and max_bytes. Defaults: 100,000 rows and 64 MiB of accounted result storage. Crossing either limit raises an error with no partial result. The remaining protocol messages are drained before returning the error so the session remains usable. SQL failures inside a transaction still need ROLLBACK.

The byte budget accounts retained metadata, row/cell storage and text payloads; it excludes allocator capacity/overhead, libpq/network buffers and temporary native row allocations. A single large field can exceed the budget transiently before rejection. This is not an RSS cap. Push projections, indexed filters and LIMIT into SQL to control remote work.

Transactions, commands and lifecycle

db.execute accepts the same separately bound parameters and returns affected rows, or zero for commands without a count. Aner requires binding or printing its Int64 return value. Commands also use single row mode; accidental result producing SQL raises an error and drains rows without materializing the entire result.

let started = db.execute(connection, "BEGIN");
let inserted = db.execute(connection,
    "CREATE TEMP TABLE aner_example_probe (label integer)");
let rows_added = db.execute(connection,
    "INSERT INTO aner_example_probe VALUES ($1::integer)", List("3"));
print(rows_added);
let rolled_back = db.execute(connection, "ROLLBACK");

Use COMMIT for a deliberate persistent write. The example above rolls back its transaction and temporary table. COPY is unsupported and closes the connection if attempted; use a dedicated supported ingestion route instead.

Copies of DBConnection share a session. db.close is idempotent and invalidates all aliases; final handle release also closes it. Results remain readable after close. Shared session operations are serialized, and workers should open separate sessions. db.connected reads libpq's last known connection status; it is not an active health probe.

Startup and later notices are suppressed before they can print server content. Errors report a validated five character SQLSTATE or a fixed native status, without copying server messages, SQL, private settings or credentials.

Synthetic data and tensors

Run db_mock.sql once in a development database with an administration tool. It creates aner_demo.classes and aner_demo.samples: 400 deterministic samples, three float attributes and four labels (0 to 3). It never drops/truncates existing data and intentionally fails on a second run. These samples are synthetic teaching data, not a scientific benchmark. Adapt db_postgres.aner with private settings.

let features = db.query(connection,
    "SELECT feature_a, feature_b, feature_c FROM aner_demo.samples ORDER BY sample_id",
    List(), max_rows: 1000, max_bytes: 1048576);
let matrix = data.to_tensor(db.to_table(features, 1048576), 1048576);
print(tensor.rows(matrix));
print(tensor.cols(matrix));
db.close(connection);

to_table is an explicit bounded copy into aner.data column storage. It requires unique valid column aliases and marks columns conservatively nullable. Tensor conversion requires finite numeric columns with no missing cells; choose features and handle missingness explicitly before materializing them.

Run the complete example

After running the setup SQL in a dedicated development database, save the downloaded source under examples/db_postgres.aner, replace the fictional settings privately, and create its credential entry. Then check and run it:

aner check examples/db_postgres.aner
aner run examples/db_postgres.aner

The prepared fixture yields 100 selected label 2 rows and a feature tensor with 400 rows and three columns. Checking validates imports and signatures without connecting. Running uses the authenticated database role and verified TLS settings. The SQL fixture intentionally refuses to recreate existing tables, so use a fresh demo schema when starting over deliberately.

Native validation covers exact integers, nulls, bound values, limits, connection reuse, aliases, TLS refusal and certificate identity checks, startup notices and safe diagnostics. Live compatibility depends on the installed client, server and certificate configuration; documentation does not claim universal latency or server support.

Native references: libpq connection controls, parameters and result types and single row mode.