Error Handling and Diagnostics

class Error(Exception):
    """Base exception class for all psycopg2 errors."""

class Warning(Exception):
    """Exception for database warnings."""

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

class DatabaseError(Error):
    """Exception for database engine errors."""

class DataError(DatabaseError):
    """Exception for data-related errors (invalid data, numeric overflow, etc.)."""

class OperationalError(DatabaseError):
    """Exception for operation-related errors (disconnect, memory allocation, etc.)."""

class IntegrityError(DatabaseError):
    """Exception for database integrity violations (foreign key, unique constraints, etc.)."""

class InternalError(DatabaseError):
    """Exception for database internal errors (cursor not valid, etc.)."""

class ProgrammingError(DatabaseError):
    """Exception for SQL programming errors (table not found, syntax error, etc.)."""

class NotSupportedError(DatabaseError):
    """Exception for unsupported operations (unsupported function, API, etc.)."""

class QueryCanceledError(OperationalError):
    """Exception for query cancellation."""

class TransactionRollbackError(OperationalError):
    """Exception for transaction rollback conditions."""

import psycopg2
from psycopg2 import (
    Error, DatabaseError, IntegrityError, ProgrammingError,
    OperationalError, DataError
)

conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")

try:
    with conn.cursor() as cur:
        # This will raise ProgrammingError if table doesn't exist
        cur.execute("SELECT * FROM nonexistent_table")
        
except ProgrammingError as e:
    print(f"SQL Programming Error: {e}")
    print(f"Error code: {e.pgcode}")
    print(f"Error class: {e.__class__.__name__}")

try:
    with conn.cursor() as cur:
        # This will raise IntegrityError if violates unique constraint
        cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
        conn.commit()
        
except IntegrityError as e:
    print(f"Database Integrity Error: {e}")
    print(f"SQLSTATE: {e.pgcode}")
    conn.rollback()

try:
    with conn.cursor() as cur:
        # This will raise DataError for invalid data type
        cur.execute("INSERT INTO users (age) VALUES (%s)", ("not_a_number",))
        
except DataError as e:
    print(f"Data Error: {e}")
    conn.rollback()

conn.close()

def lookup(code):
    """
    Look up exception class by error code.
    
    Parameters:
    - code (str): PostgreSQL SQLSTATE error code
    
    Returns:
    type: Exception class corresponding to the error code
    """

from psycopg2.errors import lookup
from psycopg2.errorcodes import UNIQUE_VIOLATION, FOREIGN_KEY_VIOLATION

# Look up exception classes
unique_error_class = lookup(UNIQUE_VIOLATION)  # '23505'
fk_error_class = lookup(FOREIGN_KEY_VIOLATION)  # '23503'

print(f"Unique violation maps to: {unique_error_class.__name__}")
print(f"Foreign key violation maps to: {fk_error_class.__name__}")

# Use in exception handling
try:
    # Database operation that might fail
    cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
except unique_error_class as e:
    print(f"Caught unique violation: {e}")
except fk_error_class as e:
    print(f"Caught foreign key violation: {e}")

