0
# Error Handling and Diagnostics
1

2
Comprehensive PostgreSQL error code mapping, exception hierarchy, error diagnostics, and debugging support. psycopg2 provides detailed error information and structured exception handling for robust database application development.
3

4
## Capabilities
5

6
### Exception Hierarchy
7

8
Complete DB API 2.0 compliant exception hierarchy with PostgreSQL-specific extensions.
9

10
```python { .api }
11
class Error(Exception):
12
    """Base class for all psycopg2 exceptions."""
13
    
14
    @property
15
    def pgcode(self):
16
        """PostgreSQL error code (SQLSTATE)."""
17
    
18
    @property  
19
    def pgerror(self):
20
        """PostgreSQL error message."""
21
    
22
    @property
23
    def diag(self):
24
        """Diagnostics object with detailed error info."""
25

26
class Warning(Error):
27
    """Exception for important warnings."""
28

29
class InterfaceError(Error):
30
    """Exception for interface-related errors."""
31

32
class DatabaseError(Error):
33
    """Exception for database-related errors."""
34

35
class DataError(DatabaseError):
36
    """Exception for data processing errors."""
37

38
class OperationalError(DatabaseError):
39
    """Exception for operational errors."""
40

41
class IntegrityError(DatabaseError):
42
    """Exception for integrity constraint violations."""
43

44
class InternalError(DatabaseError):
45
    """Exception for internal database errors."""
46

47
class ProgrammingError(DatabaseError):
48
    """Exception for programming errors."""
49

50
class NotSupportedError(DatabaseError):
51
    """Exception for unsupported operations."""
52
```
53

54
### Specific Exception Classes
55

56
PostgreSQL-specific exception classes for common error conditions.
57

