0
# Error Handling and Diagnostics
1

2
Complete DB-API 2.0 compliant exception hierarchy with PostgreSQL-specific error information, diagnostic details, and proper error classification for robust error handling.
3

4
## Capabilities
5

6
### Exception Hierarchy
7

8
Complete DB-API 2.0 compliant exception hierarchy for proper error handling and classification.
9

10
```python { .api }
11
class Error(Exception):
12
    """
13
    Base exception class for all psycopg errors.
14
    
15
    Root of the exception hierarchy, inherits from Python's Exception.
16
    All psycopg-specific exceptions derive from this class.
17
    """
18

19
class Warning(Exception):
20
    """
21
    Warning exception for important database warnings.
22
    
23
    Raised for database warning messages that don't prevent
24
    operation completion but indicate potential issues.
25
    """
26

27
class InterfaceError(Error):
28
    """
29
    Error related to database interface rather than database itself.
30
    
31
    Raised for problems with the psycopg interface, such as:
32
    - Invalid connection parameters
33
    - Interface misuse
34
    - Driver configuration issues
35
    """
36

37
class DatabaseError(Error):
38
    """
39
    Base class for database-related errors.
40
    
41
    Raised for errors that are related to the database rather
42
    than the interface. All specific database errors inherit from this.
43
    """
44

45
class DataError(DatabaseError):
46
    """
47
    Error due to problems with processed data.
48
    
49
    Raised when:
50
    - Data values are out of range
51
    - Type conversion fails
52
    - Data format is invalid
53
    - Constraint violations due to data content
54
    """
55

56
class OperationalError(DatabaseError):
57
    """
58
    Error related to database operation, not under programmer control.
59
    
60
    Raised for database operational issues:
61
    - Connection failures
62
    - Memory allocation errors
63
    - Database server errors
64
    - Network timeouts
65
    """
66

67
class IntegrityError(DatabaseError):
68
    """
69
    Error when database relational integrity is compromised.
70
    
71
    Raised for constraint violations:
72
    - Primary key violations
73
    - Foreign key violations  
74
    - Unique constraint violations
75
    - Check constraint violations
76
    """
77

78
class InternalError(DatabaseError):
79
    """
80
    Error when database encounters internal error.
81
    
82
    Raised when database system encounters unexpected internal errors:
83
    - Internal database errors
84
    - Transaction state errors
85
    - System resource errors
86
    """
87

88
class ProgrammingError(DatabaseError):
89
    """
90
    Error due to programmer error.
91
    
92
    Raised for programming mistakes:
93
    - SQL syntax errors
94
    - Table or column doesn't exist
95
    - Wrong number of parameters
96
    - Invalid operation for current state
97
    """
98

99
class NotSupportedError(DatabaseError):
100
    """
101
    Error when using unsupported database feature.
102
    
103
    Raised when attempting to use features not supported by:
104
    - Current PostgreSQL version
105
    - Current psycopg configuration
106
    - Database permissions
107
    """
108
```
109

110
### PostgreSQL-Specific Exceptions
111

112
Additional exceptions specific to PostgreSQL functionality and psycopg features.
113

114
```python { .api }
115
class ConnectionTimeout(OperationalError):
116
    """Raised when connection attempt times out"""
117

118
class QueryCanceled(OperationalError):
119
    """Raised when query is canceled by user or timeout"""
120

121
class TransactionRollbackError(OperationalError):
122
    """Raised when transaction is rolled back by server"""
123

124
class DeadlockDetected(OperationalError):
125
    """Raised when deadlock is detected and resolved"""
126

127
class SerializationFailure(OperationalError):
128
    """Raised when serializable transaction fails due to conflicts"""
129

130
class PipelineAborted(OperationalError):
131
    """Raised when pipeline operation fails due to pipeline being in aborted state"""
132

133
class UndefinedTable(ProgrammingError):
134
    """Raised when referencing non-existent table"""
135

136
class UndefinedColumn(ProgrammingError):
137
    """Raised when referencing non-existent column"""
138

139
class SyntaxError(ProgrammingError):
140
    """Raised for SQL syntax errors"""
141

142
class InvalidName(ProgrammingError):
143
    """Raised for invalid database object names"""
144

145
class InsufficientPrivilege(ProgrammingError):
146
    """Raised when operation requires higher privileges"""
147
```
148

149
### Error Diagnostic Information
150

151
Detailed error information and diagnostic data from PostgreSQL server.
152

153
```python { .api }
154
class Diagnostic:
155
    """
156
    Container for PostgreSQL error diagnostic information.
157
    
158
    Provides detailed information about database errors including
159
    error codes, messages, context, and location information.
160
    """
161
    
162
    @property
163
    def severity(self) -> str | None:
164
        """Error severity level (ERROR, FATAL, PANIC, WARNING, NOTICE, etc.)"""
165
    
166
    @property
167
    def severity_nonlocalized(self) -> str | None:
168
        """Error severity in English (not localized)"""
169
    
170
    @property
171
    def sqlstate(self) -> str | None:
172
        """
173
        SQLSTATE error code.
174
        
175
        Standard 5-character error code identifying error type.
176
        See PostgreSQL documentation for complete list.
177
        """
178
    
179
    @property
180
    def message_primary(self) -> str | None:
181
        """Primary error message"""
182
    
183
    @property
184
    def message_detail(self) -> str | None:
185
        """Detailed error message with additional context"""
186
    
187
    @property
188
    def message_hint(self) -> str | None:
189
        """Hint message suggesting how to fix the error"""
190
    
191
    @property
192
    def statement_position(self) -> int | None:
193
        """Character position in SQL statement where error occurred"""
194
    
195
    @property
196
    def internal_position(self) -> int | None:
197
        """Character position in internal query where error occurred"""
198
    
199
    @property
200
    def internal_query(self) -> str | None:
201
        """Internal query that caused the error"""
202
    
203
    @property
204
    def context(self) -> str | None:
205
        """Error context information"""
206
    
207
    @property
208
    def schema_name(self) -> str | None:
209
        """Schema name related to the error"""
210
    
211
    @property
212
    def table_name(self) -> str | None:
213
        """Table name related to the error"""
214
    
215
    @property
216
    def column_name(self) -> str | None:
217
        """Column name related to the error"""
218
    
219
    @property
220
    def datatype_name(self) -> str | None:
221
        """Data type name related to the error"""
222
    
223
    @property
224
    def constraint_name(self) -> str | None:
225
        """Constraint name related to the error"""
226
    
227
    @property
228
    def source_file(self) -> str | None:
229
        """PostgreSQL source file where error occurred"""
230
    
231
    @property
232
    def source_line(self) -> int | None:
233
        """Line number in PostgreSQL source file"""
234
    
235
    @property
236
    def source_function(self) -> str | None:
237
        """Function name in PostgreSQL source code"""
238

239
# Exception classes have diagnostic property
240
class DatabaseError(Error):
241
    @property
242
    def diag(self) -> Diagnostic:
243
        """Diagnostic information for this error"""
244
```
245

246
### Connection Pool Exceptions
247

248
Exceptions specific to connection pooling functionality.
249

250
```python { .api }
251
# Note: Requires psycopg-pool package
252
from psycopg_pool import PoolError
253

254
class PoolError(Error):
255
    """Base class for connection pool errors"""
256

257
class PoolClosed(PoolError):
258
    """Raised when operating on closed pool"""
259

260
class PoolTimeout(PoolError):
261
    """Raised when pool operation times out"""
262

263
class TooManyRequests(PoolError):
264
    """Raised when pool cannot satisfy connection request"""
265
```
266

267
## Error Handling Patterns
268

269
### Basic Exception Handling
270

271
```python
272
import psycopg
273
from psycopg import errors
274

275
try:
276
    with psycopg.connect("dbname=test user=postgres") as conn:
277
        with conn.cursor() as cur:
278
            cur.execute("SELECT * FROM nonexistent_table")
279
            
280
except errors.UndefinedTable as e:
281
    print(f"Table not found: {e}")
282
    print(f"SQLSTATE: {e.diag.sqlstate}")
283
    print(f"Detail: {e.diag.message_detail}")
284
    
285
except errors.ProgrammingError as e:
286
    print(f"Programming error: {e}")
287
    
288
except errors.OperationalError as e:
289
    print(f"Database operational error: {e}")
290
    
291
except errors.Error as e:
292
    print(f"Database error: {e}")
293
```
294

295
### Detailed Error Analysis
296

297
```python
298
def analyze_database_error(error):
299
    """Analyze database error and provide detailed information"""
300
    
301
    if not isinstance(error, psycopg.DatabaseError):
302
        return f"Non-database error: {error}"
303
    
304
    diag = error.diag
305
    analysis = []
306
    
307
    # Basic error info
308
    analysis.append(f"Error Type: {type(error).__name__}")
309
    analysis.append(f"SQLSTATE: {diag.sqlstate}")
310
    analysis.append(f"Severity: {diag.severity}")
311
    analysis.append(f"Message: {diag.message_primary}")
312
    
313
    # Additional details
314
    if diag.message_detail:
315
        analysis.append(f"Detail: {diag.message_detail}")
316
    
317
    if diag.message_hint:
318
        analysis.append(f"Hint: {diag.message_hint}")
319
    
320
    # Location information
321
    if diag.statement_position:
322
        analysis.append(f"Error at position: {diag.statement_position}")
323
    
324
    # Object information
325
    objects = []
326
    if diag.schema_name:
327
        objects.append(f"schema: {diag.schema_name}")
328
    if diag.table_name:
329
        objects.append(f"table: {diag.table_name}")
330
    if diag.column_name:
331
        objects.append(f"column: {diag.column_name}")
332
    if diag.constraint_name:
333
        objects.append(f"constraint: {diag.constraint_name}")
334
    
335
    if objects:
336
        analysis.append(f"Related objects: {', '.join(objects)}")
337
    
338
    # Context
339
    if diag.context:
340
        analysis.append(f"Context: {diag.context}")
341
    
342
    return "\n".join(analysis)
343

344
# Usage
345
try:
346
    with conn.cursor() as cur:
347
        cur.execute("INSERT INTO users (id, email) VALUES (1, 'duplicate@example.com')")
348
except psycopg.IntegrityError as e:
349
    print(analyze_database_error(e))
350
    # Output:
351
    # Error Type: IntegrityError
352
    # SQLSTATE: 23505
353
    # Severity: ERROR
354
    # Message: duplicate key value violates unique constraint "users_email_key"
355
    # Detail: Key (email)=(duplicate@example.com) already exists.
356
    # Related objects: table: users, constraint: users_email_key
357
```
358

359
### Error Recovery Strategies
360

361
```python
362
import time
363
from psycopg import errors
364

365
def execute_with_retry(conn, query, params=None, max_retries=3):
366
    """Execute query with automatic retry for transient errors"""
367
    
368
    for attempt in range(max_retries + 1):
369
        try:
370
            with conn.cursor() as cur:
371
                cur.execute(query, params)
372
                return cur.fetchall()
373
                
374
        except errors.SerializationFailure:
375
            if attempt < max_retries:
376
                # Retry serialization failures
377
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
378
                continue
379
            raise
380
            
381
        except errors.DeadlockDetected:
382
            if attempt < max_retries:
383
                # Retry deadlocks with random delay
384
                time.sleep(0.1 + random.random() * 0.2)
385
                continue
386
            raise
387
            
388
        except errors.ConnectionTimeout:
389
            if attempt < max_retries:
390
                # Retry connection timeouts
391
                time.sleep(1.0)
392
                continue
393
            raise
394
            
395
        except (errors.DataError, errors.ProgrammingError):
396
            # Don't retry programming errors
397
            raise
398
            
399
        except errors.OperationalError as e:
400
            # Retry some operational errors
401
            if "server closed the connection" in str(e).lower():
402
                if attempt < max_retries:
403
                    time.sleep(1.0)
404
                    continue
405
            raise
406

407
def safe_database_operation(conn, operation_func, *args, **kwargs):
408
    """Safely execute database operation with comprehensive error handling"""
409
    
410
    try:
411
        return operation_func(conn, *args, **kwargs)
412
        
413
    except errors.IntegrityError as e:
414
        # Handle constraint violations
415
        if e.diag.sqlstate == "23505":  # Unique violation
416
            raise ValueError(f"Duplicate value: {e.diag.message_detail}")
417
        elif e.diag.sqlstate == "23503":  # Foreign key violation
418
            raise ValueError(f"Reference error: {e.diag.message_detail}")
419
        else:
420
            raise ValueError(f"Data integrity error: {e}")
421
            
422
    except errors.DataError as e:
423
        # Handle data format/type errors
424
        raise ValueError(f"Invalid data: {e.diag.message_primary}")
425
        
426
    except errors.ProgrammingError as e:
427
        # Handle SQL/programming errors  
428
        if e.diag.sqlstate == "42P01":  # Undefined table
429
            raise RuntimeError(f"Table not found: {e.diag.table_name}")
430
        elif e.diag.sqlstate == "42703":  # Undefined column
431
            raise RuntimeError(f"Column not found: {e.diag.column_name}")
432
        else:
433
            raise RuntimeError(f"SQL error: {e}")
434
            
435
    except errors.OperationalError as e:
436
        # Handle operational errors
437
        raise ConnectionError(f"Database unavailable: {e}")
438
```
439

440
### Connection Error Handling
441

442
```python
443
def create_robust_connection(conninfo, max_attempts=3):
444
    """Create database connection with retry logic"""
445
    
446
    for attempt in range(max_attempts):
447
        try:
448
            conn = psycopg.connect(conninfo)
449
            
450
            # Test connection
451
            with conn.cursor() as cur:
452
                cur.execute("SELECT 1")
453
                cur.fetchone()
454
            
455
            return conn
456
            
457
        except errors.OperationalError as e:
458
            if attempt < max_attempts - 1:
459
                print(f"Connection attempt {attempt + 1} failed: {e}")
460
                time.sleep(2 ** attempt)  # Exponential backoff
461
                continue
462
            
463
            # Analyze connection error
464
            error_msg = str(e).lower()
465
            if "connection refused" in error_msg:
466
                raise ConnectionError("Database server is not running")
467
            elif "authentication failed" in error_msg:
468
                raise ConnectionError("Invalid credentials")
469
            elif "database" in error_msg and "does not exist" in error_msg:
470
                raise ConnectionError("Database does not exist")
471
            else:
472
                raise ConnectionError(f"Cannot connect to database: {e}")
473
        
474
        except errors.InterfaceError as e:
475
            raise ConnectionError(f"Connection interface error: {e}")
476

477
def connection_health_check(conn):
478
    """Check if connection is healthy and usable"""
479
    
480
    try:
481
        if conn.closed:
482
            return False, "Connection is closed"
483
        
484
        if conn.broken:
485
            return False, "Connection is broken"
486
        
487
        # Test with simple query
488
        with conn.cursor() as cur:
489
            cur.execute("SELECT 1")
490
            result = cur.fetchone()
491
            if result != (1,):
492
                return False, "Unexpected query result"
493
        
494
        return True, "Connection is healthy"
495
        
496
    except Exception as e:
497
        return False, f"Health check failed: {e}"
498
```
499

500
### Transaction Error Handling
501

502
```python
503
def safe_transaction(conn, transaction_func, *args, **kwargs):
504
    """Execute function within transaction with proper error handling"""
505
    
506
    savepoint_name = None
507
    try:
508
        # Start transaction
509
        if conn.autocommit:
510
            conn.autocommit = False
511
        
512
        # Create savepoint for nested transactions
513
        if conn.info.transaction_status != 0:  # Already in transaction
514
            savepoint_name = f"sp_{int(time.time() * 1000000)}"
515
            with conn.cursor() as cur:
516
                cur.execute(f"SAVEPOINT {savepoint_name}")
517
        
518
        # Execute transaction function
519
        result = transaction_func(conn, *args, **kwargs)
520
        
521
        # Commit or release savepoint
522
        if savepoint_name:
523
            with conn.cursor() as cur:
524
                cur.execute(f"RELEASE SAVEPOINT {savepoint_name}")
525
        else:
526
            conn.commit()
527
        
528
        return result
529
        
530
    except errors.SerializationFailure:
531
        # Handle serialization failures
532
        if savepoint_name:
533
            with conn.cursor() as cur:
534
                cur.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name}")
535
        else:
536
            conn.rollback()
537
        raise
538
        
539
    except errors.DeadlockDetected:
540
        # Handle deadlocks
541
        if savepoint_name:
542
            with conn.cursor() as cur:
543
                cur.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name}")
544
        else:
545
            conn.rollback()
546
        raise
547
        
548
    except Exception:
549
        # Rollback on any other error
550
        if savepoint_name:
551
            try:
552
                with conn.cursor() as cur:
553
                    cur.execute(f"ROLLBACK TO SAVEPOINT {savepoint_name}")
554
            except:
555
                conn.rollback()  # Full rollback if savepoint fails
556
        else:
557
            conn.rollback()
558
        raise
559

560
# Usage
561
def transfer_money(conn, from_account, to_account, amount):
562
    """Transfer money between accounts safely"""
563
    
564
    def transfer_operation(conn):
565
        with conn.cursor() as cur:
566
            # Check source account balance
567
            cur.execute(
568
                "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
569
                (from_account,)
570
            )
571
            balance = cur.fetchone()
572
            if not balance or balance[0] < amount:
573
                raise ValueError("Insufficient funds")
574
            
575
            # Perform transfer
576
            cur.execute(
577
                "UPDATE accounts SET balance = balance - %s WHERE id = %s",
578
                (amount, from_account)
579
            )
580
            cur.execute(
581
                "UPDATE accounts SET balance = balance + %s WHERE id = %s", 
582
                (amount, to_account)
583
            )
584
        
585
        return True
586
    
587
    return safe_transaction(conn, transfer_operation)
588
```
589

590
## SQLSTATE Error Codes
591

592
Common PostgreSQL SQLSTATE codes for error classification:
593

594
```python { .api }
595
# Class 08 - Connection Exception  
596
CONNECTION_EXCEPTION = "08000"
597
CONNECTION_FAILURE = "08006"
598
CONNECTION_DOES_NOT_EXIST = "08003"
599

600
# Class 23 - Integrity Constraint Violation
601
INTEGRITY_CONSTRAINT_VIOLATION = "23000"
602
RESTRICT_VIOLATION = "23001"
603
NOT_NULL_VIOLATION = "23502"  
604
FOREIGN_KEY_VIOLATION = "23503"
605
UNIQUE_VIOLATION = "23505"
606
CHECK_VIOLATION = "23514"
607

608
# Class 42 - Syntax Error or Access Rule Violation
609
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION = "42000"
610
SYNTAX_ERROR = "42601"
611
INSUFFICIENT_PRIVILEGE = "42501"
612
UNDEFINED_TABLE = "42P01"
613
UNDEFINED_COLUMN = "42703"
614
UNDEFINED_FUNCTION = "42883"
615
DUPLICATE_TABLE = "42P07"
616
DUPLICATE_COLUMN = "42701"
617

618
# Class 40 - Transaction Rollback
619
TRANSACTION_ROLLBACK = "40000"
620
SERIALIZATION_FAILURE = "40001"
621
DEADLOCK_DETECTED = "40P01"
622

623
# Class 53 - Insufficient Resources
624
INSUFFICIENT_RESOURCES = "53000"
625
DISK_FULL = "53100"
626
OUT_OF_MEMORY = "53200"
627
TOO_MANY_CONNECTIONS = "53300"
628

629
# Class 57 - Operator Intervention
630
OPERATOR_INTERVENTION = "57000"
631
QUERY_CANCELED = "57014"
632
ADMIN_SHUTDOWN = "57P01"
633
CRASH_SHUTDOWN = "57P02"
634
CANNOT_CONNECT_NOW = "57P03"
635
```
636

637
## Best Practices
638

639
### Error Handling Guidelines
640

641
1. **Catch Specific Exceptions**: Use specific exception types rather than generic Exception
642
2. **Check SQLSTATE**: Use SQLSTATE codes for precise error classification
643
3. **Examine Diagnostics**: Use diagnostic information for detailed error analysis
644
4. **Implement Retry Logic**: Retry transient errors like serialization failures
645
5. **Log Error Details**: Include diagnostic information in error logs
646
6. **Clean Up Resources**: Ensure connections and cursors are properly closed
647
7. **User-Friendly Messages**: Convert technical errors to user-understandable messages