Postconditions: what we check

• query · syntax-error

  error
  WhenSQL syntax error (42601)
  ThrowsError with code '42601'
  Required handlingCaller MUST validate SQL syntax before execution. This indicates a SQL syntax error - DO NOT retry. Check error.position for location of syntax error in query.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• query · unique-violation

  error
  WhenUnique constraint violation (23505)
  ThrowsError with code '23505'
  Required handlingCaller MUST handle duplicate key violations gracefully. Extract constraint name from error.constraint. DO NOT retry without changing the unique field value.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[1]
• query · foreign-key-violation

  error
  WhenForeign key constraint violation (23503)
  ThrowsError with code '23503'
  Required handlingCaller MUST verify referenced record exists before insertion. Extract constraint and table from error.constraint and error.table. This indicates data integrity issue - DO NOT retry.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[1]
• query · not-null-violation

  error
  WhenNOT NULL constraint violation (23502)
  ThrowsError with code '23502'
  Required handlingCaller MUST provide all required fields. Extract column name from error.column. Validate data completeness before query execution.
  costmediumin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[1]
• query · connection-error

  error
  WhenConnection to database failed
  ThrowsError with code 'ECONNREFUSED' or similar network errors
  Required handlingCaller MUST handle connection failures separately from query errors. Common causes: database down, wrong host/port, network issues. Implement retry with exponential backoff for transient connection issues. Alert operations if connection errors persist.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[2]
• query · undefined-table

  error
  WhenTable or view does not exist (42P01)
  ThrowsError with code '42P01'
  Required handlingCaller MUST verify table exists before querying. Check error.message for table name. DO NOT retry - this indicates schema mismatch or missing migration.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• query · null-undefined-query-throws

  error
  Whenclient.query() or pool.query() called with null/undefined first argument
  ThrowsTypeError with message 'Client was passed a null or undefined query'
  Required handlingCaller MUST validate that the query string/config is non-null before invoking query(). Common cause: building dynamic SQL from a conditional that may yield undefined (e.g. `const sql = condition ? buildQuery() : undefined; await client.query(sql)`). This is a synchronous TypeError thrown before any Promise resolution — it WILL NOT be caught by `.catch()` on the returned promise if the caller forgot to await inside try/catch. Wrap query() invocations in try/catch when the query source could be undefined.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[3]
• query · pool-query-function-arg-rejects

  error
  Whenpool.query() called with a function as the first argument
  ThrowsError with message 'Passing a function as the first parameter to pool.query is not supported'
  Required handlingCaller MUST pass a query string or QueryConfig object as the first argument to pool.query() — never a function. Common cause: passing a query-builder factory by reference instead of calling it. Pattern check: `pool.query(buildQuery, ...)` should be `pool.query(buildQuery(), ...)`. The error rejects the returned Promise — must be handled in catch block.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4][5]
• query · query-read-timeout

  error
  WhenQuery exceeds query_timeout (per-query or client-level) milliseconds
  ThrowsError with message 'Query read timeout'
  Required handlingCaller MUST handle query timeout errors distinctly from connection errors. When query_timeout is configured (on Client config or per-query via QueryConfig), long-running queries that exceed the timeout reject with 'Query read timeout'. DO NOT blindly retry — the query may still be running on the database and a retry can cause duplicate work or duplicate writes. Cancel the query at the PostgreSQL level via pg_cancel_backend() if retry is required. For idempotent read queries, retry with shorter timeout is acceptable.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[3][6]
• query · database-error-canonical-type

  warning
  WhenPostgres server-side error (constraint violation, syntax error, type error, etc.)
  ThrowsDatabaseError (from 'pg-protocol', re-exported by pg) with .code property containing SQLSTATE
  Required handlingCaller SHOULD use `error instanceof DatabaseError` to distinguish Postgres-level errors from client-side errors (TypeError, network errors). DatabaseError exposes structured fields not available on generic Error: .code (5-char SQLSTATE), .detail, .hint, .position (for syntax errors), .constraint, .table, .column, .schema. Generic `catch (error)` without instance check loses this structured context. Pattern: try { await client.query(...); } catch (err) { if (err instanceof DatabaseError) { // handle by err.code (e.g. '23505' unique violation) } else { // network / type error } }
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[7][8]
• connect · pool-exhausted

  error
  WhenAll clients in pool are busy and connectionTimeoutMillis exceeded
  ThrowsError with message 'timeout exceeded when trying to connect'
  Required handlingCaller MUST handle pool exhaustion errors. Root causes: 1. Clients not released (forgot client.release()) 2. Pool size too small for load 3. Queries taking too long Implement proper client.release() in finally blocks. Monitor pool metrics to identify leaks.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[5]
• connect · connection-error

  error
  WhenCannot establish database connection
  ThrowsError with code 'ECONNREFUSED', 'ETIMEDOUT', etc.
  Required handlingCaller MUST handle connection failures. Implement retry with exponential backoff. Check database server status. Verify connection string credentials.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[9]
• connect · client-not-released

  error
  WhenCaller forgets to release client back to pool
  ThrowsNo error thrown, but pool becomes exhausted over time
  Required handlingCaller MUST ALWAYS release clients in a finally block: ``` const client = await pool.connect(); try { await client.query(...); } finally { client.release(); } ``` CRITICAL: This is the #1 production bug (affects 30% of mid-sized projects). Failure to release causes pool exhaustion and application hangs. Silent failure until complete outage.
  costcriticalin proddelayed failureusers seeservice unavailablevisibilityvisible
  Sources[10]
• connect · client-reuse-after-connect

  error
  WhenClient.connect() called a second time on the same Client instance
  ThrowsError with message 'Client has already been connected. You cannot reuse a client.'
  Required handlingCaller MUST construct a NEW Client instance for each connection cycle. Calling .connect() twice on the same Client (even after .end()) rejects with this error. Common cause: connection-retry logic that loops on the same client instance after end(). CORRECT pattern: on retry, instantiate `const client = new Client(config)` then await client.connect(). For pooled scenarios, use Pool which manages the Client lifecycle internally.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[3][8]
• connect · pool-connect-after-end

  error
  WhenPool.connect() called after Pool.end() has been initiated
  ThrowsError with message 'Cannot use a pool after calling end on the pool'
  Required handlingCaller MUST stop routing new request handlers before calling pool.end(). Pattern: set a `shuttingDown` flag in the SIGTERM/SIGINT handler, reject incoming requests while shuttingDown is true, then call pool.end(). Without this guard, any in-flight request handler that calls pool.connect() during graceful-shutdown drain throws this error and the request fails. This is distinct from the `pool-query-after-end` postcondition on `end` — that one covers pool.query(); this one covers pool.connect() specifically.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4]
• connect · pool-onconnect-hook-fails

  error
  WhenPoolConfig.onConnect callback throws or rejects during connection setup
  ThrowsThe hook's own error (any Error subclass) rejected from Pool.connect()
  Required handlingCaller MUST handle errors from pool.connect() even when an onConnect hook is configured — a failing hook propagates as a pool.connect() rejection. The onConnect hook is invoked after the underlying TCP connect succeeds but before the client is returned. Common failure modes inside onConnect: (1) a SET command fails (`SET search_path TO ...` with an invalid schema), (2) a session-config query times out, (3) the hook awaits an external service that returns 500. The pool destroys the client on hook failure (client.end()) — partial initialization is not retained. Implement retry logic around pool.connect() calls whose onConnect performs network I/O.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4][6]
• connect · connection-terminated-during-timeout

  error
  WhenTCP/TLS handshake for a new pool client exceeds connectionTimeoutMillis
  ThrowsError with message 'Connection terminated due to connection timeout' (with original error attached as .cause)
  Required handlingCaller MUST distinguish this from `pool-exhausted` (which fires when an existing idle client is unavailable). This timeout fires during the actual TCP/TLS handshake when establishing a new connection — typically caused by network latency, firewall delays, or a slow PostgreSQL bootstrap (slow SSL negotiation on PG 17+ direct sslnegotiation). Inspect error.cause for the underlying connect error to diagnose. Treat as a transient error and retry with backoff, but escalate to ops if persistent — usually indicates network/infrastructure issue, not pool sizing.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4]
