postgres

package
v0.23.0 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Nov 3, 2026 License: MIT Imports: 18 Imported by: 0

Documentation

Overview

Package postgres provides a pgxpool.Pool constructor with the operational defaults the source applications lacked: slow-query tracing (see slow_query_tracer.go), an optional acquire timeout, acquire-wait and connection-hold histograms with optional leak detection (see pool_tracer.go), Prometheus pool metrics (see metrics.go), a hard startup failure instead of a silent no-op when TLS is required but the DSN does not enable a verified TLS connection, and server-side statement/lock/idle-in-transaction timeouts applied on every new connection.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func CheckTLS added in v0.9.0

func CheckTLS(dsn string) error

CheckTLS reports whether dsn satisfies RequireTLS, applying exactly the check NewPool applies, without connecting. It is for code that must hand a DSN string to something other than NewPool -- a migration tool, for instance -- and still honor the same TLS policy as the rest of the service. It returns an error for an unparsable DSN, or one that allows a plaintext connection or does not verify the server certificate.

func NewPool

func NewPool(ctx context.Context, cfg Config, logger *slog.Logger) (*pgxpool.Pool, error)

NewPool creates and validates a *pgxpool.Pool from cfg. It fails fast (before returning a pool) if the DSN is invalid, if RequireTLS is true but the DSN does not make pgx verify the server certificate, or if the initial ping fails.

Types

type Config

