porsager/postgres v4.1.0 on GitHub

New

query_timeout (#1278). Seconds a query may take, waiting for a connection and running together, before it is canceled and rejects with QUERY_TIMEOUT. Off by default. Unlike statement_timeout it also ends a query whose server or network has gone silent, which TCP alone takes many minutes to notice. Transaction control queries and forEach are limited too; cursors, streams and sql.listen are not.
cancel_timeout, 5 seconds by default: how long a canceled query may take to end before its connection is closed.
sql.batch([queries]) sends queries in one round trip with a single Sync: outside a transaction they commit or roll back together, and the batch rejects with the failing query's position in error.index.
json on a transform (#1169). The built-in transforms rename the keys of JSON values in results but send JSON values as they are. json: false leaves JSON values as they are both ways, and json: true renames their keys when sending too, with the transform's column.to. Without it nothing changes.
eager_types: true reads array types and domains when the pool starts instead of when a query first meets one.
sql.identifier(...parts) (#1292). An identifier from its parts, each quoted as it is and joined with ., for a name that contains a dot, which sql(name) reads as a separator: sql.identifier('acme.inc', 'users') gives "acme.inc"."users". The transform is not applied. With no parts it throws NO_PARTS.
Node.js 18 is supported again: engines is >=18 and CI runs it. Nothing in Postgres.js needs 20; using, await using and tracing are used where the runtime has them. Loading it with require still needs Node.js 20.19 or 22.12.
sql`...`.parsers(parsers) (#1294). A query's own parsers, for its rows only: by type, { [oid]: parse }, on top of the instance's types, or a function that gets each column and returns the parser for it, null for the server's text. A library that knows how to decode some queries' columns can decode them once, inside the row reader, while every other query on the instance and its pool keeps the instance's parsers. Such a query receives every column as text, so bytea and the integer arrays otherwise read in binary reach its parsers as text. A query without .parsers() reads its rows as before.

Fixed

sql.listen keeps its connection after the last unlisten() (#1298). The connection was closed so the process could exit (#1022), which made every following listen() connect again: about 3.5 ms instead of 0.1 ms per listen. It now stays open without keeping the process alive once nothing is listened.
PGSSLMODE is read (#1307). It was ignored, so PGSSLMODE=verify-full connected in plain text without an error. This may change how existing setups connect: require now uses TLS and fails against a server without it, and verify-ca and verify-full check the certificate against Node's CAs, not ~/.postgresql/root.crt or PGSSLROOTCERT. A url or an ssl option still comes first.
An insert helper after a quoted schema and an unquoted table builds the insert (#1304). In insert into ${ sql('s') }.t ${ sql(row) } or insert into "s".t, the quoted schema and the table were read as two words, so the row was taken for a list of identifiers and threw x.replace is not a function.
sql.listen keeps reconnecting when its first reconnect fails (#1285, #1286). After the listen connection closed, it was reopened once, and if that attempt failed, as it does while a restarting server still refuses connections, nothing tried again, so onlisten was never called and notifications were lost. A failed reconnect is now retried after backoff, until it succeeds, the channel is unlistened or the pool ends.
A canceled query whose server never answers no longer waits forever. .cancel(), .signal() and now query_timeout send a cancel request and waited for the server to end the query, so a server or network gone silent left both the query and the promise .cancel() returns pending until TCP gave up. This applies to every cancel, also without query_timeout: when the server doesn't answer within cancel_timeout, the connection is closed and the pool connects again. Whether the queries on it ran is unknown, so they reject with CONNECTION_DESTROYED (a timed out one with QUERY_TIMEOUT) whose cause is CANCEL_UNANSWERED, not with 57014. The cancel request's own connection is limited by cancel_timeout too, and a cancel is no longer sent for a query whose result has already arrived, where it could only hit the query after it. A cancel request whose own connection fails, as over TLS to a server gone silent, no longer crashes the process with an unhandled rejection. A query that times out while it waits behind another one on its connection is canceled only after it has had its own query_timeout once its turn comes, instead of the moment its turn comes, when it had usually finished and the cancel stopped the next query; if the server sends nothing for cancel_timeout while it waits, the connection is closed. A query waiting for types to be read rejects with QUERY_TIMEOUT without closing its connection, and the read of array types and domains is limited by query_timeout too.
sslrootcert, sslcert and sslkey in a url are used (#1308). They were sent to the server as startup parameters, so ?sslrootcert=system failed with unrecognized configuration parameter. Files are read into ca, cert and key. As in libpq, a root certificate with require or verify-ca checks the CA but not the host name, and verify-full checks both, as does ssl: true. A file without an sslmode means require.
A transaction the server ends rejects with the server's error (#1283, #1284). When a transaction_timeout (25P04), an idle_in_transaction_session_timeout (25P03) or pg_terminate_backend (57P01) ended the session, sql.begin rejected with CONNECTION_CLOSED, so a timeout or a termination looked the same as a network drop; it now rejects with the FATAL error the server sent before closing, as a query in flight already did, and a later query on that connection rejects with CONNECTION_CLOSED that has it as cause. A server restart or failover ends sessions with 57P01, so code that retries a transaction on CONNECTION_CLOSED should retry on that code too. A FATAL in answer to COMMIT rejects with CONNECTION_CLOSED that has it as cause, as the outcome is unknown, as it is when the connection is lost during COMMIT.
A server certificate is verified against an IP host (#1309). With ssl: 'verify-full' or ssl: true and an IP as the host, a connection upgraded to TLS checked the certificate against localhost, so a certificate for localhost from a trusted CA passed for any IP and a correct certificate for the IP failed. Direct TLS was not affected.
A savepoint or nested begin rejects when its transaction's connection is lost (#1281, #1282). One whose callback was still running when the connection went resolved with the callback's result after the transaction had already rejected; it now rejects with the transaction's error.
A connection closed while the pool reads its array and domain types counts as a failed connection attempt (#1279). Reconnecting follows backoff and waiting queries reject once connect_timeout has passed, instead of reconnecting as fast as the server accepts for as long as it keeps closing. With connect_timeout set to 0, null or false the pool now keeps trying for as long as it takes, as the option says, also after a refused or closed startup, which used to reject the waiting queries at once.
A query failed by a FATAL error gives that error to the queries pipelined behind it as well, instead of CONNECTION_CLOSED.
A FATAL error with the severity only in the S field is recognised as one. The non-localized V field is optional in the protocol: PostgreSQL sends it since 9.6, and a pooler that builds its own errors, such as PgBouncer, may send only the localized S. Such an error was not seen as FATAL, so the query, a sql.begin and the queries pipelined behind it rejected with CONNECTION_CLOSED without a cause instead of with the error. error.severity now falls back to severity_local when V is missing.
Each query on a connection that closes gets its own error. They shared one error object, so every query pipelined on the connection reported the first one's query and parameters.
A cached prepared statement no longer keeps the query that prepared it (#1287). Each connection's entry for a prepared statement kept that query's values and rows for as long as the statement stayed cached, up to 1,000 statements per connection, so an app with many distinct statements grew by their results until every cache was full: about 1.1 GB with 10 connections and 50-row results. They are let go once the query settles. On 4.0.0, prepare: false avoids it.
Destroying a copy ... to stdout stream frees the connection (#1306). After destroy(), breaking out of for await or a failed pipeline, the connection stayed paused for good and the queries behind it never ran. The rest of the copy is now read and dropped, so the connection is free once the server has sent it. The query is not canceled, so in a transaction the next query still succeeds.

Performance

An ssl object with a ca builds its TLS context once (#1280). The connections made with it share one context instead of each parsing the whole ca list again, so with a provider's CA bundle a new connection takes about half the time and CPU, and a burst of them no longer blocks the event loop. Replacing one of the object's values, as with a renewed cert, builds a new context for the connections after it.
A new statement whose untyped values are all numbers or null is prepared, described and run in one round trip, as one whose parameter types are all known is, since those values are written the same for any type the server picks; one with other values only the server can type is still described first, once per connection.
Array types and domains are read when a query first meets a type the pool does not know (an oid from 16384 up), instead of when the pool starts: a pool that only uses built-in types never sends that query, and a new pool's first query costs no more than any other. The connection that met the type waits for it, read on another connection. With custom types given by name they are still read at startup, as their oids are needed before the first query. Leases and values typed as a type the pool hasn't read yet still wait for it. A read whose connection fails is retried after backoff, and once connect_timeout has passed the connections waiting for it are closed and their queries reject, as for a failed startup. eager_types: true reads them when the pool starts, as before.
timestamptz, and timestamp and date read as UTC, are decoded straight from the bytes when they use the built-in parsers, with the text parser for anything else (BC, infinity, years before 1000); about 20% more rows per second for rows with timestamps.
bytea and arrays of int2, int4, int8 and bool are fetched in binary once a prepared statement's columns are known on the connection, when they use the built-in parsers: the values are the same as from the text, about 4.5 times faster to read for bytea and 3 times for an int4[]. A custom parser for the type or its elements, and raw(), still get the text.
A connection that becomes ready while others are still opening no longer takes the queries meant for those; 50 connections opened at once each run one of 50 queries.