# Error class constants (2-character codes)
CLASS_SUCCESSFUL_COMPLETION: str  # '00'
CLASS_WARNING: str                # '01'
CLASS_NO_DATA: str               # '02'
CLASS_SQL_STATEMENT_NOT_YET_COMPLETE: str  # '03'
CLASS_CONNECTION_EXCEPTION: str   # '08'
CLASS_TRIGGERED_ACTION_EXCEPTION: str  # '09'
CLASS_FEATURE_NOT_SUPPORTED: str # '0A'
CLASS_INVALID_TRANSACTION_INITIATION: str  # '0B'
CLASS_LOCATOR_EXCEPTION: str     # '0F'
CLASS_INVALID_GRANTOR: str       # '0L'
CLASS_INVALID_ROLE_SPECIFICATION: str  # '0P'
CLASS_DIAGNOSTICS_EXCEPTION: str # '0Z'
CLASS_CASE_NOT_FOUND: str        # '20'
CLASS_CARDINALITY_VIOLATION: str # '21'
CLASS_DATA_EXCEPTION: str        # '22'
CLASS_INTEGRITY_CONSTRAINT_VIOLATION: str  # '23'
CLASS_INVALID_CURSOR_STATE: str  # '24'
CLASS_INVALID_TRANSACTION_STATE: str  # '25'
CLASS_INVALID_SQL_STATEMENT_NAME: str  # '26'
CLASS_TRIGGERED_DATA_CHANGE_VIOLATION: str  # '27'
CLASS_INVALID_AUTHORIZATION_SPECIFICATION: str  # '28'
CLASS_DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST: str  # '2B'
CLASS_INVALID_TRANSACTION_TERMINATION: str  # '2D'
CLASS_SQL_ROUTINE_EXCEPTION: str # '2F'
CLASS_INVALID_CURSOR_NAME: str   # '34'
CLASS_EXTERNAL_ROUTINE_EXCEPTION: str  # '38'
CLASS_EXTERNAL_ROUTINE_INVOCATION_EXCEPTION: str  # '39'
CLASS_SAVEPOINT_EXCEPTION: str   # '3B'
CLASS_INVALID_CATALOG_NAME: str  # '3D'
CLASS_INVALID_SCHEMA_NAME: str   # '3F'
CLASS_TRANSACTION_ROLLBACK: str  # '40'
CLASS_SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: str  # '42'
CLASS_WITH_CHECK_OPTION_VIOLATION: str  # '44'
CLASS_INSUFFICIENT_RESOURCES: str  # '53'
CLASS_PROGRAM_LIMIT_EXCEEDED: str  # '54'
CLASS_OBJECT_NOT_IN_PREREQUISITE_STATE: str  # '55'
CLASS_OPERATOR_INTERVENTION: str  # '57'
CLASS_SYSTEM_ERROR: str          # '58'
CLASS_SNAPSHOT_FAILURE: str      # '72'
CLASS_CONFIGURATION_FILE_ERROR: str  # 'F0'
CLASS_FOREIGN_DATA_WRAPPER_ERROR: str  # 'HV'
CLASS_PL_PGSQL_ERROR: str        # 'P0'
CLASS_INTERNAL_ERROR: str        # 'XX'