type Config struct {
	// URL is the PostgreSQL connection string (DSN), e.g.
	// "postgres://user:pass@host:5432/db?sslmode=verify-full".
	URL string

	MaxConns        int32
	MinConns        int32
	MaxConnLifetime time.Duration
	MaxConnIdleTime time.Duration
	ConnectTimeout  time.Duration

	// RequireTLS demands an encrypted connection to a verified server, with a
	// minimum of TLS 1.2. If true, NewPool fails fast unless the DSN makes pgx
	// verify the server certificate for every host it may connect to:
	// sslmode=verify-full (recommended: chain and hostname), verify-ca
	// (chain only), or require with sslrootcert (which libpq and pgx treat
	// as verify-ca). disable, allow, prefer (the default when sslmode is
	// absent) and a bare require are rejected: they either permit plaintext
	// or accept any certificate, so a man in the middle could read the
	// traffic. False takes the DSN as is. See applyTLSConfig.
	RequireTLS bool

	// StatementTimeout caps how long a single SQL statement may run before
	// PostgreSQL cancels it (server-side `SET statement_timeout`). Protects
	// against a runaway query holding a connection (and any locks it took)
	// indefinitely. Zero uses defaultStatementTimeout (30s); a negative value
	// disables the timeout explicitly.
	StatementTimeout time.Duration

	// LockTimeout caps how long a statement will wait to acquire a row/table
	// lock before failing (server-side `SET lock_timeout`). Protects against a
	// request queuing behind a lock held by another slow transaction instead
	// of failing fast and letting the caller retry or surface an error. Zero
	// uses defaultLockTimeout (5s); a negative value disables it explicitly.
	LockTimeout time.Duration

	// IdleInTransactionSessionTimeout caps how long a connection may sit idle
	// while inside an open transaction (server-side
	// `SET idle_in_transaction_session_timeout`) before PostgreSQL kills the
	// session.
	//
	// This is the timeout that matters most of the three: it is the only
	// server-side backstop against a handler bug that begins a transaction,
	// then hangs (a stuck downstream call, a forgotten context deadline, a
	// panic recovered above the DB layer without rolling back) while still
	// holding the transaction's row locks. Without it, that one stuck request
	// can block every other request that needs the same rows — potentially
	// the whole pool, if MaxConns is small — until the process is restarted.
	// statement_timeout does not help here because no statement is running;
	// the connection is simply idle with an open transaction.
	//
	// Zero uses defaultIdleInTransactionSessionTimeout (60s); a negative value
	// disables it explicitly.
	IdleInTransactionSessionTimeout time.Duration

	// AcquireTimeout bounds how long Acquire (and every Pool method that
	// acquires: Exec, Query, QueryRow, Begin, ...) waits for a free pool
	// connection, so a full pool makes requests fail fast instead of queuing
	// until the caller's own deadline. It applies through pgxpool's
	// AcquireTracer: the acquire context gets this deadline (a shorter caller
	// deadline still wins) and is canceled when Acquire returns.
	//
	// A wait cut by it fails with an error wrapping context.DeadlineExceeded:
	// recognize it with errors.Is(err, context.DeadlineExceeded) while the
	// caller's own ctx.Err() is still nil (the caller's deadline produces the
	// same error, but then ctx.Err() is set too). The wait is observed in the
	// db_pool_acquire_wait_seconds histogram either way.
	//
	// The deadline covers the whole acquire, so when the pool has room but no
	// idle connection it also bounds dialing a new one (as HikariCP's
	// connectionTimeout does): keep it above the time a healthy connect
	// takes, or a request that has to open a connection fails before
	// ConnectTimeout. NewPool's own startup Ping is exempt, so the boot still
	// waits up to ConnectTimeout for a database that is waking up.
	//
	// Zero disables it (pgxpool's behavior: wait for the caller's context); a
	// negative value is a NewPool error.
	AcquireTimeout time.Duration

	// LeakThreshold makes NewPool log a WARN when a connection is released
	// after being held (acquired and not yet released) longer than this,
	// naming the function and file:line that acquired it: the first frame
	// outside pgx and this module's postgres and pgxtx packages. The WARN
	// never includes SQL or arguments. Hold times are always observed in the
	// db_pool_conn_hold_seconds histogram; this only adds the log and the
	// per-acquire stack capture it needs, so leave it zero when unused.
	//
	// The check runs at release, so a connection that is never released is
	// not reported by this log; db_pool_connections_acquired staying at
	// db_pool_connections_max shows that case. Zero disables it; a negative
	// value is a NewPool error.
	LeakThreshold time.Duration

	// HashSlowQuerySQL makes the slow-query WARN log sql_hash (a short SHA-256
	// prefix of the SQL text) instead of the SQL text. Leave it false to see
	// the statement, cut at 2 KiB. Bind values are never logged. The hash is
	// unsalted: it keeps the statement text out of the logs, but a reader who
	// knows the template can still confirm a low-entropy inlined literal by
	// hashing guesses, so bind such values as parameters instead. In this mode
	// a failed query's error text is dropped too, since it can quote a value:
	// a PostgreSQL error is logged by SQLSTATE (error_code) and constraint
	// name, any other error by its Go type.
	HashSlowQuerySQL bool

	// Tracer, if set, is called alongside the tracer NewPool always installs
	// (slow queries, acquire timeout, pool histograms, leak detection), on
	// every hook it implements: pgx.QueryTracer, and optionally
	// pgx.BatchTracer, pgx.CopyFromTracer, pgx.PrepareTracer,
	// pgx.ConnectTracer, pgxpool.AcquireTracer and pgxpool.ReleaseTracer.
	// Its Start hooks run before vogel's and its End hooks after, so a span
	// it opens encloses the slow-query log line. Any OpenTelemetry pgx
	// tracer fits it; this package depends on no tracing library.
	Tracer pgx.QueryTracer

	// TraceIDs, if set, returns the trace and span IDs of the span carried
	// by a context, or empty strings when there is none. The pool then
	// attaches the acquiring span as an exemplar to db_pool_acquire_wait_seconds
	// and db_pool_conn_hold_seconds, and adds trace_id and span_id to the
	// leak-threshold warning (read at acquire: the release has no context).
	// otel.SampledTraceIDs fits it, so exemplars only point at exported
	// traces. Prometheus reads exemplars only in the OpenMetrics format.
	TraceIDs func(ctx context.Context) (traceID, spanID string)

	// AfterConnect, if set, runs after this package's own per-connection setup
	// (TLS, timeouts, tracer) on every new physical connection. Use it for
	// consumer-specific per-connection work — for example, registering a
	// custom pgtype codec such as jackc/pgx-shopspring-decimal, the way
	// go-crucible's original constructor did inline. Returning an error here
	// fails that connection attempt.
	AfterConnect func(ctx context.Context, conn *pgx.Conn) error
}

Config configures NewPool. Adapters own their Config struct rather than reading a global application config, so this type carries only what a pgxpool.Pool needs — the consuming application decides how (env vars, a flags package, hardcoded test values, ...) to fill it in.