58
```python { .api }
59
class QueryCanceledError(OperationalError):
60
    """Exception for canceled queries."""
61

62
class TransactionRollbackError(OperationalError):
63
    """Exception for transaction rollback errors."""
64

65
class AdminShutdown(OperationalError):
66
    """Exception for administrative shutdown."""
67

68
class CrashShutdown(OperationalError):
69
    """Exception for crash shutdown."""
70

71
class CannotConnectNow(OperationalError):
72
    """Exception when server cannot accept connections."""
73

74
class ConnectionFailure(OperationalError):
75
    """Exception for connection failures."""
76

77
class SqlclientUnableToEstablishSqlconnection(OperationalError):
78
    """Exception for SQL connection establishment failures."""
79

80
class DeadlockDetected(OperationalError):
81
    """Exception for deadlock detection."""
82

83
class SerializationFailure(OperationalError):
84
    """Exception for serialization failures."""
85

86
class StatementCompletionUnknown(OperationalError):
87
    """Exception for unknown statement completion."""
88

89
class LockNotAvailable(OperationalError):
90
    """Exception when lock is not available."""
91

92
class ForeignKeyViolation(IntegrityError):
93
    """Exception for foreign key constraint violations."""
94

95
class UniqueViolation(IntegrityError):
96
    """Exception for unique constraint violations."""
97

98
class CheckViolation(IntegrityError):
99
    """Exception for check constraint violations."""
100

101
class NotNullViolation(IntegrityError):
102
    """Exception for not-null constraint violations."""
103

104
class RestrictViolation(IntegrityError):
105
    """Exception for restrict constraint violations."""
106

107
class ExclusionViolation(IntegrityError):
108
    """Exception for exclusion constraint violations."""
109

110
class InvalidCursorDefinition(ProgrammingError):
111
    """Exception for invalid cursor definitions."""
112

113
class InvalidDatabaseDefinition(ProgrammingError):
114
    """Exception for invalid database definitions."""
115

116
class InvalidPreparedStatementDefinition(ProgrammingError):
117
    """Exception for invalid prepared statement definitions."""
118

119
class InvalidSchemaDefinition(ProgrammingError):
120
    """Exception for invalid schema definitions."""
121

122
class InvalidTableDefinition(ProgrammingError):
123
    """Exception for invalid table definitions."""
124

125
class UndefinedColumn(ProgrammingError):
126
    """Exception for undefined columns."""
127

128
class UndefinedFunction(ProgrammingError):
129
    """Exception for undefined functions."""
130

131
class UndefinedTable(ProgrammingError):
132
    """Exception for undefined tables."""
133

134
class DuplicateColumn(ProgrammingError):
135
    """Exception for duplicate columns."""
136

137
class DuplicateCursor(ProgrammingError):
138
    """Exception for duplicate cursors."""
139

140
class DuplicateDatabase(ProgrammingError):
141
    """Exception for duplicate databases."""
142

143
class DuplicateFunction(ProgrammingError):
144
    """Exception for duplicate functions."""
145

146
class DuplicatePreparedStatement(ProgrammingError):
147
    """Exception for duplicate prepared statements."""
148

149
class DuplicateSchema(ProgrammingError):
150
    """Exception for duplicate schemas."""
151

152
class DuplicateTable(ProgrammingError):
153
    """Exception for duplicate tables."""
154

155
class DuplicateAlias(ProgrammingError):
156
    """Exception for duplicate aliases."""
157

158
class DuplicateObject(ProgrammingError):
159
    """Exception for duplicate objects."""
160

161
class SyntaxError(ProgrammingError):
162
    """Exception for SQL syntax errors."""
163

164
class InsufficientPrivilege(ProgrammingError):
165
    """Exception for insufficient privileges."""
166

167
class InvalidName(ProgrammingError):
168
    """Exception for invalid names."""
169

170
class InvalidColumnReference(ProgrammingError):
171
    """Exception for invalid column references."""
172

173
class InvalidCursorName(ProgrammingError):
174
    """Exception for invalid cursor names."""
175

176
class InvalidDatatypeDefinition(ProgrammingError):
177
    """Exception for invalid datatype definitions."""
178

179
class InvalidForeignKey(ProgrammingError):
180
    """Exception for invalid foreign keys."""
181

182
class InvalidParameterValue(ProgrammingError):
183
    """Exception for invalid parameter values."""
184

185
class InvalidTransactionState(ProgrammingError):
186
    """Exception for invalid transaction states."""
187

188
class ActiveSqlTransaction(ProgrammingError):
189
    """Exception for active SQL transactions."""
190

191
class BranchTransactionAlreadyActive(ProgrammingError):
192
    """Exception for already active branch transactions."""
193

194
class HeldCursorRequiresSameIsolationLevel(ProgrammingError):
195
    """Exception for held cursor isolation level requirements."""
196

197
class InappropriateAccessModeForBranchTransaction(ProgrammingError):
198
    """Exception for inappropriate access mode."""
199

200
class InappropriateIsolationLevelForBranchTransaction(ProgrammingError):
201
    """Exception for inappropriate isolation level."""
202

203
class NoActiveSqlTransactionForBranchTransaction(ProgrammingError):
204
    """Exception for no active SQL transaction."""
205

206
class ReadOnlySqlTransaction(ProgrammingError):
207
    """Exception for read-only transactions."""
208

209
class SchemaAndDataStatementMixingNotSupported(ProgrammingError):
210
    """Exception for mixed schema and data statements."""
211

212
class NoActiveSqlTransaction(ProgrammingError):
213
    """Exception for no active SQL transaction."""
214

215
class InFailedSqlTransaction(ProgrammingError):
216
    """Exception for failed SQL transactions."""
217
```
218

219
### Replication Control Exceptions
220

221
Specialized exceptions for controlling replication streams.
222

223
```python { .api }
224
class StopReplication(Exception):
225
    """
226
    Exception to stop replication stream.
227
    
228
    Used in replication message handlers to cleanly stop streaming
229
    replication when certain conditions are met.
230
    """
231
```
232

233
Usage examples:
234

235
```python
236
from psycopg2.extras import StopReplication
237

238
def message_handler(msg):
239
    if msg.data_start > target_lsn:
240
        # Stop replication when we reach target LSN
241
        raise StopReplication()
242
    
243
    # Process message
244
    process_message(msg.payload)
245

246
# In replication loop
247
try:
248
    for msg in replication_cursor:
249
        message_handler(msg)
250
except StopReplication:
251
    print("Replication stopped by handler")
252
```
253

254
### Error Code Lookup
255

256
Functions for looking up error codes and exception classes.
257

258
```python { .api }
259
def lookup(code):
260
    """
261
    Look up exception class by PostgreSQL error code.
262
    
263
    Parameters:
264
    - code (str): PostgreSQL error code (SQLSTATE)
265
    
266
    Returns:
267
    type: Exception class for the error code
268
    
269
    Raises:
270
    KeyError: If error code is not found
271
    """
272
```
273

