Packages

@kama/postgres 0.4.0

PostgreSQL client in kama: the v3 wire protocol, libpq's connection rules, TLS, COPY, pipelining and a statement cache

Install

kama pkg add kama.json @kama/postgres --version ^0.4.0

or in kama.json: "@kama/postgres": { "version": "^0.4.0" }

License
MIT OR Apache-2.0
Repository
github.com/cosmic-canopy/kama-postgres
Keywords
postgresql database sql libpq client
Dependencies
@kama/tls ^0.1.7

Versions

VersionPublishedRevisionIntegrity
0.4.0fb42423c1057sha256-839cc2d442f9…
0.3.0d0a7ccbc83aasha256-f236d698cfad…
0.2.16d025282ed9dsha256-c7618ec4b06d…
0.2.04cb88c0903c6sha256-fc21c36a0bac…
0.1.03ab354bd8089sha256-3a2c346afea6…

@kama/postgres

The PostgreSQL client for kama. It speaks the PostgreSQL v3 wire protocol directly, in kama, over std::net, the way pgx, tokio-postgres, pgjdbc and Npgsql do. There is no libpq and nothing to install. TLS comes from @kama/tls.

kama pkg add kama.json @kama/postgres --version ^0.4.0     # from the official registry, registry.kama-lang.org

Needs kama ≥ 0.9.734, declared in the manifest, so an older compiler is refused by name. Tested, debug and release, on macOS arm64 and Linux against PostgreSQL 14–18 and 19 beta, and on Windows x86-64 against PostgreSQL 18 for Windows.

Status: early. It connects, authenticates and runs queries, with typed parameters and typed rows, over TCP, a Unix-domain socket or TLS (every sslmode, client certificates, SCRAM-SHA-256-PLUS). The statement cache, transactions and savepoints, COPY, waiting for notifications, pipelining and batches, cancellation and query timeouts, target_session_attrs and load_balance_hosts came in 0.2.0. A pool shared by isolates, transactions scoped by a borrow window, and bounds on what a server can make a connection hold came in 0.3.0, and Windows (x86-64) in 0.4.0. Kerberos (GSSAPI and SSPI) is next: see the roadmap. In 0.x, a minor version may break the API.

Using it

import { core::args, core::println, std::collections::DynamicArray,
         postgres::Config, postgres::Connection, postgres::PgError, postgres::Rows, postgres::column,
         postgres::columnOpt };

fn uint8 main() {
    // A libpq connection string; the service file, PG* variables and ~/.pgpass apply as they do for psql.
    Optional<string> given = args().get(at: 0);
    string conninfo = match (give given) { case Some(value: s): give s; case None: ""; };
    Result<Config, PgError> parsed = Config.parse(text: conninfo);
    Config config = match (give parsed) { case Ok(value: c): give c; case Err(error: e): { println(s: e.message()); return 1; } };
    Result<Connection, PgError> opened = Connection.connect(config: config);
    Connection conn = match (give opened) { case Ok(value: c): give c; case Err(error: e): { println(s: e.message()); return 1; } };

    bool encrypted = conn.sslInUse();
    Optional<string> protocol = conn.sslAttribute(name: "protocol");   // "TLSv1.3"
    string version = match (give protocol) { case Some(value: p): give p; case None: "none"; };
    println(s: "encrypted: ${encrypted} (${version})");

    // A simple query may hold several statements, and gives one Rows for each.
    string sql = "create temp table items (name text, qty int4);"
        .concat(other: "insert into items values ('bolt', 12), ('nut', 40), (null, 3);")
        .concat(other: "select name, qty from items order by qty");
    Result<DynamicArray<Rows>, PgError> r = conn.simpleQuery(sql: sql);
    DynamicArray<Rows> results = match (give r) { case Ok(value: v): give v; case Err(error: e): { println(s: e.message()); return 1; } };
    string listed = "";
    isize i = 0;
    while (i < results[2].rowCount()) {
        // Typed access, as PQgetvalue addresses a value: the result, the row, the column. NULL reads only into an Optional.
        Result<Optional<string>, PgError> name = columnOpt::<string>(rows: results[2], row: i, index: 0);
        Result<int32, PgError> qty = column::<int32>(rows: results[2], row: i, index: 1);
        Optional<string> named = match (give name) { case Ok(value: n): give n; case Err(error: e): { println(s: e.message()); return 1; } };
        int32 count = match (qty) { case Ok(value: q): q; case Err(error: e): { println(s: e.message()); return 1; } };
        string shown = match (give named) { case Some(value: s): give s; case None: "(no name)"; };
        println(s: "${shown}: ${count}");
        if (i > 0) { listed = listed.concat(other: ", "); }
        listed = listed.concat(other: "${shown} ${count}");
        i = i + 1;
    }
    conn.close();
    if (!listed.equals(other: "(no name) 3, bolt 12, nut 40")) { println(s: "expected (no name) 3, bolt 12, nut 40"); return 1; }
    return 0;
}