• connect · sslnegotiation-direct-requires-ssl

  error
  WhenPool/Client constructed with sslnegotiation: 'direct' but ssl is falsy
  ThrowsError with message 'sslnegotiation=direct requires SSL to be enabled' (or 'Invalid sslnegotiation value: ...' for unknown values)
  Required handlingCaller MUST set ssl: true (or an ssl ConnectionOptions object) when using sslnegotiation: 'direct'. Direct SSL negotiation requires TLS — there is no plaintext mode. This is a configuration-time error: the Pool or Client constructor throws synchronously, so the failure occurs at app startup, not on first connect(). When upgrading to pg@8.22.0+ and PostgreSQL 17+ for the faster TLS handshake, audit existing PoolConfig/ClientConfig to ensure ssl is set wherever sslnegotiation is. Equally, 'sslnegotiation' values other than 'postgres' or 'direct' throw a similar Invalid-value error.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[11][6]
• end · pool-end-called-twice

  error
  WhenPool.end() called more than once on the same pool instance
  ThrowsError with message 'Called end on pool more than once'
  Required handlingCaller MUST guard against double-end by checking a boolean flag before calling pool.end(). Common in serverless functions with multiple shutdown signal handlers. Pattern: if (!isShuttingDown) { isShuttingDown = true; await pool.end(); } Failure to guard causes an unhandled rejection on the second call.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4][5]
• end · pool-query-after-end

  error
  WhenPool.connect() or Pool.query() called after Pool.end() has completed
  ThrowsError with message 'Cannot use a pool after calling end on the pool'
  Required handlingCaller MUST stop routing new requests before initiating pool shutdown. Implement graceful drain: stop accepting requests → wait for in-flight to complete → then call pool.end(). Never call pool.end() in the middle of active request handling without draining first.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4][5]
• end · client-end-connection-terminated

  warning
  WhenQueries in-flight when Client.end() is called
  ThrowsError with message 'Client was closed and is not queryable'
  Required handlingCaller MUST ensure no queries are in-flight before calling client.end(). For standalone client usage, always await all queries to completion before calling end(). Use try/finally to ensure end() is called after query completion.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[3][8]
• release · double-release-throws

  error
  Whenclient.release() called more than once on the same PoolClient
  ThrowsError with message 'Release called on client which has already been released to the pool.'
  Required handlingCaller MUST call release() exactly once. ALWAYS use finally block exclusively: const client = await pool.connect(); try { ... } finally { client.release(); } NEVER call release() in both catch and finally — the catch error path goes through the finally block, causing a double-release. If a transaction error occurred, call client.release(err) once in finally.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4][9]
• release · release-with-error-destroys-client

  warning
  Whenclient.release(err) called with an error after a failed transaction
  ThrowsNo error thrown — client is destroyed instead of returned to pool
  Required handlingCaller SHOULD pass the error to release() when the client was used in a failed transaction: client.release(err). This destroys the connection rather than returning a tainted connection (with an open transaction or held locks) to the pool. Forgetting to pass the error can cause the next user of that pooled connection to inherit transaction state.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[5][4]
• transaction · transaction-not-rolled-back-on-error

  error
  WhenQuery inside BEGIN/COMMIT transaction block throws and ROLLBACK is not issued
  ThrowsNo error from pg — but PostgreSQL holds row locks until session disconnects
  Required handlingCaller MUST issue ROLLBACK in the catch block whenever a transaction is in progress. REQUIRED pattern: try { await client.query('BEGIN'); ... await client.query('COMMIT'); } catch (e) { await client.query('ROLLBACK'); throw e; } finally { client.release(); } Failure to ROLLBACK leaves open locks, causing other queries to block waiting for those rows. Can cascade into application-wide deadlock.
  costcriticalin proddelayed failureusers seeservice unavailablevisibilityvisible
  Sources[12][13]
• transaction · transaction-on-pool-query

  error
  Whenpool.query() used for BEGIN/COMMIT/ROLLBACK instead of a dedicated client
  ThrowsNo error thrown — but transaction silently spans different connections
  Required handlingCaller MUST obtain a dedicated client via pool.connect() for all transaction work. NEVER use pool.query() for BEGIN/COMMIT/ROLLBACK — each call may hit a different connection, making the transaction a no-op. CORRECT: const client = await pool.connect(); await client.query('BEGIN'); ... WRONG: await pool.query('BEGIN'); await pool.query(...); await pool.query('COMMIT');
  costcriticalin prodsilent failureusers seelost datavisibilitysilent
  Sources[12]