274
Usage from errorcodes module:
275

276
```python { .api }
277
def lookup(code, _cache={}):
278
    """
279
    Look up symbolic name by PostgreSQL error code.
280
    
281
    Parameters:
282
    - code (str): PostgreSQL error code (SQLSTATE)
283
    
284
    Returns:
285
    str: Symbolic name for the error code
286
    
287
    Raises:
288
    KeyError: If error code is not found
289
    """
290
```
291

292
### Comprehensive Error Handling Examples
293

294
```python
295
import psycopg2
296
from psycopg2 import errors, errorcodes
297

298
# Basic exception handling
299
try:
300
    cur.execute("INSERT INTO users (id, email) VALUES (%s, %s)", (1, 'user@example.com'))
301
except psycopg2.Error as e:
302
    print(f"Database error: {e}")
303
    print(f"Error code: {e.pgcode}")
304
    print(f"Error message: {e.pgerror}")
305

306
# Specific error handling
307
try:
308
    cur.execute("INSERT INTO users (id, email) VALUES (%s, %s)", (1, 'duplicate@example.com'))
309
except psycopg2.IntegrityError as e:
310
    if e.pgcode == errorcodes.UNIQUE_VIOLATION:
311
        print("Duplicate key violation")
312
        # Handle duplicate entry
313
    elif e.pgcode == errorcodes.FOREIGN_KEY_VIOLATION:
314
        print("Foreign key constraint violation")
315
        # Handle foreign key error
316
    else:
317
        print(f"Other integrity error: {e}")
318

319
# Using specific exception classes
320
try:
321
    cur.execute("SELECT * FROM nonexistent_table")
322
except errors.UndefinedTable:
323
    print("Table does not exist")
324

325
try:
326
    cur.execute("SELECT nonexistent_column FROM users")
327
except errors.UndefinedColumn:
328
    print("Column does not exist")
329

330
try:
331
    cur.execute("SELECT * FROM users WHERE invalid_syntax")
332
except errors.SyntaxError:
333
    print("SQL syntax error")
334

335
# Connection and operational errors
336
try:
337
    conn = psycopg2.connect(host="nonexistent", database="test")
338
except psycopg2.OperationalError as e:
339
    print(f"Cannot connect: {e}")
340

341
try:
342
    cur.execute("LOCK TABLE users")
343
    # Long operation
344
except errors.QueryCanceledError:
345
    print("Query was canceled")
346
except errors.DeadlockDetected:
347
    print("Deadlock detected - retry transaction")
348

349
# Transaction errors
350
try:
351
    with conn:
352
        cur.execute("INSERT INTO users VALUES (%s)", (invalid_data,))
353
except errors.SerializationFailure:
354
    print("Serialization failure - retry transaction")
355
except errors.DeadlockDetected:
356
    print("Deadlock - retry transaction")
357
```
358

359
### Detailed Error Diagnostics
360

361
Access to comprehensive PostgreSQL error information.
362

363
```python
364
try:
365
    cur.execute("INSERT INTO orders (customer_id, product_id) VALUES (%s, %s)", 
366
               (999999, 123))  # Non-existent customer
367
except psycopg2.IntegrityError as e:
368
    diag = e.diag
369
    
370
    print(f"Severity: {diag.severity}")
371
    print(f"SQL State: {diag.sqlstate}")
372
    print(f"Message: {diag.message_primary}")
373
    print(f"Detail: {diag.message_detail}")
374
    print(f"Hint: {diag.message_hint}")
375
    print(f"Position: {diag.statement_position}")
376
    print(f"Context: {diag.context}")
377
    print(f"Schema: {diag.schema_name}")
378
    print(f"Table: {diag.table_name}")
379
    print(f"Column: {diag.column_name}")
380
    print(f"Constraint: {diag.constraint_name}")
381
    print(f"Source file: {diag.source_file}")
382
    print(f"Source line: {diag.source_line}")
383
    print(f"Source function: {diag.source_function}")
384

385
# Error context for nested operations
386
try:
387
    cur.execute("SELECT some_function(%s)", (invalid_param,))
388
except psycopg2.Error as e:
389
    diag = e.diag
390
    print(f"Internal query: {diag.internal_query}")
391
    print(f"Internal position: {diag.internal_position}")
392
    print(f"Context: {diag.context}")
393
```
394