That is examples/quickstart, and every code block in this README comes from one of the examples, each of which has run against every server this package is tested on.

Parameters go through the extended protocol, with the pg tag or a Query. A hole is always a parameter, never text in the SQL. Holes are typed: an empty Optional is NULL, and a DynamicArray<uint8> is bytea:

int32 least = 5;
Optional<string> supplier = Optional::None;
Query q = pg"select name, qty, supplier, tags from items where qty > ${least} and supplier is not distinct from ${supplier}";
Result<Rows, PgError> r = conn.query(q: q);

rowAs::<T> reads a row into a @generate(Deserializable) type by column name:

@generate(Deserializable)
type resource Item {
    @field string name;
    @field(name: "qty") int32 quantity;
    @field Optional<string> supplier;
    @field DynamicArray<string> tags;
    @field(default) string unit = "each";

    public const fn string described() {
        string from = match (this.supplier) { case Some(value: s): copy s; case None: "anyone"; };
        isize tagged = this.tags.length();
        return "${this.name}: ${this.quantity} ${this.unit} from ${from}, ${tagged} tags";
    }
}

// The row as an Item, described; or the error's message.
unsafe fn string itemAt(const ref Rows rows, isize row) {
    Result<Item, PgError> item = rowAs::<Item>(rows: rows, row: row);
    return match (item) { case Ok(value: x): x.described(); case Err(error: e): e.message(); };
}

A row or a column also reads into std's JsonValue, when its shape is not known in advance. prepare gives a reusable Statement. startRows/nextRows read a large result a chunk at a time, and openPortal/fetch read a cursor inside a transaction.

A server error is PgError::Server with every field PostgreSQL sends. Compare e.sqlstate() with the constants in postgres::sqlstate. Messages read as psql prints them. With no NoticeHandler set, notices go to std::log under the tag postgres.

TLS

TLS is libpq's, setting for setting: sslmode from disable to verify-full with libpq's fallbacks (prefer tries TLS and then plaintext, allow the other way round, each on a new connection after a refusal), sslnegotiation=direct (PostgreSQL 17 and later), sslrootcert (a file, or system), sslcrl and sslcrldir, sslcert/sslkey/sslpassword, sslcertmode, sslsni, ssl_min_protocol_version/ssl_max_protocol_version, channel_binding and sslkeylogfile. As in libpq, a root certificate file that exists means the server's chain is verified under any sslmode, and verify-full checks the server's name by libpq's rules (subjectAltNames, then the CN, one-label wildcards).

kama run examples/quickstart/kama.json -- "host=db.internal dbname=inventory user=app sslmode=verify-full sslrootcert=/etc/pg/ca.crt"

The quickstart then reads back what the session negotiated:

bool encrypted = conn.sslInUse();
Optional<string> protocol = conn.sslAttribute(name: "protocol");   // "TLSv1.3"

Connection.connectWith(dialer:, config:) takes a Dialer (pgx's DialFunc) that opens each connection, through a tunnel or a proxy, and still gets libpq's host, address and encryption fallbacks. Connection.connectOver(transport:, config:) starts a session over one transport the caller opened, TLS included.

The TLS library is Mbed TLS, through @kama/tls, not OpenSSL. What that changes:

Transactions

A transaction is a borrow window. transaction() sends BEGIN, and the window ends the transaction when the block is left by any path (its end, return, break, continue): ROLLBACK, unless it was committed. It is psycopg's with conn.transaction() with no closure, because the window is the scope. The connection is frozen inside the window, and tx.conn() reaches it:

// Moves `cents` from account 1 to account 2, or nothing at all.
unsafe fn Result<Unit, PgError> transfer(ref Connection conn, int64 cents) {
    borrow conn.transaction() as tx {
        Result<Unit, PgError> begun = tx.began();
        match (give begun) { case Ok(value: u): { } case Err(error: e): { return Result::Err(error: give e); } };
        Result<Optional<int64>, PgError> debit = tx.conn().execute(q: pg"update accounts set balance = balance - ${cents} where id = 1");
        match (give debit) { case Ok(value: n): { } case Err(error: e): { return Result::Err(error: give e); } };   // rolls back
        Result<Optional<int64>, PgError> credit = tx.conn().execute(q: pg"update accounts set balance = balance + ${cents} where id = 2");
        match (give credit) { case Ok(value: n): { } case Err(error: e): { return Result::Err(error: give e); } };
        return tx.commit();
    }
}

begin(options:), commit() and rollback() run the same statements without a window. savepoint() gives a Savepoint the connection names (sp_1, sp_2, …), for releaseSavepoint(sp:) and rollbackToSavepoint(sp:).

TransactionOptions options = TransactionOptions.of(isolation: IsolationLevel::Serializable, access: AccessMode::ReadOnly, deferrable: true);
Result<Unit, PgError> begun = conn.begin(options: options);
match (give begun) { case Ok(value: u): { } case Err(error: e): { println(s: e.message()); return 1; } };
Result<Rows, PgError> total = conn.query(q: pg"select sum(balance)::int8 from accounts");
Result<Unit, PgError> done = conn.commit();

COPY

COPY runs as libpq runs it, and std::io drives it, as pgx's CopyFrom and CopyTo do:

Result<File, IoError> opened = File.open(path: "items.csv", mode: OpenMode::Read);
File file = match (give opened) { case Ok(value: f): give f; case Err(error: e): { println(s: e.message()); return 1; } };
Result<Optional<int64>, PgError> copied = conn.copyFrom(sql: "copy items (name, qty, supplier) from stdin (format csv, header true)", source: file);

copyTo(sql:, sink:) writes COPY OUT to any Writer. The calls underneath are libpq's:

Data goes out in 64 KiB writes. If the server has already failed the COPY, the next put stops it rather than sending the rest.

The statement cache

Each SQL text is prepared once, as a named statement, and bound directly after that. A repeated query then takes one round trip instead of two. This is pgx's design, and it is on by default, with 512 statements kept and the least recently used closed. It is set on the Config, since libpq has no keyword for it, with config.setStatementCache(mode:, capacity:). The modes:

When the server refuses a cached plan because a table changed under it ("cached plan must not change result type"), the entry is dropped. Outside a transaction the query runs again once, as pgjdbc does it; inside one, the error is returned. DISCARD ALL and DEALLOCATE ALL empty the cache.

Pipelines and batches

A Batch sends many queries in one round trip, and is pgx's SendBatch:

Batch batch = Batch.make();
isize i = 0;
while (i < names.length()) {
    string name = copy names[i];
    batch.add(q: pg"insert into items (name, qty) values (${name}, 0)");
    i = i + 1;
}
batch.add(q: pg"select count(*) from items");
Result<DynamicArray<BatchResult>, PgError> results = conn.runBatch(batch: batch);

Each query's result is Rows, Failed or Aborted. A failure aborts the queries after it, and the batch, which is one implicit transaction, rolls back. Any SQL the cache does not know yet is described first, in a single round trip.

Underneath is libpq's pipeline mode, which is public:

After an error, each command up to the next sync is Aborted, as in libpq, and libpq's refusals are quoted word for word. Seven of the nine traces of libpq's own libpq_pipeline test replay message for message.

Cancel and timeouts

conn.cancelToken() gives a CancelToken, which is Sendable and can be copied. Another isolate uses it to cancel the running command with cancel(). The request goes as libpq 17's does: to the same address, with the session's sslmode, so it is encrypted when the session is.

A query timeout cancels and keeps the connection, as Npgsql and pgjdbc do:

conn.setQueryTimeout(timeout: Optional::Some(value: Duration.fromSecs(s: 30i64)), grace: Duration.fromSecs(s: 2i64));

When a call waits past the timeout, the command is cancelled, and the server's 57014 ("canceling statement due to user request") is returned. The connection can then be used again. If the server does not answer within the grace period, the connection is closed and the call returns PgError::Timeout. A connection made with connectOver has no way to send a cancel, so it closes at the timeout.

Checking and resetting a session

conn.ping() sends an empty query, as pgx's Ping does. The server answers it in a failed transaction block too. An error means the session is gone.

conn.resetSession() returns the session to the state a new one starts in, so a connection can be handed to someone else. It is Npgsql's reset, read from Npgsql 10.0.3's source:

It takes one round trip. The rollback and the reset go in one write. Npgsql sends the reset in front of the connection's next command instead. Here a failure is reported to whoever is resetting the session, and never lands on the next command after that command has run. A reset that fails leaves the session half reset, so the connection is closed and the error returned.

What it does not touch:

It is refused, as any command is, while a result, a COPY or pipeline mode is in progress.

A pool

postgres::pool keeps connections for the isolates of a program to share, as pgxpool, HikariCP and Npgsql keep theirs.

PoolOptions options = PoolOptions.defaults();
options.maxSize = 2;
options.minIdle = 1;   // the maintenance keeps one connection ready
Result<Pool, PgError> made = Pool.make(config: config, options: options);
Pool pool = match (give made) { case Ok(value: p): give p; case Err(error: e): { println(s: e.message()); return 1; } };

acquire() returns a Lease, and the lease gives its connection back when it is dropped, on every path out of its scope. lease.conn() is the connection: pass it wherever a ref Connection goes, or open a transaction window on it.

unsafe fn int32 job(ref Pool pool) {
    Result<Lease, PgError> got = pool.acquire();
    Lease lease = match (give got) { case Ok(value: l): give l; case Err(error: e): { println(s: e.message()); return -1; } };
    borrow lease.conn().transaction() as tx {
        Result<Unit, PgError> begun = tx.began();
        match (give begun) { case Ok(value: u): { } case Err(error: e): { println(s: e.message()); return -1; } };
        Result<Rows, PgError> r = tx.conn().query(q: pg"select pg_backend_pid() from pg_advisory_xact_lock(42)");
        Rows rows = match (give r) { case Ok(value: v): give v; case Err(error: e): { println(s: e.message()); return -1; } };
        Result<int32, PgError> backend = column::<int32>(rows: rows, row: 0, index: 0);
        int32 pid = match (backend) { case Ok(value: p): p; case Err(error: e): { println(s: e.message()); return -1; } };
        Result<Unit, PgError> done = tx.commit();
        match (give done) { case Ok(value: u): { } case Err(error: e): { println(s: e.message()); return -1; } };
        return pid;
    }
}

A pool belongs to the isolate that made it. To use it from another, pool.share() makes a PoolShare, which is Sendable, and Pool.join(share:) in that isolate makes a pool over the same connections, with the same maxSize between them all. Only the pool that made the connections' channels can share them.

Result<PoolShare, PgError> shared = pool.share();
PoolShare share = match (give shared) { case Ok(value: s): give s; case Err(error: e): { println(s: e.message()); return 1; } };
Worker w = Worker.make(share: give share, report: answers.sender());
Isolate t = spawn work(w: give w);

The defaults are pgxpool's where pgxpool has one:

The rest come from other pools:

Pool.makeWith(connector:, config:, options:) takes a Connector that makes the connections, for a pool that connects through a Dialer or sets each session up its own way.

Notifications

waitForNotification(timeout:) returns the next LISTEN/NOTIFY notification. If none arrives in time it returns None, and it takes in any notices on the way. libpq leaves this loop to the application, through PQsocket, PQconsumeInput and PQnotifies.

Limits

What a server can make one connection hold is bounded, where libpq bounds nothing. Each bound is set on the Config, since libpq has no keyword for it, and each is an Optional: None turns it off.

Limits limits = Limits.defaults();
limits.resultBytes = Optional::Some(value: 64 * 1024 * 1024);   // fail a result over 64 MiB, and keep the session
config.setLimits(limits: limits);
bound default past it
readBuffer: bytes taken in while a write waits 1 GiB the connection closes
parameters: distinct parameters the server reports 1,000 the connection closes
notifications: notifications waiting to be read 10,000 the oldest is dropped
resultBytes: row bytes one call keeps off the call fails, and the session goes on

Reaching a bound is PgError::Limit, which names the bound, its value and what was given up.

When the server restarts

A session open across a restart is over. Its next call fails with what libpq reports in its place:

isClosed() is then true, and every later call is PgError::Closed. A pool reads isClosed() to drop such a connection. A connect while the server is down fails as libpq's does ("Connection refused" and its hint), and with several hosts it goes on to the next one. While the server starts, a connect may meet "the database system is starting up" (57P03), which also moves on to the next host. The integration suite restarts each server to check all of this, in plaintext and over TLS.

Several hosts

A connection string can list several hosts, as libpq's can:

Where this differs from libpq

What it covers

No GSSAPI/Kerberos/SSPI yet: a server that asks for them gets a clear error. They are the roadmap's phase 10.

On Windows

The client follows libpq's Windows rules, as psql on Windows does:

Examples

Each is a program of its own under examples/. Each takes a connection string as its argument, or reads libpq's PG* variables when given none, as psql does:

kama pkg install examples/quickstart/kama.json
PGHOST=localhost PGUSER=app PGDATABASE=inventory kama run examples/quickstart/kama.json
kama run examples/quickstart/kama.json -- "postgresql://[email protected]/inventory"

Each one checks what it reads back, and exits 1 if anything is wrong. The tables they make are temporary, so they need no privilege beyond connecting. tools/test.sh builds them, and tools/check-readme.sh holds every code block in this README to one of them. tools/test-integration.sh runs them against every server, in plaintext and over TLS with sslmode=verify-full.

Tests

KAMA=/path/to/kama tools/test.sh               # hermetic unit tests debug and release, the examples built, the README checked
KAMA=/path/to/kama tools/test-integration.sh   # live tests on PostgreSQL 14–18 and 19 beta, debug and release, then the examples
tools/test-integration.sh --version 18         # one version
tools/pg.sh smoke --version 18                 # one login per auth and TLS method, with the server's own psql
tools/pg.sh restart --version 18               # also stop and start: the server keeps its data
tools/pg.sh down --version 18

The unit tests need no server. They include a scripted fake server that drives every startup and query path, hostile ones included, and runs Mbed TLS as the server for the TLS paths. Every reply of eighteen conversations is then changed in turn, ten ways, from a seed (garble_test.kama; PGTEST_GARBLE_SEEDS=N runs more): the client must never panic, and a protocol error must end the session. The integration tests start each server in podman or docker, run the whole suite a second time over TLS, and then restart, stop and start each server around the sessions of a last leg of their own. Their expected results come from libpq itself: PostgreSQL's own authentication, service-file and TLS tests (its negotiation matrix and its host-name cases, replayed by tools/gen-ssl-vectors.sh), and the server container's libpq asked case by case (tools/gen-libpq-test-cases.sh).

The pipeline tests replay the traces of libpq's own libpq_pipeline (tools/gen-pipeline-vectors.sh). The session tests run the cases of PostgreSQL's 001_stream_rep.pl and 003_load_balance_host_list.pl (tools/gen-session-vectors.sh).

The integration server has one role per authentication method and TLS on. A hot standby streams from each server, for target_session_attrs and load_balance_hosts. Its test certificates are generated into out/ and never committed. See tests/integration/server/.

License

@kama/postgres is licensed under either of

at your option. It vendors nothing; its TLS comes from @kama/tls, whose README says what that bundles.

Contribution

Unless you explicitly state otherwise, any contribution intentionally submitted for inclusion in @kama/postgres by you, as defined in the Apache-2.0 license, shall be dual licensed as above, without any additional terms or conditions.