Postgres.jl

Postgres.Connection

A single connection to a PostgreSQL server, created via DBInterface.connect(Postgres.Connection, ...):

DBInterface.connect(Postgres.Connection, host, user, password; dbname, port=5432, kwargs...)
DBInterface.connect(Postgres.Connection, dsn::String; kwargs...)
DBInterface.connect(Postgres.Connection, params::ConnectionParams; kwargs...)

dsn may be a libpq-style keyword string ("host=127.0.0.1 user=postgres dbname=postgres") or a PostgreSQL URI ("postgresql://user:pass@host:5432/dbname?sslmode=require").

Supported keyword arguments. All are also available as DSN/URI options except style, which is Julia-only:

• dbname, port, application_name
• connect_timeout (seconds), statement_timeout (milliseconds)
• sslmode ("disable", "prefer" (default), "require", "verify-full"), sslrootcert, sslcert, sslkey, sslcapath, and sslservername. Only verify-full verifies the server's certificate; require encrypts without authenticating the server, and the default prefer falls back to an unencrypted connection if the server declines TLS. sslcapath is a fallback CA bundle or directory used only when sslrootcert is unset (it is ignored otherwise). sslservername overrides the TLS server name when the host is a pre-resolved address — note that under verify-full this is also the name the certificate is verified against, so it must name the server you intend to authenticate.
• statement_cache_maxsize: LRU backend cache size for explicit named prepared statements (default 100; 0 disables)
• reconnect: automatically reconnect and re-prepare statements if the connection is found dead (default false; never reconnects mid-transaction)
• numeric_overflow: :warn returns numerics that cannot fit DecimalValue{Int256} as text with a warning; :error throws. Typed decimal results always require an exact conversion. This also applies to numeric array elements and range bounds.
• style: a custom AbstractPostgresStyle for query logging / notice / notification behavior
• debug: log wire protocol messages. Authentication messages are redacted, but bind parameter values are not — treat a debug log as sensitive as the data the connection carries.

Individual operations are serialized on an internal lock. Keep each manual transaction and streaming cursor on one task and do not run unrelated work on that connection until the scope ends. Use a ConnectionPool for concurrent work. cancel_query! is the intentional cross-task exception. Close with DBInterface.close!(conn) or close(conn); the do-block form DBInterface.connect(f, Postgres.Connection, ...) closes automatically.

Postgres.ConnectionPool

A pool of Connections, created lazily up to limit and reused across acquire/release cycles. Locally closed connections are replaced. A peer close that the local socket has not observed can surface on the borrower's first operation; an ambiguous failed operation is never retried automatically.

ConnectionPool(Postgres.Connection, host, user, password; limit=10, kwargs...)
ConnectionPool(dsn::String; limit=10, kwargs...)
ConnectionPool(params::ConnectionParams; limit=10, kwargs...)
ConnectionPool(connector::Function; limit=10)

Prefer with_connection over manual acquire/release. Close all pooled connections with DBInterface.close!(pool).

Postgres.PostgresInterfaceError <: Exception

A client-side error raised by Postgres.jl itself (closed connections, parameter-count mismatches, unsupported features, ...), as opposed to Postgres.Error, which represents an error reported by the server.

Postgres.acquire(pool; forcenew=false) -> Connection

Take a connection from the pool, creating one if none is available (blocking if the pool is at its limit). Return it with release.

Postgres.cancel_query!(conn)

Send a PostgreSQL CancelRequest for the query currently running on conn (over a separate, short-lived connection, so it works while conn is busy). The cancelled query fails with a Postgres.Error with SQLSTATE 57014.

The cancel connection uses the same TLS settings as conn, since the cancel key it carries is a credential: if conn itself is on TLS, the cancel connection requires TLS too. Throws a PostgresInterfaceError if the cancel request could not be delivered (rather than failing silently, which would leave the query running).

Postgres.clear_statement_cache!(conn)

Close all server-side prepared statements in the connection's cache and empty it.

Postgres.command_tag(result) -> Union{String, Nothing}

The PostgreSQL command completion tag for the executed statement, e.g. "SELECT 5", "INSERT 0 2", or "UPDATE 3".

Postgres.commit(conn)

Commit the current transaction (or release one level of transaction nesting).

Postgres.copy_from(conn, sql, data)

Execute a COPY ... FROM STDIN statement, streaming data (an IO, string, or byte vector) to the server. Supports all COPY formats, including (FORMAT BINARY). Returns conn.

Postgres.copy_to(conn, sql, [dest::IO])

Execute a COPY ... TO STDOUT statement. With a dest IO, the copy stream is written to it and dest is returned; without one, the raw bytes are returned as a Vector{UInt8}.

Postgres.cursor(conn, sql, params=nothing; fetchsize=1000) -> Cursor
Postgres.cursor(stmt, params=nothing; fetchsize=1000) -> Cursor

Execute a query and stream its result rows in batches of fetchsize instead of materializing them all at once. The returned cursor iterates rows; close it with DBInterface.close!(cursor). A cursor requires a transaction: one is started (and committed on close) if the connection isn't already in one.

Postgres.describe(conn, table; schema="public")

Return a printable summary of a table's columns: name, type, nullability, default, primary-key flag, and foreign-key reference.

Postgres.escape_identifier(name) -> String

Quote a string for use as a SQL identifier (double-quoted, embedded quotes doubled). Throws if name contains a NUL byte.

Postgres.escape_literal(val) -> String

Quote a string for use as a SQL literal (single-quoted, embedded quotes doubled). Throws if val contains a NUL byte.

Prefer query parameters ($1, $2, ...) over literal interpolation whenever possible — parameters are never parsed as SQL. Inputs containing a backslash use PostgreSQL's explicit escape-string syntax, with backslashes and quotes escaped so the result is independent of standard_conforming_strings.

Postgres.get_cached_statements(conn) -> Dict{String, Statement}

Return a copy of the connection's prepared-statement cache, keyed by SQL text.

Postgres.get_server_parameter(conn, name) -> Union{String, Nothing}

Return the server-reported value of runtime parameter name (e.g. "server_version", "TimeZone"), or nothing if the server has not reported it.

Postgres.get_server_parameters(conn) -> Dict{String, String}

Return a copy of all runtime parameters the server has reported on this connection.

Postgres.get_statement_timeout(conn) -> Union{Int, Nothing}

Return the statement timeout (milliseconds) configured on the connection, or nothing if none was set.

Postgres.in_transaction(conn) -> Bool

Whether the connection currently has an open transaction.

Postgres.listen!(conn, channel)

Execute LISTEN channel so the connection receives notifications for channel. Use wait_for_notification to block until one arrives.

Postgres.notify!(conn, channel, payload=nothing)

Execute NOTIFY channel (with optional payload), delivering a Notification to all connections listening on channel.

Postgres.register_composite!(conn, name; schema="public")

Look up the composite type schema.name on the server and register it so values are returned as NamedTuples with the composite's field names.

Postgres.register_enum!(conn, name; schema="public", julia_type=Symbol)

Look up the enum type schema.name on the server and register it so values are returned as julia_type (by default Symbol). The supported types are Symbol and String.

Postgres.register_range!(conn, name; schema="public")

Look up the range type schema.name on the server and register it so values are returned as PostgresRange of the range's element type.

The element type is captured when the range is registered, so register it first: a range over a custom enum or composite must come after the corresponding register_enum! / register_composite! call.

Postgres.register_type!(conn, oid, julia_type; parser=nothing)

Register a mapping from PostgreSQL type oid to julia_type in the connection's type registry. parser is a (val::String, registry) -> value function that converts the wire text. If parser is omitted, julia_type must be String. See also register_enum!, register_composite!, and register_range!.

Postgres.release(pool, conn)

Return a connection previously taken with acquire to the pool. A connection still inside a transaction is rolled back first, so the next borrower starts from a clean session; if it can't be rolled back it is closed rather than reused.

Postgres.rollback(conn)

Roll back the current transaction (or, in a nested transaction, roll back to the enclosing savepoint).

Postgres.rows_affected(result) -> Union{Int, Nothing}

The number of rows the statement affected (parsed from the command tag), or nothing when the statement doesn't report one.

Postgres.set_statement_cache_maxsize!(conn, maxsize)

Set the maximum number of prepared statements the connection caches (LRU eviction). 0 disables caching and closes all currently cached statements.

Postgres.set_statement_timeout!(conn, timeout)

Set the server statement_timeout for the connection, in milliseconds. nothing or 0 disables the timeout. This setting is session state and cannot be changed while a transaction is open. An explicit disable is retained across automatic reconnects as 0.

Postgres.start_transaction(conn)

Begin a transaction (BEGIN). If a transaction is already open, create a savepoint instead, so transactions nest. Pair with commit or rollback; prefer transaction or @transaction for automatic handling.

Postgres.transaction(f, conn)

Run f(conn) inside a transaction: committed if f returns normally, rolled back if it throws. Nested calls use savepoints. Returns f's result.

A transaction already opened with raw SQL (execute(conn, "BEGIN")) is treated as the enclosing level: the block nests inside it with a savepoint and leaves it open, so the caller's own COMMIT/ROLLBACK stays in control of their transaction.

Postgres.transaction(conn) do conn
    DBInterface.execute(conn, "INSERT INTO t VALUES (1)")
end

Postgres.unlisten!(conn, channel)

Execute UNLISTEN channel to stop receiving notifications for channel.

Postgres.wait_for_notification(conn; timeout=nothing) -> Union{Notification, Nothing}

Block until a NOTIFY message arrives on the connection (see listen!) and return it as a Notification. With a timeout (seconds), return nothing if no message begins arriving in that window; once a message starts, it is always read to completion so the connection is never left parked mid-message. The connection lock is held while waiting, so use a dedicated connection for listening — that is also the only way to receive every notification, since notifications that arrive while the connection is busy with a query are delivered to notification_callback only during the phases of a query that read result data.

Over TLS the poll interval bounds a read on the underlying transport rather than on the TLS record layer, so a record that arrives split across a poll boundary cannot be resumed. That is detected on the following poll and closes the connection with an error rather than returning corrupt data; a blocking wait (no timeout) is not affected.

Postgres.with_connection(f, pool; forcenew=false)

Acquire a connection from the pool, call f(conn), and release the connection back to the pool afterwards. Returns f's result.

Postgres.@transaction conn expr

Run expr inside a transaction. Any non-exceptional exit commits: normal completion, return (which then returns from the enclosing function), break, or continue. Only a thrown exception rolls back. Evaluates to expr's value. Nested @transaction blocks use savepoints, and an early return commits every enclosing level on its way out.

The body keeps plain Julia semantics: a return inside a nested function, closure, do-block, or any task-forming macro (Threads.@spawn, @async, Distributed.@spawnat, third-party equivalents) belongs to that function or task, exactly as it would outside the macro.

Postgres.Error <: Exception

A PostgreSQL server error (an ErrorResponse message). Carries the fields the server reported: severity (non-localized when the server supplies it, so it can be compared against "FATAL", "ERROR", ... regardless of the server's lc_messages), code (the SQLSTATE, e.g. "23505"), message, and optional context such as detail, hint, position, schema, table, column, and constraint. A small number of protocol-level failures detected client-side (unsupported authentication methods, protocol desync) also use this type, with an empty code; other client-side failures throw Postgres.PostgresInterfaceError.

Postgres.Notification

An asynchronous NOTIFY message received from the server, with the notifying backend's pid, the channel name, and the payload string (empty when the notification had no payload). See Postgres.listen! and Postgres.wait_for_notification.

Postgres.PostgresRange{T}

A PostgreSQL range value (int4range, numrange, tstzrange, ...). lower and upper are the bounds (missing when unbounded), lower_inclusive and upper_inclusive indicate whether each bound is inclusive, and empty is true for the empty range.

Postgres.ConnectionParams(; host="localhost", port=5432, user="", password=nothing,
                          dbname="", kwargs...)

Structured connection options, an alternative to DSN strings:

params = Postgres.ConnectionParams(host="127.0.0.1", user="postgres", dbname="postgres")
conn = DBInterface.connect(Postgres.Connection, params)

Also produced by Postgres.parse_dsn. Supported keyword arguments mirror the connection keywords: application_name, connect_timeout, sslmode, sslrootcert, sslcert, sslkey, sslcapath, sslservername, statement_timeout, statement_cache_maxsize, debug, and reconnect. numeric_overflow accepts :warn (return out-of-range numeric values as text with a warning) or :error (throw). DSN strings use warn or error without the colon.

Postgres.parse_dsn(dsn) -> ConnectionParams

Parse a libpq-style keyword string ("host=127.0.0.1 user=postgres") or a PostgreSQL URI ("postgresql://user:pass@host:5432/dbname") into ConnectionParams. Unset options fall back to the PGHOST, PGPORT, PGUSER, PGPASSWORD, PGDATABASE, PGAPPNAME, PGCONNECT_TIMEOUT, and PGSSL* environment variables, then to defaults.