395
### Custom Error Handling Patterns
396

397
```python
398
def safe_execute(cursor, query, params=None, retries=3):
399
    """Execute query with automatic retry for transient errors."""
400
    for attempt in range(retries):
401
        try:
402
            cursor.execute(query, params)
403
            return cursor.fetchall()
404
        except (errors.DeadlockDetected, errors.SerializationFailure) as e:
405
            if attempt < retries - 1:
406
                print(f"Retrying due to {type(e).__name__} (attempt {attempt + 1})")
407
                cursor.connection.rollback()
408
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
409
                continue
410
            raise
411
        except psycopg2.Error:
412
            # Don't retry for other errors
413
            raise
414

415
def handle_constraint_violation(e):
416
    """Handle different types of constraint violations."""
417
    if e.pgcode == errorcodes.UNIQUE_VIOLATION:
418
        constraint = e.diag.constraint_name
419
        if constraint == 'users_email_key':
420
            return "Email already registered"
421
        elif constraint == 'users_username_key':  
422
            return "Username already taken"
423
        else:
424
            return f"Duplicate value for {constraint}"
425
    
426
    elif e.pgcode == errorcodes.FOREIGN_KEY_VIOLATION:
427
        table = e.diag.table_name
428
        constraint = e.diag.constraint_name
429
        return f"Referenced record not found: {constraint} in {table}"
430
    
431
    elif e.pgcode == errorcodes.CHECK_VIOLATION:
432
        constraint = e.diag.constraint_name
433
        return f"Data validation failed: {constraint}"
434
    
435
    elif e.pgcode == errorcodes.NOT_NULL_VIOLATION:
436
        column = e.diag.column_name
437
        return f"Required field missing: {column}"
438
    
439
    else:
440
        return f"Data integrity error: {e.pgerror}"
441

442
# Usage
443
try:
444
    cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)",
445
               ('existing_user', 'new@email.com'))
446
except psycopg2.IntegrityError as e:
447
    error_message = handle_constraint_violation(e)
448
    print(f"Cannot create user: {error_message}")
449
```
450

451
### Error Logging and Monitoring
452

453
```python
454
import logging
455
from psycopg2.extras import LoggingConnection
456

457
# Setup comprehensive error logging
458
logging.basicConfig(
459
    level=logging.ERROR,
460
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
461
)
462

463
logger = logging.getLogger('database')
464

465
def log_database_error(e, query=None, params=None):
466
    """Log database errors with context."""
467
    error_info = {
468
        'error_type': type(e).__name__,
469
        'pgcode': getattr(e, 'pgcode', None),
470
        'pgerror': getattr(e, 'pgerror', None),
471
        'query': query,
472
        'params': params
473
    }
474
    
475
    if hasattr(e, 'diag') and e.diag:
476
        error_info.update({
477
            'severity': e.diag.severity,
478
            'sqlstate': e.diag.sqlstate,
479
            'message_primary': e.diag.message_primary,
480
            'message_detail': e.diag.message_detail,
481
            'table_name': e.diag.table_name,
482
            'constraint_name': e.diag.constraint_name
483
        })
484
    
485
    logger.error("Database error occurred", extra=error_info)
486

487
# Usage with error logging
488
try:
489
    query = "INSERT INTO users (email) VALUES (%s)"
490
    params = ('invalid-email',)
491
    cur.execute(query, params)
492
except psycopg2.Error as e:
493
    log_database_error(e, query, params)
494
    raise
495
```
496

497
## Types
498

499
### Error Code Constants
500

