PostgreSQL
nish/net/postgres is a PostgreSQL client. It reads no socket: a
PgConnection writes the messages it wants sent into an output buffer, reads
the server's answers from an input buffer you fill, and tells you what
happened, one event at a time. Your program owns the socket
(nish:net) and the loop, so one loop can drive many
connections next to everything else it serves. It is imported like any other
library module (export and import).
It speaks protocol 3.0, which every PostgreSQL since 7.4 understands, and it is tested against exchanges recorded from PostgreSQL 18.
A query
The program below connects to a server on 127.0.0.1:5432 and looks up three
users by id through a PgBatcher (below): the
requests for ids 7, 3 and 7 become one execution that asks for each id once,
and every request gets its rows, or word that there were none. The connection
asks for nothing a round trip does not need: the statement is prepared, bound
and executed in one write.
import {
PG_BATCH_MISSING,
PG_BATCH_NONE,
PG_BATCH_ROW,
PG_COMPLETE,
PG_ERROR,
PG_FAILED,
PG_GROUP_DONE,
PG_NEED_MORE,
PG_READY,
PG_ROW,
PgBatchConfig,
PgBatcher,
PgConfig,
PgConnection,
} from "nish/net/postgres";
import { connectResult, netAddress, netClose, netRead, netWrite, pollAdd, pollCreate, pollModify, pollWait, tcpConnect } from "nish:net";
import { secret, wipe } from "nish:secret";
const bytesOf = (text: string): u8[] => {
const out: u8[] = [];
const n: i32 = toI32(text.length);
for (let i: i32 = 0; i < n; i++) {
out.push(toU8(text.charCodeAt(i)));
}
return out;
};
export const main = (): i32 => {
const config = new PgConfig();
config.user = "app";
config.database = "app";
const nonce = new Array<u8>(18);
crypto.getRandomValues(nonce);
const password = secret(bytesOf("app password"));
const conn = new PgConnection(config, password, nonce);
wipe(password);
const batch = new PgBatchConfig();
batch.name = "user_by_id";
batch.select = "select u.id, u.name from users u where u.id = q.key";
const users = new PgBatcher(batch);
users.addInt(toI64(7), 0); // the key, and a tag naming the request
users.addInt(toI64(3), 1);
users.addInt(toI64(7), 2);
const noTypes: i32[] = [];
let run: i32 = -1;
const addr = new Array<u8>(18);
netAddress(addr, "127.0.0.1", 5432);
const fd = tcpConnect(addr);
const loop = pollCreate();
pollAdd(loop, fd, 2, 0);
const ready: i32[] = [0, 0];
if (pollWait(loop, ready, 5000) !== 1 || connectResult(fd) !== 0) {
return 1;
}
pollModify(loop, fd, 3, 0);
let code: i32 = 1;
let running: boolean = true;
while (running) {
// Send what the connection queued.
while (conn.outputEnd() > conn.outputStart()) {
const n = netWrite(fd, conn.outputBuffer(), conn.outputStart(), conn.outputEnd() - conn.outputStart());
if (n < 0) {
break;
}
conn.sent(n);
}
// Read what the server answered, and act on each event.
const got = netRead(fd, conn.inputBuffer(), conn.inputEnd(), conn.inputRoom());
if (got === 0) {
conn.closed();
} else if (got > 0) {
conn.received(got);
}
let event = conn.next();
while (event !== PG_NEED_MORE && running) {
if (event === PG_READY) {
conn.prepare(users.name, users.sql, noTypes);
run = users.take(conn); // binds ids 7 and 3 as one execution
conn.sync();
} else if (event === PG_ROW && run >= 0) {
users.row(run, conn);
} else if (event === PG_COMPLETE && run >= 0) {
users.end(run);
} else if (event === PG_ERROR) {
const e = conn.lastError;
console.log(e === null ? "error" : `${e.code}: ${e.message}`);
if (run >= 0) {
users.fail(run);
}
} else if (event === PG_GROUP_DONE) {
code = conn.groupFailed ? 1 : 0;
conn.terminate();
netWrite(fd, conn.outputBuffer(), conn.outputStart(), conn.outputEnd() - conn.outputStart());
running = false;
} else if (event === PG_FAILED) {
console.log(`connection failed: ${conn.failure}`);
running = false;
}
// What the event owes each request, before the next event moves the row.
for (let e = run >= 0 ? users.next(run) : PG_BATCH_NONE; e !== PG_BATCH_NONE; e = users.next(run)) {
if (e === PG_BATCH_ROW) {
console.log(`request ${users.tag}: ${conn.int(0)} ${conn.columnText(1)}`);
} else if (e === PG_BATCH_MISSING) {
console.log(`request ${users.tag}: no such user`);
}
}
if (running) {
event = conn.next();
}
}
if (running) {
pollWait(loop, ready, 5000);
}
}
conn.dispose();
netClose(fd);
return code;
};
A real program keeps one loop for all its sockets, asks for writability only while output is queued, and gives up on a connection that stays silent; the shape stays the same.
Events
next() frames the next message in the input buffer and answers what it did:
| Event | Meaning | Read |
|---|---|---|
PG_NEED_MORE |
the buffer holds no whole message: read more | |
PG_READY |
authenticated and ready; answered once | backendPid, serverVersion |
PG_DESCRIBED |
a prepared statement was described | columns, columnType(c), an OID: 23 is int4 |
PG_ROW |
a row | int(c), bool(c), isNull(c), columnText(c), colStart(c) and colLength(c) |
PG_COMPLETE |
a statement finished | tagIs("UPDATE"), tagRows() |
PG_ERROR |
a statement failed; the rest of its group is skipped | lastError |
PG_NOTICE |
the server sent a notice | lastNotice, notices |
PG_GROUP_DONE |
a sync()'s group is answered |
groupFailed, txStatus (73 idle, 84 in a transaction, 69 in a failed one) |
PG_FAILED |
final: the connection is over | failure, and lastError when the server said why |
A row's columns are not copied: colStart(c) is an offset into
inputBuffer() and colLength(c) its length, -1 for NULL, and they stay
valid until the next next(), which may move the unread input to the start
of the buffer or replace the buffer with a larger one: fetch inputBuffer()
again after every next() rather than keep it, and outputBuffer() after
every builder call.
int, bool and isNull read a column where
it lies; columnText makes a string. conn.text decodes the rest in place:
timestamp(b, start, length, out, at) writes a timestamptz as
2024-01-02T03:04:05.123Z into out, and a PgArrayWalker walks a text[]
column element by element. A decoder that meets text it cannot read answers
0, false or -1 and sets conn.text.failed.
Binary results
Open a bind with bindStartBinary(name) instead of bindStart(name) and
the server sends every result column of that execution in binary format: an
int8 is eight bytes, a timestamptz eight bytes of microseconds since
2000-01-01 UTC, a bool one byte. Nothing is parsed, and a row of ids,
counts and times is smaller on the wire. Read its columns with the binary
decoders:
conn.bindStartBinary("post");
conn.paramInt(id);
conn.bindEnd();
conn.sync();
// on PG_ROW:
const postId: i64 = conn.binaryInt(0); // int2, int4 or int8, by its length
const body: string = conn.columnText(1); // text is the same bytes either way
const n: i32 = conn.binary.timestamp(conn.inputBuffer(), conn.colStart(2), conn.colLength(2), out, 0);
// out[0, n) is 2025-12-31T23:59:19.409Z, the microseconds cut to milliseconds
binaryBool(c) and binaryFloat(c) (a float8) read the other fixed-size
kinds, binaryInt(c) of a time is its microseconds, and
conn.binary.iso(micros, out, at) writes those as PgText.timestamp would.
A text or varchar column needs nothing new. A column of another type, a
numeric or a text[], comes in PostgreSQL's own binary layout, which no
decoder here reads: cast it to text in the SQL, or bind with bindStart.
Rows do not say which format they are in; the program knows which of its
binds asked for binary and reads that execution's rows with
conn.binary, whose failed is set as conn.text.failed is.
A PgBatcher whose PgBatchConfig sets binary does
the same for each of its batches: take opens the bind with
bindStartBinary, so every row comes in binary, and row reads the routing
ordinal, the last column, with binaryInt (an int8, the unnest ordinal
PostgreSQL sends in binary). It saves and restores binary.failed as it
does text.failed for a text batch, so the program's own decoding is left as
it was. A batch that leaves binary false is unchanged.
A one-dimensional int2[], int4[] or int8[] column is the exception: it
is read in place by PgBinaryArray, which takes the column's window and walks
its elements in order:
// on PG_ROW, column 3 is an int4[] of ids:
const ids: PgBinaryArray = new PgBinaryArray(conn.binary);
ids.begin(conn.inputBuffer(), conn.colStart(3), conn.colLength(3));
let sum: i64 = 0;
let r: i32 = ids.next();
while (r === PG_ARRAY_ELEMENT) {
sum += ids.int();
r = ids.next();
}
// r is PG_ARRAY_END when the array was read whole, `ids.count` elements
begin reads the header; next() answers PG_ARRAY_ELEMENT, PG_ARRAY_NULL
(an element that is NULL, which is not an error) or PG_ARRAY_END, and
PG_ARRAY_BAD for an array it refuses. An empty array has no elements, and
ids.count is 0 for it. An ndim above 1 is refused, as is another element
type, an element whose length is not its type's size, and bytes left over
after the last element; each refusal sets conn.binary.failed.
Statements, groups and pipelining
Every statement goes through the extended protocol, so parameters never meet the SQL text:
-
prepare(name, sql, paramTypes)parses and describes a statement. The types are OIDs, and0lets the server infer one. -
bindStart(name), then oneparamInt,paramText,paramBytes,paramTextArray,paramIntArray,paramInt4Array,paramInt8Array,paramNullor array (below) per parameter, thenbindEnd(), runs it.bindStartBinary(name)does the same and asks for the rows in binary. closeStatement(name)frees it on the server.sync()ends the group.
The server answers groups in order, and an error skips only the rest of its own
group. Up to PgConfig.maxInFlight groups may wait for their answers at once:
send the next before the last is answered, and the round trips overlap.
canSend() says whether another group may start.
A builder call that may not run now — before PG_READY, past the cap, a
prepare inside an open bind, a param... call or bindEnd() with no bind
open, a paramBytes window outside its array, a name or SQL holding a NUL
byte, or a message that would put more than PgConfig.maxOutput bytes (1 MiB)
waiting to be sent — answers false and writes nothing. sync() and
terminate() are never refused for room. Send what is queued and call
sent(n), and the room comes back.
Array parameters
An array parameter lets one statement take a whole batch, as in
select ... from unnest($1::text[]) with ordinality as u(x, ord) or
where id = any($1::int8[]):
-
paramIntArray(values, start, count)bindsvalues[start, start + count)as anint8[];count0 is the empty array. paramTextArray(items)binds astring[]as atext[].-
arrayStart(), thenarrayElement(bytes, start, length)(a UTF-8 window),arrayInt(value)(ani64, in decimal) orarrayNull()for each element, thenarrayEnd(), builds one element at a time, straight into the Bind, with no array of the elements first.
conn.bindStart("names");
conn.arrayStart();
for (let i: i32 = 0; i < count; i++) {
if (!conn.arrayElement(body, starts[i], lengths[i])) {
break; // no room under maxOutput: end the array, send the rest next time
}
}
conn.arrayEnd();
conn.bindEnd();
Every element is quoted, with " and \ escaped, so commas, braces, spaces
and the word NULL arrive as text; only arrayNull() is a NULL. The array is
sent as a text literal, so the server reads it as whatever type the statement
gives the parameter: the same elements bind to $1::int4[] or $1::varchar[].
An element refused for room answers false, writes nothing and leaves the
array open, so the elements already in it can still be sent: end the array
and the Bind, and put the rest in the next statement. arrayEnd() is never
refused. When not even the first element fits, bindCancel() drops the open
bind, array and all, unsent, as if bindStart had never been called.
While an array is open, sync(), prepare, closeStatement and bindStart
answer false, as with any open bind. Using an array out of order is a bug, so
it panics: an element or arrayEnd() with no arrayStart(), a second
arrayStart(), or any other param... call or bindEnd() before
arrayEnd(). So do an arrayElement window outside its array and a
paramIntArray range outside values.
In a group that fails, PG_DESCRIBED still comes once for each prepare the
server parsed before the error: count them to know which statements exist (a
named one outlives its group) and which to prepare again. The connection does
not track names: a second prepare("x") while x exists fails its group with
SQLSTATE 42P05, so keep names unique or closeStatement first.
Binary integer array parameters
paramInt4Array(values, start, count) and paramInt8Array(values, start, count) bind the same i64[] window as paramIntArray, but as an int4[] or
int8[] in PostgreSQL's binary layout: a fixed header, then four or eight
bytes an element, with no digits to write on the client or parse on the
server. binaryArrayStart(oid) (23 for int4, 20 for int8), then
binaryArrayInt(value) or binaryArrayNull() for each element, then the
same arrayEnd(), builds one element at a time, NULLs included.
conn.bindStart("posts_by_ids"); // where id = any($1::int8[])
conn.paramInt8Array(ids, 0, toI32(ids.length));
conn.bindEnd();
conn.bindStart("scores"); // from unnest($1::int4[])
conn.binaryArrayStart(23);
for (let i: i32 = 0; i < count; i++) {
if (!(missing[i] ? conn.binaryArrayNull() : conn.binaryArrayInt(scores[i]))) {
break; // no room: end the array, send the rest next time
}
}
conn.arrayEnd();
conn.bindEnd();
A binary array is not a literal the server reinterprets: its element type
must be the statement's. Send an int4[] where the parameter is int8[]
and the server fails that execution with SQLSTATE 42804
(PG_ERROR, as any error); the connection and the rest of the pipeline go
on. An int4 element outside -2^31..2^31-1 panics rather than wrapping,
as do a binaryArrayStart OID other than 23 or 20, a binary element in a
text array or a text element in a binary one, and a window outside values.
Room is refused, and bindCancel() works, exactly as for the text arrays.
A bind whose parameters are all text sends one format code, as before; the
first binary parameter makes bindEnd() write a code for every parameter,
two bytes each, which the bind keeps free under maxOutput from then on.
Binary pays wherever the array is long. On PostgreSQL 18, with seven runs of
bench/postgres/arrays.mjs (median, at
1,000 elements):
| measure | type | text | binary | cut |
|---|---|---|---|---|
server CPU per Bind/Execute, unnest($1) |
int4 |
259.4 µs | 180.3 µs | 30%, within spread |
server CPU per Bind/Execute, unnest($1) |
int8 |
357.4 µs | 176.2 µs | 51% |
server CPU per Bind/Execute, id = any($1) |
int8 |
1255.4 µs | 1085.0 µs | within spread |
| client CPU per array encoded | int4 |
52.3 µs | 5.60 µs | 89% |
| client CPU per array encoded | int8 |
98.2 µs | 7.65 µs | 92% |
client CPU per array_agg column decoded |
int8 |
62.6 µs | 10.3 µs | 84% |
The encode rows time the whole parameter both ways: a Bind holding the array, cancelled, less a Bind holding one NULL.
At ten elements the server's cost is the same either way; where the
statement's own work dominates, as an index probe per element in
id = any($1) does, the format barely shows. Use binary for long unnest
batches and for any client that encodes many arrays; paramIntArray remains
for a statement whose parameter type is not int4[] or int8[].
Connecting over a Unix socket
A server on the same machine also listens on a Unix socket, and psql's
host=/var/run/postgresql reaches it there. pgTarget(host, port) reads a
host and port the way libpq does, and pgTargetUrl(url) reads a
postgres:// or postgresql:// URL; each gives a PgTarget and opens
nothing. When failure is PG_TARGET_OK, a unix target is
unixConnect(target.path) (nish:net), and any other is
netAddress(addr, target.host, target.port) and tcpConnect(addr). Either
way the connection is the same PgConnection.
import { PG_TARGET_OK, pgTargetUrl } from "nish/net/postgres";
import { netAddress, tcpConnect, unixConnect } from "nish:net";
export const main = (): i32 => {
const target = pgTargetUrl("postgres://app@%2Fvar%2Frun%2Fpostgresql/app");
if (target.failure !== PG_TARGET_OK) {
return 2;
}
console.log(target.path); // /var/run/postgresql/.s.PGSQL.5432
let fd: i32 = 0;
if (target.unix) {
fd = unixConnect(target.path);
} else {
const addr = new Array<u8>(18);
netAddress(addr, target.host, target.port);
fd = tcpConnect(addr);
}
return fd < 0 ? 1 : 0;
};
-
A host starting with
/is the socket's directory: the socket is<host>/.s.PGSQL.<port>, the name the server gives it. A path to the socket file itself is taken as a directory too, aslibpqtakes it, and names a socket that does not exist. - A host starting with
@is a Linux abstract socket,@<name>/.s.PGSQL.<port>. -
Any other host is TCP, and the target's
hostis it, unchanged.pgTargetdoes not percent-decode:%2Ftmpis a TCP host there. -
In a URL, the host, the port and the query are percent-decoded, once, so a
directory is written
%2Fvar%2Frun%2Fpostgresql, or given as?host=/var/run/postgresql. Ahostorportin the query overrides the one before the path; other keys are ignored. No port means 5432. An IPv6 address is bracketed,postgres://[::1]:5433/app, and itshostcomes without the brackets. -
The user and password end at the first
@, as inlibpq. Write an@in a password as%40: a second raw@before the path is refused rather than read as part of the host. -
Refused, with
failuresaying why: another scheme or a query key with no=(PG_TARGET_BAD_URL); a%without two hex digits after it,%00, or escapes that decode to bytes that are not UTF-8, anywhere in the URL (PG_TARGET_BAD_ESCAPE); no host, more than one (a,), an unclosed[or text after its]other than:port, a raw@after the first or after a?, or a NUL byte anywhere (PG_TARGET_BAD_HOST); a port that is not 1 to 65535 (PG_TARGET_BAD_PORT); and a socket path longer than the 107 bytes Linux holds, counted in bytes and never cut short (PG_TARGET_TOO_LONG). macOS holds 103: thereunixConnectreturns-36for a path between the two. -
An empty port,
?port=or?host=is refused, wherelibpqtakes an empty value as unset: leave the key out instead.
Over a socket the server can authenticate by peer, the operating system's
word for which user is connecting, or trust; either way it sends
AuthenticationOk without asking for the password, and the connection never
uses it (below).
Authentication
The password is a Secret<u8[]> (nish:secret):
the connection keeps its own copy, so the caller wipes theirs at once, and it
wipes the copy once the proof is computed, or when the connection fails or is
disposed. The nonce is 18 random bytes from
the caller, so the connection itself reads no randomness.
The connection accepts trust and scram-sha-256, PostgreSQL's default
since version 14, and checks the server's signature before it believes the
server. It refuses a server that asks for a cleartext or md5 password, and
fails with PG_ERR_AUTH_UNSUPPORTED, over TLS as well: the first
sends the password as it is, and the second a hash anyone who sees it can
replay, where SCRAM proves the password without sending it. Set the role's password with password_encryption = scram-sha-256.
The password's bytes are used as they are, without SASLprep (RFC 4013), the normalisation PostgreSQL applies to a non-ASCII password. An ASCII password works; a non-ASCII one works only if it is already in SASLprep's form, and otherwise the server refuses it.
Failures and caps
failure says why a connection ended:
| Code | Name | Cause |
|---|---|---|
1 |
PG_ERR_FRAME |
a message whose length or layout does not add up |
2 |
PG_ERR_TOO_BIG |
a message over maxMessage, or a row over maxColumns |
3 |
PG_ERR_UNEXPECTED |
a message the protocol does not allow at that point, COPY among them |
4 |
PG_ERR_AUTH_UNSUPPORTED |
cleartext, md5, GSS, SSPI, or a SASL mechanism other than SCRAM-SHA-256 |
5 |
PG_ERR_SCRAM |
a malformed SCRAM exchange, or iterations outside minIterations..maxIterations |
6 |
PG_ERR_SERVER_SIGNATURE |
the server does not know the password |
7 |
PG_ERR_SERVER |
a FATAL error, or any error before PG_READY; lastError has it |
8 |
PG_ERR_ENCODING |
a client_encoding other than UTF8 |
9 |
PG_ERR_CLOSED |
you called closed(): the stream ended |
10 |
PG_ERR_SETTING |
a DateStyle that is not ISO alone or ISO then a comma (ISO, MDY), or a TimeZone other than UTC, at startup or after a SET |
The connection asks for DateStyle=ISO and TimeZone=UTC at startup, so that
conn.text.timestamp reads the time the server means; a session that changes
either fails rather than be misread.
The caps in PgConfig bound what a server can make the client hold or
compute: maxMessage (1 MiB), maxColumns (1664), maxInFlight (16), and
the SCRAM iteration count, 4096 to 100000; maxOutput (1 MiB) bounds what
the program can queue unsent. A cap out of range — maxMessage or
maxOutput under 1024, maxInFlight or minIterations under 1, and so on —
an empty user, a startup parameter holding a NUL, or a nonce shorter than 18
bytes is a mistake in the program, and the constructor panics.
A pool
PgPool holds up to size connections to one server and a queue of requests
waiting for one. It is sans-IO like the connection: it says which slot needs a
socket, and your loop makes it and moves its bytes.
const pool = new PgPool(config, password, 4, 256); // 4 connections, 256 waiting requests
wipe(password); // the pool keeps its own copy until dispose()
pool.register("by_id", "select name from users where id = $1", [20]);
const fds: i32[] = [-1, -1, -1, -1]; // slot s's socket
const ticket: i32 = pool.request(); // PG_POOL_FULL when 256 wait
// Each turn of the loop: send what waits, then connect what is wanted.
for (let slot: i32 = pool.dispatch(); slot >= 0; slot = pool.dispatch()) {
const conn = pool.connection(slot); // pool.ticket names the request
conn.bindStart("by_id");
conn.paramInt(toI64(42));
conn.bindEnd();
pool.send(); // ends the group: send(), not sync()
}
for (let slot: i32 = pool.wanted(); slot >= 0; slot = pool.wanted()) {
const nonce = new Array<u8>(18);
crypto.getRandomValues(nonce); // a fresh nonce for each connect
pool.connect(slot, nonce);
fds[slot] = tcpConnect(addr); // then pollAdd it under a token naming slot
}
// Then, for each slot s whose socket fds[s] was readable, read it and take
// its events as for one connection, with pool.next(s) and pool.ticket.
-
Requests.
request()queues a request and answers its ticket.dispatch()hands the oldest one to the ready connection with the fewest groups in flight, never more thanPgConfig.maxInFlighton one. WithPgConfig.packset it hands it instead to the first ready connection with room, filling one tomaxInFlightbefore using the next: fewer, fuller writes, which pays on a lossy link to the server. Build its group onconnection(slot)and end it withsend(). Until it is sent,dispatch()answersPG_POOL_NONE: a request handed out and never sent holds the pool.send()answersfalsewhen nothing was handed out, when a bind is still open (finish it and call again), and when no message of the group was written. Then a live connection drops the request, and a failed one keeps it and reports itPG_LOSTwith the rest. A connection is made only when the waiting requests outgrow the ones there are, one for everymaxInFlightof them, up tosize; eachdispatch()that finds no room wants at most one more. -
Statements. A statement given to
registeris prepared on each connection in the same write as its first request, so a request binds it by name at once. Register them all before the firstconnect; after it,registeranswersfalse, as it does for a name or SQL holding a NUL and past 256 statements. -
Events.
next(slot)answers whatPgConnection.next()does, withticketnaming the request. An error fails only its own request's group: the other requests on the connection are answered. -
Failures. When a connection fails,
next(slot)answersPG_LOSTfor each request it took with it, oldest first, thenPG_FAILED: close the socket. After it,next(slot)answersPG_NEED_MOREuntil the slot is connected again, when a request next needs it. A connection that fails before it is ready, while no other is, takes the oldest waiting request with it, so a server that cannot be reached empties the queue rather than holds it. When a socket's connect fails, its read answers 0 or an error other than -11, or its write fails, callconnection(slot).closed(). -
Sockets. The sockets are the program's: it closes one on
PG_FAILED, and at the end.dispose()neither sendsTerminatenor closes a socket; callterminate()on each connection, flush it and close its socket first. -
The password.
dispose()wipes the pool's copy and every connection's;passwordWiped()says whether it is gone.
The constructor panics on a size outside 1 to 1024, on a queueSize
outside 1 to 2^20, and on a maxInFlight over 1024, as well as on what
PgConnection panics on. connect panics on a slot that wants no connection,
and after dispose(); connection and next on a slot outside 0 to
size - 1 or one that never had a connection.
To drive a pool from an HTTP server's route handlers, let PgCarrier own
the sockets: Querying from a route handler.
Batching keyed reads
A server that answers many requests for one row each — a user by id, an
article by slug — can send them as one execution instead of one each. A
PgBatcher queues those reads, and when a connection is free binds up to
maxRequests of them as one statement over an array of their keys:
select t.*, q.ord from unnest($1::int8[]) with ordinality as q(key, ord)
cross join lateral (<select>) as t order by q.ord[, <orderBy>]
<select> is the query for one key, which it reads as q.key:
select a.title from articles a where a.slug = q.key. (Under sorted false, the order by q.ord
goes.) Each distinct key is
bound once however many requests ask for it, and each row goes back to every
one of them. The one exception is keys chosen to collide in the batcher's
table: past 1024 slots of probing a key is bound again rather than found,
and each copy is answered the same. Such keys also cost more per request;
see "Large batches" below.
PG_BATCH_INT keys are bound as a text int8[], one arrayInt each. The
statement's $1::int8[] would take the binary builder
as it is, which the benchmark puts at about a tenth of the client's encoding
CPU and, for a batch of 1,000 keys, a 51% cut in the server's on the unnest;
the batcher does not use it yet.
const config = new PgBatchConfig();
config.name = "article_by_slug";
config.keyType = PG_BATCH_TEXT; // PG_BATCH_INT (the default) binds int8 keys
config.select = "select a.id, a.title from articles a where a.slug = q.key";
config.maxRequests = 64;
const articles = new PgBatcher(config);
pool.register(articles.name, articles.sql, noTypes); // prepare `sql` under `name`
// Per request: the key is copied in, and the tag comes back with its answers.
if (!articles.addText(body, slugStart, slugLength, requestId)) {
// The queue is full (queueSize requests, or queueBytes of text keys), the
// key is longer than maxBytes allows, or it holds a NUL or is not UTF-8:
// answer 503, or 400 for a bad key.
}
// Each pool request is one take, and its events name it by pool.ticket, so
// keep the run each ticket got. Tickets count up, so a ring of more slots
// than requests can be outstanding at once (connections plus queue) holds
// every live one.
const RING: i32 = 512;
const runs: i32[] = new Array<i32>(RING);
// When the loop has a connection for it:
const slot = pool.dispatch(); // after pool.request() while articles.waiting() > 0
runs[pool.ticket % RING] = articles.take(pool.connection(slot)); // a handle, or -1: nothing bound
pool.send();
// For each event: the pool's own name no request (pool.ticket is -1).
const run: i32 = pool.ticket >= 0 ? runs[pool.ticket % RING] : -1;
if (run >= 0) {
if (event === PG_ROW) {
articles.row(run, conn);
} else if (event === PG_COMPLETE) {
articles.end(run);
} else if (event === PG_ERROR || event === PG_LOST) {
articles.fail(run);
}
for (let e = articles.next(run); e !== PG_BATCH_NONE; e = articles.next(run)) {
// articles.tag is the request; for PG_BATCH_ROW, conn's current row is its row.
}
}
next(run) answers, for each request, a PG_BATCH_ROW for each of its rows,
then one last event: PG_BATCH_DONE after at least one row,
PG_BATCH_MISSING when its key had none, or PG_BATCH_FAILED when the
execution failed (the rows it had may be incomplete). Call it until it answers
PG_BATCH_NONE after every row, end and fail, before the connection's
next event: a row's columns are read from the connection, where they last only
until then.
Skipping the ordinal sort
The order by q.ord is what lets a request's last event come early: once a
row for a later key arrives, every earlier key is finished. It costs the
server a Sort node over the whole result. Set sorted to false and the
statement has no order by at all, or order by <orderBy> alone when
orderBy is set:
select t.*, q.ord from unnest($1::int8[]) with ordinality as q(key, ord)
cross join lateral (<select>) as t[ order by <orderBy>]
config.sorted = false; // the default is true: ordered by q.ord, as above
Rows then come in whatever order the plan gives them, and each is answered
at once, a PG_BATCH_ROW for every request of its key. No last event comes
before the execution ends: after end, each request gets PG_BATCH_DONE if
its key had a row or PG_BATCH_MISSING if not, in key order, then request
order. A fail gives every request one PG_BATCH_FAILED, those whose key
already had rows too. So a request holds its rows until the whole batch is
done; choose this when the batch is answered as a whole anyway, or the sort
is what dominates the execution.
A non-empty orderBy still sorts the whole result by it, so the Sort node
stays. To drop every sort, leave orderBy empty and order a key's rows
yourself, as they arrive. The live suite's explain scenario
(tests/postgres/live) checks that the plan has no Sort node.
-
Caps.
maxRequests(32, at most 65536) bounds the requests one execution answers;maxBytes(32 KiB) the key array's text;queueSize(1024) the requests waiting, andqueueBytes(64 KiB) their text keys;runs(16) the executions in flight at once. A cap out of range, an emptynameorselect, or a NUL in them or inorderBypanics in the constructor. -
Large batches. For ordinary keys, a take's cost per request stays flat as
maxRequestsgrows: memory grows with the largest take so far, not with the cap, and finding a key's duplicate stops after 1024 probes, past which the key is simply bound again. Int keys built to share one table hash are the exception: each still costs at most 1024 probes, but from 1,024 to 65,536 such keys the measured time per key rose 2.3× (2.8 µs to 6.4 µs, against a 35% noise band), where spread keys stayed within noise. For a take of thousands of requests, raise the other caps with it:queueSizeto at leastmaxRequests,maxBytesto fit every key's text (about 20 bytes an int8 key), the connection'smaxOutputabovemaxBytes, andmaxMessageif a single row is large. -
Taking fewer.
takebinds the oldest requests first and stops at the first whose key would passmaxBytes, or that the connection refuses for room undermaxOutput; the rest stay queued for the next take. It answers -1 and writes nothing when nothing waits, every run is in flight, or the connection has no room even for the first key.addTextrefuses a key whose escaped text could never fitmaxBytes, but one past what a connection could ever hold stays at the head of the queue. A take that fillsmaxBytesalways fits a connection with nothing else unsent whenmaxBytes + name.length + 32is at most itsmaxOutput; leave more for whatever else the group queues. -
Handles.
takeanswers a handle for the run, not a reused number: a run is free again once all its events are answered afterendorfail, and its next take answers a new handle. A handle that is spent — its run answered, or taken again — makesrow,endandfaildo nothing andnextanswerPG_BATCH_NONE, and counts the call instray; so a server that answers twice, or a connection lost after its answer, cannot touch the run's next batch.Only a caller's mistake panics:
row,endorfailbeforenext(run)answeredPG_BATCH_NONE, a negative handle or one no take could answer,addInton a text batcher, andaddTexton an int8 one or with a window outside its array. -
Ordinals. Rows are routed by the ordinal in their last column, which comes from the server.
rowrefuses one that is NULL, not an integer, past the keys, or before the key the last row was for: it answersfalse, counts it inrefused, and fails the run, so a confused server cannot hand one request's rows to another. -
One key, one column. A key is one
int8or onetext. A composite key — two columns — is out of its scope: encode it into one text key, or useparamTextArrayand your own statement.
The batcher reads no clock. It takes what waits whenever it is asked, so a
loop that calls take as soon as a connection is free sends small batches
when traffic is light and large ones when it is heavy, without waiting. To
collect for a while first, let your loop decide when to ask: take when
waiting() reaches a size you choose, or when the oldest request has waited
longer than a window you time with your own clock, whichever comes first.
const BATCH_WINDOW_MS: i32 = 2;
let asked: boolean = false; // a request is queued and its dispatch has not come
const due = articles.waiting() >= 32 || (articles.waiting() > 0 && nowMs() - oldestAddedMs >= BATCH_WINDOW_MS);
if (due && !asked && pool.request() >= 0) {
asked = true; // and false again on its dispatch, which takes, as above
}
// And pass min(timeout, the window left) to pollWait, so the loop wakes for it.
tests/postgres/live/live.ts's
batch scenario drives three batchers through a pool against a live server.
When to take: PgBatchClock
A fixed window costs latency when traffic is quiet and buys nothing when it
is heavy. A PgBatchClock decides for you, from the time you pass it in
microseconds; it reads no clock and allocates only when it is made. A batch is
due when either
-
at least max(
minCount, the moving average of recent batch sizes ×factor) items wait, or - the oldest has waited
maxWaitUs;
and, with the idle bypass on, at once when nothing is in flight and fewer than
lightCount items arrived in the last lightWindowUs. Under a backlog the
count is already there, so nothing waits on the timer, and a batch started by
count is never smaller than the average, so batches do not shrink while the
backlog lasts.
const timing = new PgBatchClockConfig();
// The defaults are the fixed window: 32 items or 10 ms, whatever the load.
timing.maxCount = config.maxRequests; // the batcher's own cap
timing.queueSize = config.queueSize;
timing.minCount = 16;
timing.factor = 1.1;
timing.lightCount = 8;
const clock = new PgBatchClock(timing);
// Per request, beside addText: the clock mirrors the batcher's queue.
if (articles.addText(body, slugStart, slugLength, requestId)) {
clock.arrived(nowUs());
}
// When the loop has a connection: takeDue takes only when the clock says so.
runs[pool.ticket % RING] = articles.takeDue(pool.connection(slot), clock, nowUs(), pool.inFlight());
// And wake the loop for the timer: deadline() is -1 when nothing waits.
const deadline = clock.deadline();
| Setting | Default | What it does |
|---|---|---|
maxCount |
32 | The most items one batch takes; set it to the batcher's maxRequests. |
minCount |
32 | The fewest that start a batch by count. |
factor |
0 | The share of the recent average that must wait; 0 leaves it out. Above 1, batches grow until the timer bounds them. |
weight |
0.25 | How far each batch moves the average, over 0 up to 1. |
maxWaitUs |
10000 | The longest the oldest item waits, in µs. |
lightCount, lightWindowUs |
0, 10000 | The idle bypass; 0 turns it off. |
queueSize |
1024 | The most items waiting; the batcher's queueSize. |
A setting out of range panics in the constructor. After each start,
average, lastSize, lastWaitUs and lastReason — PG_CLOCK_COUNT,
PG_CLOCK_AVERAGE, PG_CLOCK_TIMER or PG_CLOCK_IDLE — are there for your
metrics, and startsFor(reason) counts the starts for each. takeDue panics
when the clock does not mirror the batcher: its waiting count is not
waiting(), or its maxCount is below maxRequests.
With several connections, PgBatchTurns lets one free connection collect
while the others execute: push(now) records an item and answers the slot to
wake only when its batch is due (PG_TURNS_NONE otherwise, PG_TURNS_FULL
when the queue is), poll(now) does the same at deadline(), and the woken
slot calls started(slot, size, now) or release(slot), then
finished(slot, now) when its batch is done.
The fixed window stays the default until measurements against a live server
say otherwise. bench/pg-batch-clock.ts
models a pool at three loads; there, the adaptive settings above match the
better of a 10 ms and a 30 ms window at each.
Querying from a route handler
nish/net/postgres-carrier puts a pool on an
HttpHandler's loop. PgCarrier connects the pool's
slots, reads and writes their sockets, and watches them from the handler's
own wait, so a route handler hands its request to the carrier and goes on
serving: a slow query holds only the requests waiting on it. It is a module
of its own because it reads sockets, the clock and the entropy source, and
nish/net/postgres reaches none of them.
import { HandlerLimits, HttpHandler, HttpRequest } from "nish/net/handler"
import { PgBatchConfig, PgBatcher, PgConfig, PgConnection, PgPool, pgTarget } from "nish/net/postgres"
import {
PG_CARRIER_BUILD, PG_CARRIER_DONE, PG_CARRIER_FAILED, PG_CARRIER_FULL,
PG_CARRIER_MISSING, PG_CARRIER_NONE, PG_CARRIER_ROW, PgCarrier, PgCarrierLimits,
} from "nish/net/postgres-carrier"
const pool = new PgPool(config, password, 4, 256)
pool.register("article", "select body from articles where id = $1", [toI32(20)])
const handler = new HttpHandler(new HandlerLimits(), listener, router)
const carrier = new PgCarrier(new PgCarrierLimits(), pool, pgTarget("127.0.0.1", 5432), handler)
const users: i32 = carrier.attach(new PgBatcher(usersConfig))
// Whether query q's request was answered: a ROW answers it, and the final
// event answers only one that no row did.
const answered: boolean[] = new Array<boolean>(256)
while (running) {
handler.wait(50)
while (handler.ready()) {
const req: HttpRequest = handler.take()
const q: i32 = req.route === USER
? carrier.runInt(req, users, toI64(req.paramDecimal(0)), USER)
: carrier.run(req, ARTICLE)
if (q === PG_CARRIER_FULL) {
handler.send(req, 503, text, none, 0, 0)
}
}
let event: i32 = carrier.next()
while (event !== PG_CARRIER_NONE) {
if (event === PG_CARRIER_BUILD) {
const conn: PgConnection = carrier.connection()
conn.bindStart("article")
conn.paramInt(toI64(carrier.request().paramDecimal(0)))
conn.bindEnd()
carrier.send()
} else if (event === PG_CARRIER_ROW) {
if (!answered[carrier.query]) {
const conn: PgConnection = carrier.connection()
handler.send(carrier.request(), 200, text, conn.inputBuffer(), conn.colStart(0), conn.colLength(0))
answered[carrier.query] = true
}
} else if (event === PG_CARRIER_DONE || event === PG_CARRIER_MISSING || event === PG_CARRIER_FAILED) {
if (!answered[carrier.query]) {
handler.send(carrier.request(), event === PG_CARRIER_FAILED ? 503 : 404, text, none, 0, 0)
}
answered[carrier.query] = false
}
event = carrier.next()
}
}
carrier.close()
-
run(req, tag)queues a plain query and answers its number at once, orPG_CARRIER_FULLwhen the carrier holdsqueriesalready. When a connection takes it,next()answersPG_CARRIER_BUILD: bind the statement onconnection()and callsend()before the nextnext(). -
runIntandrunTextqueue a key for a batcherattachgave you (at most 8, attached before the pool connects). The carrier takes a batch whenever a connection is free and answers each key's rows to its own request; a key with no row isPG_CARRIER_MISSING. -
Events. A query's final event comes whether or not it had rows, so
answer the request once: on its first row, or on the final event when no
row came. Each query gets its rows (
PG_CARRIER_ROW, read onconnection()before the next call), then exactly one ofPG_CARRIER_DONE,PG_CARRIER_MISSINGandPG_CARRIER_FAILED. On each,queryis the numberrunanswered,tagwhat you passed, andrequest()the request to answer; they hold until the followingnext(), which frees the query. -
Failures. A connection that is refused, reset, closed or not
authenticated within
connectTimeoutMs(5000 by default) fails every query on it withPG_CARRIER_FAILED:failureis the connection'sPG_ERR_*(9, closed, for a socket that failed) andsocketErrorthe socket's error,-110for the timeout. The next query makes a new connection. While no connection is up, each failed connect also fails the oldest waiting query, one per slot per turn, so givewaita timeout: a server that cannot be reached empties the queue over the following turns. A query the server refuses fails withfailure0 and its error onconnection().lastError, afterPG_CARRIER_ERROR. -
Caps.
PgCarrierLimitssetsqueries(256) andexecutions(64), each at most 65536, before the carrier starts; nothing grows after, and once the connections are up nothing is allocated per query.watchis the id the carrier is watched under on the handler (0 to 15), if your program watches other descriptors. - The target is a Unix socket or a numeric address: the carrier resolves no names.
There is no deadline on a query once its connection is up: bound a slow
statement with the server's statement_timeout. close() closes the
carrier's sockets; the pool's dispose() still wipes the password. Queries
in flight at close() get no further event, so answer their requests
yourself. After it, run, runInt and runText answer PG_CARRIER_FULL
and next() answers PG_CARRIER_NONE, and a second close() does nothing.
The constructor panics on a cap or watch id out of range, a target that failed or a host
name, TLS on a Unix socket, or PG_TLS_VERIFY_FULL with no root certificate to read; attach past 8 batchers, after the pool connected, on a NUL in the
batcher's name or SQL, or past 256 statements; runInt and
runText on an id attach never gave; request() for a query run with no
request; connection() before an event named one; send() with no build
open; and next() with one still open.
tests/postgres/carrier.ts is a whole
program that serves HTTP while a query sleeps on a live server.
Over TLS
The carrier can speak TLS 1.3 to the server. Set PgConfig.tls before the
pool is made:
import { PG_TLS_VERIFY_FULL, PgConfig } from "nish/net/postgres"
const config = new PgConfig()
config.user = "app"
config.database = "app"
config.tls = PG_TLS_VERIFY_FULL
config.tlsRootsPath = "/etc/app/db-ca.pem" // "" reads the system's store
config.tlsServerName = "db.internal" // "" checks the target's host
tls |
What it does |
|---|---|
PG_TLS_DISABLE (the default) |
No TLS: the connection is in the clear, as before. |
PG_TLS_REQUIRE |
TLS or no connection, with the certificate unchecked: it stops an eavesdropper, not a machine in the middle. |
PG_TLS_VERIFY_FULL |
TLS, with the server's chain checked to a root in tlsRootsPath and its certificate checked for tlsServerName. |
Use PG_TLS_VERIFY_FULL whenever the database is across a network you do
not control; it is libpq's sslmode=verify-full. The default stays
PG_TLS_DISABLE so that a program connecting in the clear today keeps
connecting, and a Unix socket, where TLS buys nothing, keeps working; TLS on
a Unix socket target is a panic. Since the carrier resolves no names, a
target is an address: set tlsServerName to the name on the server's
certificate when it does not carry the address.
The carrier sends PostgreSQL's SSLRequest, and a server that answers no
fails each query with PG_ERR_TLS and carrier.socketError 0. A handshake
that fails is PG_ERR_TLS too, with socketError the TLS alert the
carrier sent: 48 for a certificate under a root you did not give, 42 for
one that names another host. The server needs ssl = on and a certificate
and key, and must speak TLS 1.3, which the carrier requires: PostgreSQL
built with OpenSSL 1.1.1 or later does.
Only the carrier speaks TLS: the async pool
and a PgPool you drive yourself do not, and neither does a client
certificate. Rather than connect in the clear, they refuse a PgConfig that
asks for TLS: the PgConnection constructor, pgAsyncConnect and
PgPool.connect on a pool no carrier drives panic on a tls other than
PG_TLS_DISABLE.
Awaiting a query from a task
nish/net/postgres-async is a pool for an async program: a
spawned task awaits its query, and while it waits on the socket the executor
runs the program's other tasks. It is three calls, not one, because a spawned
task may write only numbers into what it shares: pgAsyncClaim and
pgAsyncRelease mark a connection taken and free, and pgAsyncQuery builds
its result in the task's own memory.
import { spawn } from "nish:async"
import { secret, wipe } from "nish:secret"
import { PgConfig, pgTarget } from "nish/net/postgres"
import {
PgAsyncParams, PgAsyncPool, pgAsyncClaim, pgAsyncConnect, pgAsyncQuery, pgAsyncRelease,
} from "nish/net/postgres-async"
const title = async (pool: PgAsyncPool, id: i32): Promise<void> => {
const ticket: i32 = await pgAsyncClaim(pool)
if (ticket < 0) {
return // -1: no live connection; -2: none came free within claimWaitMs
}
const params = new PgAsyncParams()
params.int(id)
const r = await pgAsyncQuery(pool, ticket, "select title from article where id = $1", params)
pgAsyncRelease(pool, ticket, r.reusable)
if (r.ok() && r.rows > 0) {
console.log(r.text(0, 0))
}
}
export const main = async (): Promise<i32> => {
const config = new PgConfig()
config.user = "app"
const pool = new PgAsyncPool(4)
const password = secret(passwordBytes())
const live: i32 = await pgAsyncConnect(pool, config, password, pgTarget("127.0.0.1", 5432))
wipe(password)
spawn(title(pool, 1))
spawn(title(pool, 2))
// ... wait for the tasks, then pool.close()
return live === pool.size ? 0 : 1
}
-
Run each query in a spawned task. The task's memory, and every buffer,
row and string its query built, is freed when the task ends, so a server
that runs thousands of queries stays the same size. A query awaited in
mainkeeps about 9 KB untilmainreturns. -
Claim, query, release, once each. A ticket names one connection and one
claim of it. Give it to
pgAsyncReleaseexactly once, with the result'sreusable: a second release, or a ticket from beforeclose(), panics, so two tasks never share a connection. -
Waiting for a connection. When all are claimed,
pgAsyncClaimwaits on the pool's wake, which a release rings, and answers -2 onceclaimWaitMs(5000 by default) has passed. -
A result.
ok()is true when the server ran the statement;failureis thePG_ERR_*code (0 witherrorCodeanderrorMessagefor an error the server sent, 8 for a NUL in the SQL or a text parameter, 9 for a lost socket, withsocketError).rowsandcolumnscount what came back; read a cell withtext,intorisNull, which panic outside the result. -
Caps.
maxMessage(1 MiB) bounds one message,maxColumns(1664) a row,maxRows(100000) andmaxResultBytes(16 MiB: each row's bytes and 16 a cell, each command tag's and error's bytes) a result. Rows, tags and errors pastmaxRowsormaxResultBytesare read and dropped, and the query fails 2 (PG_ERR_TOO_BIG) with the rows that fit.maxMessageandmaxResultBytestake 1024 to 2^30-1,maxColumns1 to 65535 andmaxRows1 to 2^24;pgAsyncQuerypanics on a cap outside its range. -
Reuse. A connection goes back to the pool only when its query ended
idle, changed no setting and left no bytes after its answer. A
transaction left open, a
SET, a lost socket, a frame no query expects, a message pastmaxMessageor a row pastmaxColumnscloses it, so no task inherits another's session.pgAsyncConnectfills the slots that have no connection; call it again whenlive()falls. - The target is a Unix socket or a numeric address: the pool resolves no names. Parameters go in text, never spliced into the SQL.
pgAsyncConnect gives each connection connectWaitMs (10000 by default) to
connect and finish its startup, and leaves a slot empty past it; connects
counts the connections it made. A query's wait has no deadline of its own: a
server that accepts and never answers holds the task awaiting it until the task is cancelled, by cancelAfter in
nish:async or by a server's deadline. A cancelled claim answers -3 and
holds nothing; a cancelled query fails with socketError -3 and its
connection is closed, since its answer would reach nobody, and the pool
dials another. The server is sent no CancelRequest, so bound a slow statement
with its statement_timeout as well. The constructor panics on a size outside 1..64, and
pgAsyncConnect on a target that failed or a host name.
A handler of nish/net/serve
reaches the pool the same way: written as an arrow in main, it captures
pool, and each request's task claims, queries and releases, then answers
with a cell of the result, which is the task's own memory:
return await serve(8080, router, new ServeLimits(), async (req: Request): Promise<Response> => {
const ticket: i32 = await pgAsyncClaim(pool)
if (ticket < 0) {
return new Response(503).text("no connection\n")
}
const params = new PgAsyncParams()
params.int(req.paramNumber(0))
const r = await pgAsyncQuery(pool, ticket, "select title from article where id = $1", params)
pgAsyncRelease(pool, ticket, r.reusable)
return r.ok() && r.rows > 0 ? new Response(200).text(r.text(0, 0)) : new Response(404)
})
Pipelining statements from many tasks
A claim holds a connection for one task's whole round trip. For short
statements, let tasks share connections instead: register each statement once
with pgAsyncPrepare, then each task takes a seat on a connection, sends,
waits for its turn and reads its answer. Up to depth (8 by default, at most
16) statements from different tasks are in flight on one connection at once,
and each is parsed once a connection, then only bound and executed:
import { spawn } from "nish:async"
import {
PgAsyncParams, PgAsyncPool, pgAsyncAnswer, pgAsyncDone, pgAsyncPrepare, pgAsyncSeat, pgAsyncSend,
pgAsyncTurn,
} from "nish/net/postgres-async"
const title = async (pool: PgAsyncPool, statement: i32, id: i32): Promise<void> => {
const ticket: i32 = await pgAsyncSeat(pool, statement)
if (ticket < 0) {
return
}
const params = new PgAsyncParams()
params.int(id)
const sent: i32 = await pgAsyncSend(pool, ticket, params)
const turn: i32 = await pgAsyncTurn(pool, ticket, sent)
const r = await pgAsyncAnswer(pool, ticket, turn)
pgAsyncDone(pool, ticket, turn, r.reusable)
console.log(r.ok() && r.rows > 0 ? r.text(0, 0) : "none")
}
export const main = async (): Promise<i32> => {
const pool = new PgAsyncPool(4)
const byId: i32 = pgAsyncPrepare(pool, "select title from article where id = $1")
// ... pgAsyncConnect as above ...
for (let id: i32 = 1; id <= 100; id++) {
spawn(title(pool, byId, id))
}
return 0
}
-
Five calls, in order, in the task.
pgAsyncSeatfixes the statement's place in the connection's order, so the task makes the other four calls straight after it, each once, with no otherawaitbetween.pgAsyncDonespends the ticket; a ticket used after it, or a statement numberpgAsyncPreparedid not give, panics. -
Answers come back in order. A statement the server refuses fails
alone and the ones behind it are answered. A task cancelled before its
answer is read fails alone too (
socketError-3): the next task reads past that answer. A connection lost, sent a frame past a cap, or left out of step (a cancelled task's answer that left a transaction open or changed a setting) fails every statement in flight on it withPG_ERR_CLOSEDand is closed. A slow statement delays the ones seated behind it: keep long or transactional work onpgAsyncClaim. -
Waiting for a seat. When every connection has
depthin flight,pgAsyncSeatwaits on the pool's wake, which an answered statement rings, and answers -2 afterclaimWaitMs; -1 when none is connected. Waiting tasks are seated in the order they began to wait, so a task that keeps asking cannot take every seat that frees; seats freed together go to as many waiting tasks in one turn of the loop.
What it does not do
TLS outside the carrier, COPY, LISTEN/NOTIFY (a notification is read and dropped), a CancelRequest
for a cancelled query, binary-format parameters other than int4[] and int8[],
binary results for a type other
than an integer, bool, float8, time, text or a one-dimensional int2[],
int4[] or int8[], so a text[], a numeric and an array of more than
one dimension stay in text, a mix of text and binary columns in one execution,
and the simple query protocol. A pool
neither reads a clock nor retries: a request waits in its queue until a
connection takes it, and one lost with its connection is the program's to run
again.
A target names one host: there is no list of hosts to try in turn, and no
default socket directory for a URL with no host, which libpq would use.
The user, password and database in a URL are not read by pgTargetUrl; set
them on the PgConfig.
It does not insist on authentication: a server that answers
AuthenticationOk without asking for a password is believed, as
libpq does without require_auth. conn.text.timestamp checks a
timestamp's shape and year but not its month, day or time, which it takes on
the server's word.