• transaction · transaction-deadlock

  error
  WhenTwo concurrent transactions deadlock — PostgreSQL code 40P01
  ThrowsDatabaseError with error.code === '40P01' (deadlock_detected)
  Required handlingCaller MUST catch DatabaseError with code '40P01' and retry the entire transaction from BEGIN. DO NOT retry individual queries — the whole transaction context is invalidated. Implement exponential backoff with jitter between retries. Check error.code to distinguish deadlock from other errors.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[1][14]
• transaction · transaction-serialization-failure

  error
  WhenSERIALIZABLE transaction conflicts — PostgreSQL code 40001
  ThrowsDatabaseError with error.code === '40001' (serialization_failure)
  Required handlingCaller MUST catch DatabaseError with code '40001' and retry the entire transaction from BEGIN. Serialization failures are expected in SERIALIZABLE isolation — they are not bugs but rather PostgreSQL enforcing correctness. Implement retry with backoff. Only applies when using SERIALIZABLE isolation.
  costhighin prodimmediate exceptionusers seelost datavisibilityvisible
  Sources[1][15]

Sources

Every postcondition cites at least one of these. Grouped by source type; numbered to match the footnotes above.

Research notes

Curator notes from SOURCES.md captured when the profile was written so you can verify the reasoning, not just the rules.

import { UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION } from 'pg-error-constants';

if (error.code === UNIQUE_VIOLATION) {
  // Handle duplicate key
}

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'user',
  password: 'password',
  max: 20,                        // Max 20 connections
  idleTimeoutMillis: 30000,       // Close idle clients after 30s
  connectionTimeoutMillis: 2000,  // Wait max 2s for connection
});

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  // Client is automatically terminated and removed from pool
});

const client = await pool.connect();
try {
  const result = await client.query('SELECT * FROM users WHERE id = $1', [userId]);
  return result.rows;
} finally {
  client.release();  // ALWAYS release, even on error
}

// Automatically checks out and releases client
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// ❌ DANGEROUS: SQL injection vulnerability
const query = `SELECT * FROM users WHERE email = '${email}'`;
await pool.query(query);

// ✅ SAFE: Parameterized query
await pool.query('SELECT * FROM users WHERE email = $1', [email]);

pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
  // Log to monitoring system
  // Client is automatically removed from pool
});

try {
  await pool.query('INSERT INTO users (email) VALUES ($1)', [email]);
} catch (error) {
  if (error.code === '23505') {
    // Unique violation - user already exists
    throw new Error(`User with email ${email} already exists`);
  } else if (error.code === '23502') {
    // Not null violation
    throw new Error(`Missing required field: ${error.column}`);
  }
  throw error; // Re-throw unexpected errors
}

// ✅ CORRECT
await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

// ❌ WRONG - SQL injection vulnerability
await pool.query(`SELECT * FROM users WHERE id = '${userId}'`);

// ✅ CORRECT - Using try/finally
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('INSERT INTO users (name) VALUES ($1)', ['Alice']);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();  // ALWAYS release
}

// ✅ REQUIRED
pool.on('error', (err, client) => {
  console.error('Unexpected error on idle client', err);
});

// ✅ RECOMMENDED
import { UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION } from 'pg-error-constants';

try {
  await pool.query('INSERT INTO users (email) VALUES ($1)', [email]);
} catch (error) {
  if (error.code === UNIQUE_VIOLATION) {
    return { error: 'Email already exists' };
  }
  throw error;
}

// ✅ PRODUCTION CONFIGURATION
const pool = new Pool({
  max: 20,                        // Adjust based on load
  idleTimeoutMillis: 30000,       // Close idle connections
  connectionTimeoutMillis: 2000,  // Don't wait forever
});

// Monitor pool health
setInterval(() => {
  console.log('Pool status:', {
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  });
}, 60000);