# Specific error constants (5-character codes)
SUCCESSFUL_COMPLETION: str        # '00000'
WARNING: str                      # '01000'
DYNAMIC_RESULT_SETS_RETURNED: str # '0100C'
IMPLICIT_ZERO_BIT_PADDING: str    # '01008'
NULL_VALUE_ELIMINATED_IN_SET_FUNCTION: str  # '01003'
PRIVILEGE_NOT_GRANTED: str        # '01007'
PRIVILEGE_NOT_REVOKED: str        # '01006'
STRING_DATA_RIGHT_TRUNCATION: str # '01004'
DEPRECATED_FEATURE: str           # '01P01'
NO_DATA: str                      # '02000'
NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED: str  # '02001'
SQL_STATEMENT_NOT_YET_COMPLETE: str  # '03000'
CONNECTION_EXCEPTION: str         # '08000'
CONNECTION_DOES_NOT_EXIST: str    # '08003'
CONNECTION_FAILURE: str           # '08006'
SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION: str  # '08001'
SQLSERVER_REJECTED_ESTABLISHMENT_OF_SQLCONNECTION: str  # '08004'
TRANSACTION_RESOLUTION_UNKNOWN: str  # '08007'
PROTOCOL_VIOLATION: str           # '08P01'
TRIGGERED_ACTION_EXCEPTION: str   # '09000'
FEATURE_NOT_SUPPORTED: str        # '0A000'
INVALID_TRANSACTION_INITIATION: str  # '0B000'
LOCATOR_EXCEPTION: str            # '0F000'
INVALID_LOCATOR_SPECIFICATION: str  # '0F001'
INVALID_GRANTOR: str              # '0L000'
INVALID_GRANT_OPERATION: str      # '0LP01'
INVALID_ROLE_SPECIFICATION: str   # '0P000'
DIAGNOSTICS_EXCEPTION: str        # '0Z000'
STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER: str  # '0Z002'
CASE_NOT_FOUND: str               # '20000'
CARDINALITY_VIOLATION: str        # '21000'
DATA_EXCEPTION: str               # '22000'
ARRAY_SUBSCRIPT_ERROR: str        # '2202E'
CHARACTER_NOT_IN_REPERTOIRE: str  # '22021'
DATETIME_FIELD_OVERFLOW: str      # '22008'
DIVISION_BY_ZERO: str             # '22012'
ERROR_IN_ASSIGNMENT: str          # '22005'
ESCAPE_CHARACTER_CONFLICT: str    # '2200B'
INDICATOR_OVERFLOW: str           # '22022'
INTERVAL_FIELD_OVERFLOW: str      # '22015'
INVALID_ARGUMENT_FOR_LOGARITHM: str  # '2201E'
INVALID_ARGUMENT_FOR_NTILE_FUNCTION: str  # '22014'
INVALID_ARGUMENT_FOR_NTH_VALUE_FUNCTION: str  # '22016'
INVALID_ARGUMENT_FOR_POWER_FUNCTION: str  # '2201F'
INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION: str  # '2201G'
INVALID_CHARACTER_VALUE_FOR_CAST: str  # '22018'
INVALID_DATETIME_FORMAT: str      # '22007'
INVALID_ESCAPE_CHARACTER: str     # '22019'
INVALID_ESCAPE_OCTET: str         # '2200D'
INVALID_ESCAPE_SEQUENCE: str      # '22025'
NONSTANDARD_USE_OF_ESCAPE_CHARACTER: str  # '22P06'
INVALID_INDICATOR_PARAMETER_VALUE: str  # '22010'
INVALID_PARAMETER_VALUE: str      # '22023'
INVALID_REGULAR_EXPRESSION: str   # '2201B'
INVALID_ROW_COUNT_IN_LIMIT_CLAUSE: str  # '2201W'
INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE: str  # '2201X'
INVALID_TABLESAMPLE_ARGUMENT: str # '2202H'
INVALID_TABLESAMPLE_REPEAT: str   # '2202G'
INVALID_TIME_ZONE_DISPLACEMENT_VALUE: str  # '22009'
INVALID_USE_OF_ESCAPE_CHARACTER: str  # '2200C'
MOST_SPECIFIC_TYPE_MISMATCH: str  # '2200G'
NULL_VALUE_NOT_ALLOWED: str       # '22004'
NULL_VALUE_NO_INDICATOR_PARAMETER: str  # '22002'
NUMERIC_VALUE_OUT_OF_RANGE: str   # '22003'
STRING_DATA_LENGTH_MISMATCH: str  # '22026'
STRING_DATA_RIGHT_TRUNCATION: str # '22001'
SUBSTRING_ERROR: str              # '22011'
TRIM_ERROR: str                   # '22027'
UNTERMINATED_C_STRING: str        # '22024'
ZERO_LENGTH_CHARACTER_STRING: str # '2200F'
FLOATING_POINT_EXCEPTION: str     # '22P01'
INVALID_TEXT_REPRESENTATION: str  # '22P02'
INVALID_BINARY_REPRESENTATION: str  # '22P03'
BAD_COPY_FILE_FORMAT: str         # '22P04'
UNTRANSLATABLE_CHARACTER: str     # '22P05'
NOT_AN_XML_DOCUMENT: str          # '2200L'
INVALID_XML_DOCUMENT: str         # '2200M'
INVALID_XML_CONTENT: str          # '2200N'
INVALID_XML_COMMENT: str          # '2200S'
INVALID_XML_PROCESSING_INSTRUCTION: str  # '2200T'
INTEGRITY_CONSTRAINT_VIOLATION: str  # '23000'
RESTRICT_VIOLATION: str           # '23001'
NOT_NULL_VIOLATION: str           # '23502'
FOREIGN_KEY_VIOLATION: str        # '23503'
UNIQUE_VIOLATION: str             # '23505'
CHECK_VIOLATION: str              # '23514'
EXCLUSION_VIOLATION: str          # '23P01'
INVALID_CURSOR_STATE: str         # '24000'
INVALID_TRANSACTION_STATE: str    # '25000'
ACTIVE_SQL_TRANSACTION: str       # '25001'
BRANCH_TRANSACTION_ALREADY_ACTIVE: str  # '25002'
HELD_CURSOR_REQUIRES_SAME_ISOLATION_LEVEL: str  # '25008'
INAPPROPRIATE_ACCESS_MODE_FOR_BRANCH_TRANSACTION: str  # '25003'
INAPPROPRIATE_ISOLATION_LEVEL_FOR_BRANCH_TRANSACTION: str  # '25004'
NO_ACTIVE_SQL_TRANSACTION_FOR_BRANCH_TRANSACTION: str  # '25005'
READ_ONLY_SQL_TRANSACTION: str    # '25006'
SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED: str  # '25007'
NO_ACTIVE_SQL_TRANSACTION: str    # '25P01'
IN_FAILED_SQL_TRANSACTION: str    # '25P02'
INVALID_SQL_STATEMENT_NAME: str   # '26000'
TRIGGERED_DATA_CHANGE_VIOLATION: str  # '27000'
INVALID_AUTHORIZATION_SPECIFICATION: str  # '28000'
INVALID_PASSWORD: str             # '28P01'
DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST: str  # '2B000'
DEPENDENT_OBJECTS_STILL_EXIST: str  # '2BP01'
INVALID_TRANSACTION_TERMINATION: str  # '2D000'
SQL_ROUTINE_EXCEPTION: str        # '2F000'
FUNCTION_EXECUTED_NO_RETURN_STATEMENT: str  # '2F005'
MODIFYING_SQL_DATA_NOT_PERMITTED: str  # '2F002'
PROHIBITED_SQL_STATEMENT_ATTEMPTED: str  # '2F003'
READING_SQL_DATA_NOT_PERMITTED: str  # '2F004'
INVALID_CURSOR_NAME: str          # '34000'
EXTERNAL_ROUTINE_EXCEPTION: str   # '38000'
CONTAINING_SQL_NOT_PERMITTED: str # '38001'
MODIFYING_SQL_DATA_NOT_PERMITTED: str  # '38002'
PROHIBITED_SQL_STATEMENT_ATTEMPTED: str  # '38003'
READING_SQL_DATA_NOT_PERMITTED: str  # '38004'
EXTERNAL_ROUTINE_INVOCATION_EXCEPTION: str  # '39000'
INVALID_SQLSTATE_RETURNED: str    # '39001'
NULL_VALUE_NOT_ALLOWED: str       # '39004'
TRIGGER_PROTOCOL_VIOLATED: str    # '39P01'
SRF_PROTOCOL_VIOLATED: str        # '39P02'
EVENT_TRIGGER_PROTOCOL_VIOLATED: str  # '39P03'
SAVEPOINT_EXCEPTION: str          # '3B000'
INVALID_SAVEPOINT_SPECIFICATION: str  # '3B001'
INVALID_CATALOG_NAME: str         # '3D000'
INVALID_SCHEMA_NAME: str          # '3F000'
TRANSACTION_ROLLBACK: str         # '40000'
TRANSACTION_INTEGRITY_CONSTRAINT_VIOLATION: str  # '40002'
SERIALIZATION_FAILURE: str        # '40001'
STATEMENT_COMPLETION_UNKNOWN: str # '40003'
DEADLOCK_DETECTED: str            # '40P01'
SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION: str  # '42000'
SYNTAX_ERROR: str                 # '42601'
INSUFFICIENT_PRIVILEGE: str       # '42501'
CANNOT_COERCE: str                # '42846'
GROUPING_ERROR: str               # '42803'
WINDOWING_ERROR: str              # '42P20'
INVALID_RECURSION: str            # '42P19'
INVALID_FOREIGN_KEY: str          # '42830'
INVALID_NAME: str                 # '42602'
NAME_TOO_LONG: str               # '42622'
RESERVED_NAME: str               # '42939'
DATATYPE_MISMATCH: str           # '42804'
INDETERMINATE_DATATYPE: str      # '42P18'
COLLATION_MISMATCH: str          # '42P21'
INDETERMINATE_COLLATION: str     # '42P22'
WRONG_OBJECT_TYPE: str           # '42809'
GENERATED_ALWAYS: str            # '428C9'
UNDEFINED_COLUMN: str            # '42703'
UNDEFINED_FUNCTION: str          # '42883'
UNDEFINED_TABLE: str             # '42P01'
UNDEFINED_PARAMETER: str         # '42P02'
UNDEFINED_OBJECT: str            # '42704'
DUPLICATE_COLUMN: str            # '42701'
DUPLICATE_CURSOR: str            # '42P03'
DUPLICATE_DATABASE: str          # '42P04'
DUPLICATE_FUNCTION: str          # '42723'
DUPLICATE_PREPARED_STATEMENT: str # '42P05'
DUPLICATE_SCHEMA: str            # '42P06'
DUPLICATE_TABLE: str             # '42P07'
DUPLICATE_ALIAS: str             # '42712'
DUPLICATE_OBJECT: str            # '42710'
AMBIGUOUS_COLUMN: str            # '42702'
AMBIGUOUS_FUNCTION: str          # '42725'
AMBIGUOUS_PARAMETER: str         # '42P08'
AMBIGUOUS_ALIAS: str             # '42P09'
INVALID_COLUMN_REFERENCE: str    # '42P10'
INVALID_COLUMN_DEFINITION: str   # '42611'
INVALID_CURSOR_DEFINITION: str   # '42P11'
INVALID_DATABASE_DEFINITION: str # '42P12'
INVALID_FUNCTION_DEFINITION: str # '42P13'
INVALID_PREPARED_STATEMENT_DEFINITION: str  # '42P14'
INVALID_SCHEMA_DEFINITION: str   # '42P15'
INVALID_TABLE_DEFINITION: str    # '42P16'
INVALID_OBJECT_DEFINITION: str   # '42P17'
WITH_CHECK_OPTION_VIOLATION: str # '44000'
INSUFFICIENT_RESOURCES: str      # '53000'
DISK_FULL: str                   # '53100'
OUT_OF_MEMORY: str              # '53200'
TOO_MANY_CONNECTIONS: str       # '53300'
CONFIGURATION_LIMIT_EXCEEDED: str # '53400'
PROGRAM_LIMIT_EXCEEDED: str      # '54000'
STATEMENT_TOO_COMPLEX: str       # '54001'
TOO_MANY_COLUMNS: str           # '54011'
TOO_MANY_ARGUMENTS: str         # '54023'
OBJECT_NOT_IN_PREREQUISITE_STATE: str  # '55000'
OBJECT_IN_USE: str              # '55006'
CANT_CHANGE_RUNTIME_PARAM: str  # '55P02'
LOCK_NOT_AVAILABLE: str         # '55P03'
OPERATOR_INTERVENTION: str       # '57000'
QUERY_CANCELED: str             # '57014'
ADMIN_SHUTDOWN: str             # '57P01'
CRASH_SHUTDOWN: str             # '57P02'
CANNOT_CONNECT_NOW: str         # '57P03'
DATABASE_DROPPED: str           # '57P04'
SYSTEM_ERROR: str               # '58000'
IO_ERROR: str                   # '58030'
UNDEFINED_FILE: str             # '58P01'
DUPLICATE_FILE: str             # '58P02'
SNAPSHOT_TOO_OLD: str           # '72000'
CONFIG_FILE_ERROR: str          # 'F0000'
LOCK_FILE_EXISTS: str           # 'F0001'
FDW_ERROR: str                  # 'HV000'
FDW_COLUMN_NAME_NOT_FOUND: str  # 'HV005'
FDW_DYNAMIC_PARAMETER_VALUE_NEEDED: str  # 'HV002'
FDW_FUNCTION_SEQUENCE_ERROR: str # 'HV010'
FDW_INCONSISTENT_DESCRIPTOR_INFORMATION: str  # 'HV021'
FDW_INVALID_ATTRIBUTE_VALUE: str # 'HV024'
FDW_INVALID_COLUMN_NAME: str    # 'HV007'
FDW_INVALID_COLUMN_NUMBER: str  # 'HV008'
FDW_INVALID_DATA_TYPE: str      # 'HV004'
FDW_INVALID_DATA_TYPE_DESCRIPTORS: str  # 'HV006'
FDW_INVALID_DESCRIPTOR_FIELD_IDENTIFIER: str  # 'HV091'
FDW_INVALID_HANDLE: str         # 'HV00B'
FDW_INVALID_OPTION_INDEX: str   # 'HV00C'
FDW_INVALID_OPTION_NAME: str    # 'HV00D'
FDW_INVALID_STRING_LENGTH_OR_BUFFER_LENGTH: str  # 'HV090'
FDW_INVALID_STRING_FORMAT: str  # 'HV00A'
FDW_INVALID_USE_OF_NULL_POINTER: str  # 'HV009'
FDW_TOO_MANY_HANDLES: str       # 'HV014'
FDW_OUT_OF_MEMORY: str          # 'HV001'
FDW_NO_SCHEMAS: str             # 'HV00P'
FDW_OPTION_NAME_NOT_FOUND: str  # 'HV00J'
FDW_REPLY_HANDLE: str           # 'HV00K'
FDW_SCHEMA_NOT_FOUND: str       # 'HV00Q'
FDW_TABLE_NOT_FOUND: str        # 'HV00R'
FDW_UNABLE_TO_CREATE_EXECUTION: str  # 'HV00L'
FDW_UNABLE_TO_CREATE_REPLY: str # 'HV00M'
FDW_UNABLE_TO_ESTABLISH_CONNECTION: str  # 'HV00N'
PLPGSQL_ERROR: str              # 'P0000'
RAISE_EXCEPTION: str            # 'P0001'
NO_DATA_FOUND: str              # 'P0002'
TOO_MANY_ROWS: str              # 'P0003'
ASSERT_FAILURE: str             # 'P0004'
INTERNAL_ERROR: str             # 'XX000'
DATA_CORRUPTED: str             # 'XX001'
INDEX_CORRUPTED: str            # 'XX002'

from psycopg2.errorcodes import (
    UNIQUE_VIOLATION, NOT_NULL_VIOLATION, FOREIGN_KEY_VIOLATION,
    UNDEFINED_TABLE, SYNTAX_ERROR, INSUFFICIENT_PRIVILEGE
)
import psycopg2

conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")

try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO users (email) VALUES (%s)", ("duplicate@email.com",))
        conn.commit()
        
except psycopg2.IntegrityError as e:
    if e.pgcode == UNIQUE_VIOLATION:
        print("Email already exists")
    elif e.pgcode == NOT_NULL_VIOLATION:
        print("Required field is missing")
    elif e.pgcode == FOREIGN_KEY_VIOLATION:
        print("Referenced record does not exist")
    else:
        print(f"Other integrity error: {e}")
    conn.rollback()

try:
    with conn.cursor() as cur:
        cur.execute("SELECT * FROM nonexistent_table")
        
except psycopg2.ProgrammingError as e:
    if e.pgcode == UNDEFINED_TABLE:
        print("Table does not exist")
    elif e.pgcode == SYNTAX_ERROR:
        print("SQL syntax error")
    elif e.pgcode == INSUFFICIENT_PRIVILEGE:
        print("Permission denied")
    else:
        print(f"Other programming error: {e}")

conn.close()

class Diagnostics:
    """Exception diagnostics object."""
    
    @property
    def column_name(self):
        """Column name related to error."""
    
    @property
    def constraint_name(self):
        """Constraint name that was violated."""
    
    @property
    def context(self):  
        """Error context information."""
    
    @property
    def datatype_name(self):
        """Data type name related to error."""
    
    @property
    def internal_position(self):
        """Internal error position."""
    
    @property
    def internal_query(self):
        """Internal query that caused error."""
    
    @property
    def message_detail(self):
        """Detailed error message."""
    
    @property
    def message_hint(self):
        """Error message hint."""
    
    @property
    def message_primary(self):
        """Primary error message."""
    
    @property
    def schema_name(self):
        """Schema name related to error."""
    
    @property
    def severity(self):
        """Error severity level."""
    
    @property
    def source_file(self):
        """Source file where error occurred."""
    
    @property
    def source_function(self):
        """Source function where error occurred."""
    
    @property
    def source_line(self):
        """Source line where error occurred."""
    
    @property
    def sqlstate(self):
        """SQLSTATE error code."""
    
    @property
    def statement_position(self):
        """Position in statement where error occurred."""
    
    @property
    def table_name(self):
        """Table name related to error."""

import psycopg2

conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")

try:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO users (email, age) VALUES (%s, %s)", 
                   ("user@example.com", "invalid_age"))
        conn.commit()
        
except psycopg2.DataError as e:
    print(f"Error: {e}")
    print(f"SQLSTATE: {e.pgcode}")
    
    # Access diagnostics if available
    if hasattr(e, 'diag'):
        diag = e.diag
        print(f"Severity: {diag.severity}")
        print(f"Primary message: {diag.message_primary}")
        print(f"Detail: {diag.message_detail}")
        print(f"Hint: {diag.message_hint}")
        print(f"Position: {diag.statement_position}")
        print(f"Context: {diag.context}")
        
        if diag.table_name:
            print(f"Table: {diag.table_name}")
        if diag.column_name:
            print(f"Column: {diag.column_name}")
        if diag.constraint_name:
            print(f"Constraint: {diag.constraint_name}")
    
    conn.rollback()

conn.close()

import psycopg2
from psycopg2.errorcodes import SERIALIZATION_FAILURE, DEADLOCK_DETECTED
import time
import random

def retry_on_serialization_failure(func, max_retries=3, base_delay=0.1):
    """Retry function on serialization failures with exponential backoff."""
    for attempt in range(max_retries):
        try:
            return func()
        except psycopg2.OperationalError as e:
            if e.pgcode in (SERIALIZATION_FAILURE, DEADLOCK_DETECTED):
                if attempt < max_retries - 1:
                    delay = base_delay * (2 ** attempt) + random.uniform(0, 0.1)
                    print(f"Serialization failure, retrying in {delay:.2f}s...")
                    time.sleep(delay)
                    continue
            raise

def safe_database_operation():
    """Example database operation with comprehensive error handling."""
    conn = psycopg2.connect("host=localhost dbname=mydb user=myuser password=mypass")
    
    def transfer_money():
        with conn.cursor() as cur:
            # Set serializable isolation level
            conn.set_isolation_level(3)  # ISOLATION_LEVEL_SERIALIZABLE
            
            cur.execute("SELECT balance FROM accounts WHERE id = %s", (1,))
            from_balance = cur.fetchone()[0]
            
            cur.execute("SELECT balance FROM accounts WHERE id = %s", (2,))
            to_balance = cur.fetchone()[0]
            
            if from_balance < 100:
                raise ValueError("Insufficient funds")
            
            cur.execute("UPDATE accounts SET balance = balance - 100 WHERE id = %s", (1,))
            cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = %s", (2,))
            
            conn.commit()
            print("Transfer completed successfully")
    
    try:
        retry_on_serialization_failure(transfer_money)
        
    except psycopg2.IntegrityError as e:
        print(f"Integrity constraint violation: {e}")
        conn.rollback()
        
    except psycopg2.ProgrammingError as e:
        print(f"Programming error: {e}")
        conn.rollback()
        
    except psycopg2.OperationalError as e:
        print(f"Operational error: {e}")
        if conn:
            conn.rollback()
            
    except ValueError as e:
        print(f"Business logic error: {e}")
        conn.rollback()
        
    except Exception as e:
        print(f"Unexpected error: {e}")
        if conn:
            conn.rollback()
        raise
        
    finally:
        if conn:
            conn.close()

# Context manager for automatic error handling
class DatabaseTransaction:
    """Context manager for database transactions with automatic error handling."""
    
    def __init__(self, connection_params):
        self.connection_params = connection_params
        self.conn = None
    
    def __enter__(self):
        self.conn = psycopg2.connect(**self.connection_params)
        return self.conn
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type is None:
            # No exception, commit transaction
            self.conn.commit()
        else:
            # Exception occurred, rollback transaction
            self.conn.rollback()
            
            # Log different types of errors
            if isinstance(exc_val, psycopg2.IntegrityError):
                print(f"Data integrity error: {exc_val}")
            elif isinstance(exc_val, psycopg2.ProgrammingError):
                print(f"SQL programming error: {exc_val}")
            elif isinstance(exc_val, psycopg2.OperationalError):
                print(f"Database operational error: {exc_val}")
            else:
                print(f"Database error: {exc_val}")
        
        self.conn.close()
        return False  # Don't suppress exceptions

# Usage of context manager
try:
    with DatabaseTransaction({'host': 'localhost', 'database': 'mydb', 'user': 'myuser', 'password': 'mypass'}) as conn:
        with conn.cursor() as cur:
            cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
                       ("Alice", "alice@example.com"))
            cur.execute("INSERT INTO profiles (user_id, bio) VALUES (currval('users_id_seq'), %s)",
                       ("Software engineer",))
        # Transaction automatically committed if no exceptions
        
except Exception as e:
    print(f"Transaction failed: {e}")

class Error(Exception):
    """Base psycopg2 exception."""
    
    pgcode: str | None  # PostgreSQL SQLSTATE code
    pgerror: str | None  # PostgreSQL error message
    diag: Diagnostics | None  # Detailed diagnostics

class Warning(Exception):
    """Database warning."""

class InterfaceError(Error):
    """Interface-related errors."""

class DatabaseError(Error):
    """Database engine errors."""

class DataError(DatabaseError):
    """Data-related errors."""

class OperationalError(DatabaseError):
    """Operation-related errors."""

class IntegrityError(DatabaseError):  
    """Database integrity violations."""

class InternalError(DatabaseError):
    """Database internal errors."""

class ProgrammingError(DatabaseError):
    """SQL programming errors."""

class NotSupportedError(DatabaseError):
    """Unsupported operations."""

class QueryCanceledError(OperationalError):
    """Query cancellation."""

class TransactionRollbackError(OperationalError):
    """Transaction rollback conditions."""

class Diagnostics:
    """Detailed error diagnostics."""
    
    column_name: str | None
    constraint_name: str | None  
    context: str | None
    datatype_name: str | None
    internal_position: str | None
    internal_query: str | None
    message_detail: str | None
    message_hint: str | None
    message_primary: str | None
    schema_name: str | None
    severity: str | None
    source_file: str | None
    source_function: str | None
    source_line: str | None
    sqlstate: str | None
    statement_position: str | None
    table_name: str | None

def lookup(code: str) -> type:
    """Look up exception class by SQLSTATE code."""

# Error code constants (over 200 specific codes)
UNIQUE_VIOLATION: str          # '23505'
NOT_NULL_VIOLATION: str        # '23502'
FOREIGN_KEY_VIOLATION: str     # '23503'
CHECK_VIOLATION: str           # '23514'
UNDEFINED_TABLE: str           # '42P01'
SYNTAX_ERROR: str              # '42601'
INSUFFICIENT_PRIVILEGE: str    # '42501'
DEADLOCK_DETECTED: str         # '40P01'
SERIALIZATION_FAILURE: str     # '40001'
# ... and many more