type PoolMetricsCollector

type PoolMetricsCollector struct {
	// contains filtered or unexported fields
}

PoolMetricsCollector exports pgxpool stats as Prometheus metrics.

Unlike a package-level prometheus.MustRegister, NewPoolMetricsCollector is safe to call more than once against the same Registerer: a prometheus.AlreadyRegisteredError is handled by reusing the already registered collector instead of panicking. This matters for a library — a consumer may build two pools (e.g. in a test that constructs a fresh app per test case) and must not have the second one panic the process.

Reusing the existing collector means a second NewPoolMetricsCollector call against the same Registerer returns the FIRST pool's collector, still bound to the first pool. If a consumer genuinely needs metrics for two distinct, simultaneously-live pools, register each with its own prometheus.Registerer (e.g. a dedicated prometheus.NewRegistry()) rather than sharing one.

For a pool built by NewPool the collector also exports the pool tracer's histograms, db_pool_acquire_wait_seconds and db_pool_conn_hold_seconds (see pool_tracer.go), through this same registration. Their descriptors are described for every pool, so the duplicate-registration handling above covers them too; a pool built without NewPool simply exports no samples for them.

func NewPoolMetricsCollector

func NewPoolMetricsCollector(pool *pgxpool.Pool, reg prometheus.Registerer) (*PoolMetricsCollector, error)

NewPoolMetricsCollector creates a collector for pool and registers it on reg. If reg is nil, prometheus.DefaultRegisterer is used.

func (*PoolMetricsCollector) Collect

func (c *PoolMetricsCollector) Collect(ch chan<- prometheus.Metric)

func (*PoolMetricsCollector) Describe

func (c *PoolMetricsCollector) Describe(ch chan<- *prometheus.Desc)

type SlowQueryOption added in v0.11.0

type SlowQueryOption func(*SlowQueryTracer)

SlowQueryOption configures a SlowQueryTracer.

func WithSlowQuerySQLHash added in v0.11.0

func WithSlowQuerySQLHash() SlowQueryOption

WithSlowQuerySQLHash logs sql_hash, the first sqlHashBytes bytes of the SHA-256 of the SQL text in hex, instead of the text itself, and a PostgreSQL error by its SQLSTATE instead of its message.

type SlowQueryTracer

type SlowQueryTracer struct {
	// contains filtered or unexported fields
}

SlowQueryTracer implements pgx.QueryTracer and logs queries that exceed the configured threshold.

Bind values are never logged, only their count (args_count). The SQL text is logged, bounded by maxLoggedSQLBytes, because a slow query cannot be diagnosed without it; a literal inlined in the SQL instead of bound would therefore reach the log. WithSlowQuerySQLHash replaces the text with a short hash (sql_hash) that still groups repeated queries.

A failed query's error text is logged bounded by maxLoggedErrorBytes. An error message can quote a value: PostgreSQL's (`invalid input syntax for type uuid: "..."`) and pgx's own when it encodes an argument or scans a result. So with WithSlowQuerySQLHash the text is dropped: a *pgconn.PgError is logged as error_code (its SQLSTATE) and error_constraint, a canceled or timed-out context keeps its fixed text, and any other error is logged by its Go type only (error_type).

The hash is unsalted, so it pseudonymizes rather than hides: someone who can read the logs and knows the query template can hash candidate values (an email, a user ID) and confirm a low-entropy literal. Binding values as parameters is what keeps them out of the logs; the hash only keeps the statement text out.

func NewSlowQueryTracer

func NewSlowQueryTracer(logger *slog.Logger, threshold time.Duration, opts ...SlowQueryOption) *SlowQueryTracer

NewSlowQueryTracer creates a tracer that logs queries slower than threshold.

func (*SlowQueryTracer) TraceQueryEnd

func (t *SlowQueryTracer) TraceQueryEnd(ctx context.Context, _ *pgx.Conn, data pgx.TraceQueryEndData)

func (*SlowQueryTracer) TraceQueryStart

func (t *SlowQueryTracer) TraceQueryStart(ctx context.Context, _ *pgx.Conn, data pgx.TraceQueryStartData) context.Context

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL