0
# Exception Handling
1

2
Comprehensive exception hierarchy mapping all PostgreSQL error codes to Python exceptions with detailed error information, proper inheritance structure, and practical error handling patterns.
3

4
## Capabilities
5

6
### Exception Hierarchy
7

8
AsyncPG provides a complete mapping of PostgreSQL SQLSTATE codes to specific Python exception classes, enabling precise error handling.
9

10
```python { .api }
11
class PostgresError(Exception):
12
    """Base class for all PostgreSQL server errors."""
13
    
14
    # Error details available as attributes
15
    severity: str           # Error severity (ERROR, FATAL, etc.)
16
    severity_en: str        # English severity
17
    sqlstate: str          # PostgreSQL SQLSTATE code
18
    message: str           # Primary error message
19
    detail: str            # Detailed error information
20
    hint: str              # Suggestion for fixing the error
21
    position: str          # Character position in query (if applicable)
22
    internal_position: str # Internal query position
23
    internal_query: str    # Internal query text
24
    context: str           # Error context
25
    schema_name: str       # Schema name (if applicable)
26
    table_name: str        # Table name (if applicable) 
27
    column_name: str       # Column name (if applicable)
28
    data_type_name: str    # Data type name (if applicable)
29
    constraint_name: str   # Constraint name (if applicable)
30

31
class FatalPostgresError(PostgresError):
32
    """A fatal error that should result in server disconnection."""
33

34
class UnknownPostgresError(FatalPostgresError):
35
    """An error with an unknown SQLSTATE code."""
36
```
37

38
### Client-Side Exceptions
39

40
Exceptions originating from the asyncpg client rather than PostgreSQL server.
41

42
```python { .api }
43
class InterfaceError(Exception):
44
    """An error caused by improper use of asyncpg API."""
45

46
class ClientConfigurationError(InterfaceError, ValueError):
47
    """An error caused by improper client configuration."""
48

49
class InterfaceWarning(UserWarning):
50
    """A warning for asyncpg API usage."""
51

52
class DataError(InterfaceError, ValueError):
53
    """Invalid query input data."""
54

55
class InternalClientError(Exception):
56
    """All unexpected errors not classified otherwise."""
57

58
class ProtocolError(InternalClientError):
59
    """Unexpected condition in PostgreSQL protocol handling."""
60

61
class UnsupportedClientFeatureError(InterfaceError):
62
    """Requested feature is unsupported by asyncpg."""
63

64
class UnsupportedServerFeatureError(InterfaceError):
65
    """Requested feature is unsupported by PostgreSQL server."""
66

67
class OutdatedSchemaCacheError(InternalClientError):
68
    """A value decoding error caused by a schema change."""
69

70
class TargetServerAttributeNotMatched(InternalClientError):
71
    """Could not find a host satisfying target attribute requirement."""
72
```
73

74
### Connection Errors
75

76
Errors related to database connectivity and authentication.
77

78
```python { .api }
79
class PostgresConnectionError(PostgresError):
80
    """Base class for connection-related errors."""
81

82
class ConnectionDoesNotExistError(PostgresConnectionError):
83
    """The connection does not exist (SQLSTATE: 08003)."""
84

85
class ConnectionFailureError(PostgresConnectionError):
86
    """Connection failure (SQLSTATE: 08006)."""
87

88
class ClientCannotConnectError(PostgresConnectionError):
89
    """Client cannot connect to server (SQLSTATE: 08001)."""
90

91
class ConnectionRejectionError(PostgresConnectionError):
92
    """Server rejected connection (SQLSTATE: 08004)."""
93

94
class ProtocolViolationError(PostgresConnectionError):
95
    """Protocol violation (SQLSTATE: 08P01)."""
96

97
class InvalidAuthorizationSpecificationError(PostgresError):
98
    """Authentication failed (SQLSTATE: 28000)."""
99

100
class InvalidPasswordError(InvalidAuthorizationSpecificationError):
101
    """Invalid password (SQLSTATE: 28P01)."""
102
```
103

104
#### Example Usage
105

106
```python
107
import asyncpg
108
import asyncio
109

110
async def connect_with_retry(dsn, max_retries=3):
111
    """Connect with automatic retry for transient failures."""
112
    
113
    for attempt in range(max_retries):
114
        try:
115
            return await asyncpg.connect(dsn, timeout=10.0)
116
            
117
        except asyncpg.ConnectionFailureError:
118
            print(f"Connection failed, attempt {attempt + 1}/{max_retries}")
119
            if attempt == max_retries - 1:
120
                raise
121
            await asyncio.sleep(2 ** attempt)  # Exponential backoff
122
            
123
        except asyncpg.InvalidPasswordError:
124
            print("Authentication failed - check credentials")
125
            raise  # Don't retry authentication errors
126
            
127
        except asyncpg.ClientCannotConnectError:
128
            print("Cannot reach server - check host and port")
129
            raise  # Don't retry unreachable server
130
```
131

132
### Data Errors
133

134
Errors related to data validation, type conversion, and constraint violations.
135

136
```python { .api }
137
class PostgresDataError(PostgresError):
138
    """Base class for PostgreSQL data-related errors (SQLSTATE: 22000)."""
139

140
class InvalidTextRepresentationError(PostgresDataError):
141
    """Invalid input syntax for data type (SQLSTATE: 22P02)."""
142

143
class InvalidBinaryRepresentationError(PostgresDataError):
144
    """Invalid binary representation (SQLSTATE: 22P03)."""
145

146
class NumericValueOutOfRangeError(PostgresDataError):
147
    """Numeric value out of range (SQLSTATE: 22003)."""
148

149
class DivisionByZeroError(PostgresDataError):
150
    """Division by zero (SQLSTATE: 22012)."""
151

152
class StringDataRightTruncationError(PostgresDataError):
153
    """String data right truncation (SQLSTATE: 22001)."""
154

155
class DatetimeFieldOverflowError(PostgresDataError):
156
    """Datetime field overflow (SQLSTATE: 22008)."""
157

158
class InvalidDatetimeFormatError(PostgresDataError):
159
    """Invalid datetime format (SQLSTATE: 22007)."""
160

161
class InvalidTimeZoneDisplacementValueError(PostgresDataError):
162
    """Invalid timezone displacement (SQLSTATE: 22009)."""
163
```
164

165
#### Example Usage
166

167
```python
168
async def safe_insert_user(conn, name, age, email):
169
    """Insert user with comprehensive data validation error handling."""
170
    
171
    try:
172
        return await conn.execute(
173
            "INSERT INTO users(name, age, email) VALUES($1, $2, $3)",
174
            name, age, email
175
        )
176
        
177
    except asyncpg.InvalidTextRepresentationError as e:
178
        print(f"Invalid data format: {e}")
179
        if 'age' in str(e):
180
            raise ValueError("Age must be a valid integer")
181
        elif 'email' in str(e):
182
            raise ValueError("Email format is invalid")
183
        else:
184
            raise ValueError(f"Invalid data format: {e}")
185
            
186
    except asyncpg.NumericValueOutOfRangeError:
187
        raise ValueError("Age value is out of valid range")
188
        
189
    except asyncpg.StringDataRightTruncationError as e:
190
        if 'name' in e.column_name:
191
            raise ValueError("Name is too long (maximum 100 characters)")
192
        elif 'email' in e.column_name:
193
            raise ValueError("Email is too long (maximum 255 characters)")
194
```
195

196
### Integrity Constraint Violations
197

198
Errors related to database constraints and referential integrity.
199

200
```python { .api }
201
class IntegrityConstraintViolationError(PostgresError):
202
    """Base class for constraint violations (SQLSTATE: 23000)."""
203

204
class NotNullViolationError(IntegrityConstraintViolationError):
205
    """NOT NULL constraint violation (SQLSTATE: 23502)."""
206

207
class ForeignKeyViolationError(IntegrityConstraintViolationError):
208
    """Foreign key constraint violation (SQLSTATE: 23503)."""
209

210
class UniqueViolationError(IntegrityConstraintViolationError):
211
    """Unique constraint violation (SQLSTATE: 23505)."""
212

213
class CheckViolationError(IntegrityConstraintViolationError):
214
    """Check constraint violation (SQLSTATE: 23514)."""
215

216
class ExclusionViolationError(IntegrityConstraintViolationError):
217
    """Exclusion constraint violation (SQLSTATE: 23P01)."""
218
```
219

220
#### Example Usage
221

222
```python
223
async def create_user_safely(conn, user_data):
224
    """Create user with proper constraint violation handling."""
225
    
226
    try:
227
        user_id = await conn.fetchval(
228
            """
229
            INSERT INTO users(username, email, age)
230
            VALUES($1, $2, $3)
231
            RETURNING id
232
            """,
233
            user_data['username'],
234
            user_data['email'], 
235
            user_data['age']
236
        )
237
        return user_id
238
        
239
    except asyncpg.UniqueViolationError as e:
240
        if 'username' in e.constraint_name:
241
            raise ValueError("Username already exists")
242
        elif 'email' in e.constraint_name:
243
            raise ValueError("Email address already registered")
244
        else:
245
            raise ValueError("Duplicate value in unique field")
246
            
247
    except asyncpg.NotNullViolationError as e:
248
        raise ValueError(f"Required field missing: {e.column_name}")
249
        
250
    except asyncpg.CheckViolationError as e:
251
        if 'age' in e.constraint_name:
252
            raise ValueError("Age must be between 13 and 120")
253
        else:
254
            raise ValueError(f"Data validation failed: {e.constraint_name}")
255
            
256
    except asyncpg.ForeignKeyViolationError as e:
257
        raise ValueError(f"Referenced record does not exist: {e.constraint_name}")
258

259
async def update_order_status(conn, order_id, status, user_id):
260
    """Update order with referential integrity checks."""
261
    
262
    try:
263
        await conn.execute(
264
            "UPDATE orders SET status = $1, updated_by = $2 WHERE id = $3",
265
            status, user_id, order_id
266
        )
267
        
268
    except asyncpg.ForeignKeyViolationError as e:
269
        if 'updated_by' in e.constraint_name:
270
            raise ValueError("Invalid user ID")
271
        else:
272
            raise ValueError("Referenced record does not exist")
273
```
274

275
### Transaction Errors
276

277
Errors related to transaction state and concurrency control.
278

279
```python { .api }
280
class InvalidTransactionStateError(PostgresError):
281
    """Base class for transaction state errors (SQLSTATE: 25000)."""
282

283
class ActiveSQLTransactionError(InvalidTransactionStateError):
284
    """Cannot start transaction - already in one (SQLSTATE: 25001)."""
285

286
class NoActiveSQLTransactionError(InvalidTransactionStateError):
287
    """No active transaction (SQLSTATE: 25P01)."""
288

289
class InFailedSQLTransactionError(InvalidTransactionStateError):
290
    """Current transaction is aborted (SQLSTATE: 25P02)."""
291

292
class ReadOnlySQLTransactionError(InvalidTransactionStateError):
293
    """Cannot execute in read-only transaction (SQLSTATE: 25006)."""
294

295
class TransactionRollbackError(PostgresError):
296
    """Base class for transaction rollback errors (SQLSTATE: 40000)."""
297

298
class SerializationError(TransactionRollbackError):
299
    """Serialization failure (SQLSTATE: 40001)."""
300

301
class DeadlockDetectedError(TransactionRollbackError):
302
    """Deadlock detected (SQLSTATE: 40P01)."""
303
```
304

305
#### Example Usage
306

307
```python
308
async def transfer_money_with_retry(conn, from_account, to_account, amount, max_retries=3):
309
    """Money transfer with serialization error retry."""
310
    
311
    for attempt in range(max_retries):
312
        try:
313
            async with conn.transaction(isolation='serializable'):
314
                # Check balance
315
                balance = await conn.fetchval(
316
                    "SELECT balance FROM accounts WHERE id = $1",
317
                    from_account
318
                )
319
                
320
                if balance < amount:
321
                    raise ValueError("Insufficient funds")
322
                
323
                # Perform transfer
324
                await conn.execute(
325
                    "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
326
                    amount, from_account
327
                )
328
                await conn.execute(  
329
                    "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
330
                    amount, to_account
331
                )
332
                
333
                return True  # Success
334
                
335
        except asyncpg.SerializationError:
336
            if attempt == max_retries - 1:
337
                raise TransferFailedError("Transfer failed due to concurrent modifications")
338
            # Retry with exponential backoff
339
            await asyncio.sleep(0.1 * (2 ** attempt))
340
            
341
        except asyncpg.DeadlockDetectedError:
342
            if attempt == max_retries - 1:
343
                raise TransferFailedError("Transfer failed due to deadlock")
344
            await asyncio.sleep(0.05 * (2 ** attempt))
345
            
346
        except asyncpg.ReadOnlySQLTransactionError:
347
            raise TransferFailedError("Cannot perform transfer in read-only transaction")
348

349
async def safe_transaction_operation(conn, operation):
350
    """Execute operation with transaction error handling."""
351
    
352
    try:
353
        async with conn.transaction():
354
            return await operation(conn)
355
            
356
    except asyncpg.ActiveSQLTransactionError:
357
        # Already in transaction, execute directly
358
        return await operation(conn)
359
        
360
    except asyncpg.InFailedSQLTransactionError:
361
        # Transaction is in failed state, must rollback
362
        print("Transaction failed, rolling back and retrying")
363
        raise  # Let caller handle retry logic
364
```
365

366
### Syntax and Access Errors
367

368
Errors related to SQL syntax, permissions, and schema objects.
369

370
```python { .api }
371
class SyntaxOrAccessError(PostgresError):
372
    """Base class for syntax and access errors (SQLSTATE: 42000)."""
373

374
class PostgresSyntaxError(SyntaxOrAccessError):
375
    """SQL syntax error (SQLSTATE: 42601)."""
376

377
class InsufficientPrivilegeError(SyntaxOrAccessError):
378
    """Insufficient privilege (SQLSTATE: 42501)."""
379

380
class UndefinedTableError(SyntaxOrAccessError):
381
    """Table does not exist (SQLSTATE: 42P01)."""
382

383
class UndefinedColumnError(SyntaxOrAccessError):
384
    """Column does not exist (SQLSTATE: 42703)."""
385

386
class UndefinedFunctionError(SyntaxOrAccessError):
387
    """Function does not exist (SQLSTATE: 42883)."""
388

389
class DuplicateTableError(SyntaxOrAccessError):
390
    """Table already exists (SQLSTATE: 42P07)."""
391

392
class DuplicateColumnError(SyntaxOrAccessError):
393
    """Column already exists (SQLSTATE: 42701)."""
394

395
class AmbiguousColumnError(SyntaxOrAccessError):
396
    """Column reference is ambiguous (SQLSTATE: 42702)."""
397
```
398

399
#### Example Usage
400

401
```python
402
async def dynamic_query_executor(conn, table_name, columns, conditions):
403
    """Execute dynamic queries with comprehensive error handling."""
404
    
405
    # Build query dynamically
406
    column_list = ', '.join(columns)
407
    where_clause = ' AND '.join(f"{k} = ${i+1}" for i, k in enumerate(conditions.keys()))
408
    query = f"SELECT {column_list} FROM {table_name} WHERE {where_clause}"
409
    
410
    try:
411
        return await conn.fetch(query, *conditions.values())
412
        
413
    except asyncpg.UndefinedTableError:
414
        raise ValueError(f"Table '{table_name}' does not exist")
415
        
416
    except asyncpg.UndefinedColumnError as e:
417
        raise ValueError(f"Column does not exist: {e.message}")
418
        
419
    except asyncpg.PostgresSyntaxError as e:
420
        raise ValueError(f"Invalid SQL syntax: {e.message}")
421
        
422
    except asyncpg.InsufficientPrivilegeError:
423
        raise PermissionError(f"Access denied to table '{table_name}'")
424
        
425
    except asyncpg.AmbiguousColumnError as e:
426
        raise ValueError(f"Ambiguous column reference: {e.message}")
427

428
async def safe_table_creation(conn, table_name, schema):
429
    """Create table with proper error handling."""
430
    
431
    try:
432
        await conn.execute(f"CREATE TABLE {table_name} ({schema})")
433
        return True
434
        
435
    except asyncpg.DuplicateTableError:
436
        print(f"Table {table_name} already exists")
437
        return False
438
        
439
    except asyncpg.PostgresSyntaxError as e:
440
        raise ValueError(f"Invalid table schema: {e.message}")
441
        
442
    except asyncpg.InsufficientPrivilegeError:
443
        raise PermissionError("Cannot create table - insufficient privileges")
444
```
445

446
### System Errors
447

448
Errors related to system resources and server limitations.
449

450
```python { .api }
451
class InsufficientResourcesError(PostgresError):
452
    """Base class for resource errors (SQLSTATE: 53000)."""
453

454
class DiskFullError(InsufficientResourcesError):
455
    """Disk full (SQLSTATE: 53100)."""
456

457
class OutOfMemoryError(InsufficientResourcesError):
458
    """Out of memory (SQLSTATE: 53200)."""
459

460
class TooManyConnectionsError(InsufficientResourcesError):
461
    """Too many connections (SQLSTATE: 53300)."""
462

463
class ProgramLimitExceededError(PostgresError):
464
    """Base class for program limit errors (SQLSTATE: 54000)."""
465

466
class StatementTooComplexError(ProgramLimitExceededError):
467
    """Statement too complex (SQLSTATE: 54001)."""
468

469
class TooManyColumnsError(ProgramLimitExceededError):
470
    """Too many columns (SQLSTATE: 54011)."""
471
```
472

473
### Error Information Access
474

475
Access detailed error information for logging and debugging.
476

477
```python
478
async def detailed_error_handler():
479
    """Demonstrate accessing detailed error information."""
480
    
481
    try:
482
        await conn.execute("INSERT INTO users(id, name) VALUES(1, 'Alice')")
483
        
484
    except asyncpg.PostgresError as e:
485
        # Access all available error details
486
        error_info = {
487
            'sqlstate': e.sqlstate,
488
            'severity': e.severity,
489
            'message': e.message,
490
            'detail': e.detail,
491
            'hint': e.hint,
492
            'position': e.position,
493
            'context': e.context,
494
            'schema_name': e.schema_name,
495
            'table_name': e.table_name,
496
            'column_name': e.column_name,
497
            'constraint_name': e.constraint_name,
498
        }
499
        
500
        # Log comprehensive error information
501
        print(f"PostgreSQL Error {e.sqlstate}: {e.message}")
502
        if e.detail:
503
            print(f"Detail: {e.detail}")
504
        if e.hint:
505
            print(f"Hint: {e.hint}")
506
        if e.position:
507
            print(f"Position: {e.position}")
508
            
509
        # Error-specific handling
510
        if isinstance(e, asyncpg.UniqueViolationError):
511
            handle_duplicate_key(e.constraint_name)
512
        elif isinstance(e, asyncpg.ForeignKeyViolationError):
513
            handle_referential_integrity(e.constraint_name)
514
```
515

516
## Types
517

518
```python { .api }
519
# Base exception types
520
class PostgresError(Exception):
521
    """Base PostgreSQL error with detailed attributes."""
522
    
523
class FatalPostgresError(PostgresError):
524
    """Fatal error requiring disconnection."""
525
    
526
class UnknownPostgresError(FatalPostgresError):
527
    """Error with unknown SQLSTATE code."""
528
    
529
class InterfaceError(Exception):
530
    """Client-side API usage error."""
531
    
532
class InterfaceWarning(UserWarning):
533
    """Client-side API usage warning."""
534
    
535
class DataError(InterfaceError, ValueError):
536
    """Invalid query input data."""
537
    
538
class InternalClientError(Exception):
539
    """Internal asyncpg error."""
540
    
541
# Error detail attributes available on PostgresError instances
542
ErrorDetails = typing.TypedDict('ErrorDetails', {
543
    'severity': str,
544
    'severity_en': str, 
545
    'sqlstate': str,
546
    'message': str,
547
    'detail': str,
548
    'hint': str,
549
    'position': str,
550
    'internal_position': str,
551
    'internal_query': str,
552
    'context': str,
553
    'schema_name': str,
554
    'table_name': str,
555
    'column_name': str,
556
    'data_type_name': str,
557
    'constraint_name': str,
558
})
559
```