Guides

Read the wire diagnostic

Start with the protocol message type. PostgreSQL sends an ErrorResponse for an error and a NoticeResponse for a notice; both carry identified fields terminated by a zero byte. The C field is the non-localized SQLSTATE, M is the primary message, and D/H are optional detail and hint. S can be localized; V is the non-localized severity when supplied. Schema (s), table (t), column (c), constraint (n), position (P), context (W), source file (F), line (L), and routine (R) are conditional fields: preserve them when present and do not infer missing fields. See the PostgreSQL 18 error and notice fields and message formats.

A directory class is only a classifier. Read the actual protocol severity and message type before deciding whether a command failed. A WARNING or NOTICE may arrive as a notice while the command completes; ERROR arrives as an error and normally changes explicit transaction state. A successful CommandComplete has a command tag and no ErrorResponse SQLSTATE. Keep the server version and operation beside the fields because one SQLSTATE can have several source paths.

Recover the right transaction boundary

Autocommit gives each statement its own transaction boundary. After a statement error, an otherwise usable connection can execute a later statement. In an explicit transaction, an unhandled ERROR leaves the transaction failed; PostgreSQL then rejects ordinary commands with 25P02 until rollback. 25P02 is a follow-on observation, not the first error. Use the ROLLBACK and SAVEPOINT references for the boundary you actually established.

If an already-existing savepoint surrounds one optional unit, use ROLLBACK TO SAVEPOINT name, then continue in the outer transaction or release the savepoint. Work before that savepoint remains. Without such a savepoint, ROLLBACK ends the failed transaction and a retry starts a new one. The accepted 25P02 case shows root 23505, subsequent 25P02, and recovery; 23505 evidence records the narrower savepoint boundary.

A PL/pgSQL EXCEPTION block is another narrow boundary. Changes made inside the block are rolled back before its handler runs, while earlier outer work remains. In the handler, SQLSTATE and SQLERRM identify the current exception; GET STACKED DIAGNOSTICS can retrieve RETURNED_SQLSTATE, message, detail, hint, context, and object fields. See error trapping, stacked diagnostics, and RAISE. Keep the handler around the smallest operation that can be classified safely; WHEN OTHERS should not discard the original fields.

Retry a complete unit only when its outcome is known

Retry is a business decision, not a property of a SQLSTATE class. For a transient serialization or deadlock error, retry the whole transaction from a fresh snapshot when the operation is safe. Do not replay only the last statement if earlier reads, writes, locks, notifications, or external calls formed one business unit. A transaction may have committed while the client timed out or lost its connection before receiving the result; completion can then be uncertain. Use an idempotency key, durable business key, or status query before repeating externally visible work.

For authorization failures, missing objects, and invalid data, first inspect and repair the permission, object, or input; then decide whether a corrected operation is safe to run. A warning that already completed the command should not trigger automatic replay. Preserve the first diagnostic and transaction state; log a later 25P02 as a consequence. Backoff and attempt limits control load, but cannot make a non-idempotent operation safe. A clear server-side timeout or cancellation is a failed operation; a lost connection before the client knows the outcome is a separate uncertain-completion case.

Correlate application and server logs

Record SQLSTATE, non-localized severity, primary message, detail, hint, object names, position/context, server version, backend PID when available, and client transaction state before formatting a human message. Add an application request or idempotency key and timestamp so the operation can be found in server logs. PID, session identifiers, and query ID are correlation aids, not replacements for protocol fields.

CSV and JSON server logs are structured but do not have the same shape as an ErrorResponse. PostgreSQL 18 documents csvlog columns including severity, SQLSTATE, message, detail, hint, context, query, source location, application name, backend type, and query ID. jsonlog is JSON and may omit null-valued fields; consumers should ignore future fields. Configure log_destination and logging_collector using the logging reference, then correlate by PID/session/request rather than assuming a CSV column or JSON key equals a protocol field.

PL/pgSQL and custom conditions

RAISE can report DEBUG, LOG, INFO, NOTICE, WARNING, or EXCEPTION; EXCEPTION normally aborts the current transaction. It can select a condition name or five-character SQLSTATE and set MESSAGE, DETAIL, HINT, SCHEMA, TABLE, COLUMN, DATATYPE, or CONSTRAINT with USING. PostgreSQL permits any five-character code made of digits and upper-case ASCII letters except 00000, including custom codes. A code ending in three zeroes is a category and can only be trapped by that category. See RAISE syntax.

A named handler matches its condition and documented aliases; a class handler is broader. WHEN OTHERS catches every error condition except QUERY_CANCELED and ASSERT_FAILURE; notice-level messages are not exceptions. A custom code raised by PL/pgSQL proves that deliberate report only, not natural emission by core, contrib, FDW, ECPG, or a driver.

What clients expose

The table pins each row to an inspected release or primary documentation. It does not claim that all six clients were runtime-tested here.

Named exception classes, constants, and wrappers are convenience mappings. Cross-client evidence remains the protocol SQLSTATE and fields; a client-side message or null SQLSTATE does not overwrite a confirmed server-log code. Existing psycopg 3.3.5/libpq 18.6 startup records with driver-null and server 28P01/53300 demonstrate this boundary for that stack only.