501
```python { .api }
502
# Major error classes (first 2 characters of SQLSTATE)
503
CLASS_SUCCESSFUL_COMPLETION: str = '00'
504
CLASS_WARNING: str = '01'  
505
CLASS_NO_DATA: str = '02'
506
CLASS_SQL_STATEMENT_NOT_YET_COMPLETE: str = '03'
507
CLASS_CONNECTION_EXCEPTION: str = '08'
508
CLASS_TRIGGERED_ACTION_EXCEPTION: str = '09'
509
CLASS_FEATURE_NOT_SUPPORTED: str = '0A'
510
CLASS_INVALID_TRANSACTION_INITIATION: str = '0B'
511
CLASS_LOCATOR_EXCEPTION: str = '0F'
512
CLASS_INVALID_GRANTOR: str = '0L'
513
CLASS_INVALID_ROLE_SPECIFICATION: str = '0P'
514
CLASS_DIAGNOSTICS_EXCEPTION: str = '0Z'
515
CLASS_CASE_NOT_FOUND: str = '20'
516
CLASS_CARDINALITY_VIOLATION: str = '21'
517
CLASS_DATA_EXCEPTION: str = '22'
518
CLASS_INTEGRITY_CONSTRAINT_VIOLATION: str = '23'
519
CLASS_INVALID_CURSOR_STATE: str = '24'
520
CLASS_INVALID_TRANSACTION_STATE: str = '25'
521
CLASS_INVALID_SQL_STATEMENT_NAME: str = '26'
522
CLASS_TRIGGERED_DATA_CHANGE_VIOLATION: str = '27'
523
CLASS_INVALID_AUTHORIZATION_SPECIFICATION: str = '28'
524
CLASS_DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST: str = '2B'
525
CLASS_INVALID_TRANSACTION_TERMINATION: str = '2D'
526
CLASS_SQL_ROUTINE_EXCEPTION: str = '2F'
527
CLASS_INVALID_CURSOR_NAME: str = '34'
528
CLASS_EXTERNAL_ROUTINE_EXCEPTION: str = '38'
529
CLASS_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION: str = '39'
530
CLASS_SAVEPOINT_EXCEPTION: str = '3B'
531
CLASS_INVALID_CATALOG_NAME: str = '3D'
532
CLASS_INVALID_SCHEMA_NAME: str = '3F'
533
CLASS_TRANSACTION_ROLLBACK: str = '40'
534
CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: str = '42'
535
CLASS_WITH_CHECK_OPTION_VIOLATION: str = '44'
536
CLASS_INSUFFICIENT_RESOURCES: str = '53'
537
CLASS_PROGRAM_LIMIT_EXCEEDED: str = '54'
538
CLASS_OBJECT_NOT_IN_PREREQUISITE_STATE: str = '55'
539
CLASS_OPERATOR_INTERVENTION: str = '57'
540
CLASS_SYSTEM_ERROR: str = '58'
541
CLASS_SNAPSHOT_FAILURE: str = '72'
542
CLASS_CONFIGURATION_FILE_ERROR: str = 'F0'
543
CLASS_FOREIGN_DATA_WRAPPER_ERROR: str = 'HV'
544
CLASS_PL_PGSQL_ERROR: str = 'P0'
545
CLASS_INTERNAL_ERROR: str = 'XX'
546

547
# Common specific error codes
548
UNIQUE_VIOLATION: str = '23505'
549
FOREIGN_KEY_VIOLATION: str = '23503'
550
CHECK_VIOLATION: str = '23514'
551
NOT_NULL_VIOLATION: str = '23502'
552
RESTRICT_VIOLATION: str = '23001'
553
EXCLUSION_VIOLATION: str = '23P01'
554
UNDEFINED_TABLE: str = '42P01'
555
UNDEFINED_COLUMN: str = '42703'
556
UNDEFINED_FUNCTION: str = '42883'
557
SYNTAX_ERROR: str = '42601'
558
INSUFFICIENT_PRIVILEGE: str = '42501'
559
DUPLICATE_TABLE: str = '42P07'
560
DUPLICATE_COLUMN: str = '42701'
561
DEADLOCK_DETECTED: str = '40P01'
562
SERIALIZATION_FAILURE: str = '40001'
563
QUERY_CANCELED: str = '57014'
564
```
565

566
### Exception Mapping
567

568
```python { .api }
569
# Exception class to SQLSTATE mapping
570
EXCEPTION_MAP = {
571
    '23505': UniqueViolation,
572
    '23503': ForeignKeyViolation,
573
    '23514': CheckViolation,
574
    '23502': NotNullViolation,
575
    '42P01': UndefinedTable,
576
    '42703': UndefinedColumn,
577
    '42883': UndefinedFunction,
578
    '42601': SyntaxError,
579
    '40P01': DeadlockDetected,
580
    '40001': SerializationFailure,
581
    '57014': QueryCanceledError,
582
    # ... hundreds more mappings
583
}
584
```