Error Handling and Diagnostics

class Error(Exception):
    """Base class for all psycopg2 exceptions."""
    
    @property
    def pgcode(self):
        """PostgreSQL error code (SQLSTATE)."""
    
    @property  
    def pgerror(self):
        """PostgreSQL error message."""
    
    @property
    def diag(self):
        """Diagnostics object with detailed error info."""

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

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

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

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

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

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

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

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

class NotSupportedError(DatabaseError):
    """Exception for unsupported operations."""

class QueryCanceledError(OperationalError):
    """Exception for canceled queries."""

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

class InFailedSqlTransaction(ProgrammingError):
    """Exception for failed SQL transactions."""

class StopReplication(Exception):
    """
    Exception to stop replication stream.
    
    Used in replication message handlers to cleanly stop streaming
    replication when certain conditions are met.
    """

from psycopg2.extras import StopReplication

def message_handler(msg):
    if msg.data_start > target_lsn:
        # Stop replication when we reach target LSN
        raise StopReplication()
    
    # Process message
    process_message(msg.payload)

# In replication loop
try:
    for msg in replication_cursor:
        message_handler(msg)
except StopReplication:
    print("Replication stopped by handler")

def lookup(code):
    """
    Look up exception class by PostgreSQL error code.
    
    Parameters:
    - code (str): PostgreSQL error code (SQLSTATE)
    
    Returns:
    type: Exception class for the error code
    
    Raises:
    KeyError: If error code is not found
    """

def lookup(code, _cache={}):
    """
    Look up symbolic name by PostgreSQL error code.
    
    Parameters:
    - code (str): PostgreSQL error code (SQLSTATE)
    
    Returns:
    str: Symbolic name for the error code
    
    Raises:
    KeyError: If error code is not found
    """

import psycopg2
from psycopg2 import errors, errorcodes

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

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

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

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

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

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

try:
    cur.execute("LOCK TABLE users")
    # Long operation
except errors.QueryCanceledError:
    print("Query was canceled")
except errors.DeadlockDetected:
    print("Deadlock detected - retry transaction")

# Transaction errors
try:
    with conn:
        cur.execute("INSERT INTO users VALUES (%s)", (invalid_data,))
except errors.SerializationFailure:
    print("Serialization failure - retry transaction")
except errors.DeadlockDetected:
    print("Deadlock - retry transaction")

try:
    cur.execute("INSERT INTO orders (customer_id, product_id) VALUES (%s, %s)", 
               (999999, 123))  # Non-existent customer
except psycopg2.IntegrityError as e:
    diag = e.diag
    
    print(f"Severity: {diag.severity}")
    print(f"SQL State: {diag.sqlstate}")
    print(f"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}")
    print(f"Schema: {diag.schema_name}")
    print(f"Table: {diag.table_name}")
    print(f"Column: {diag.column_name}")
    print(f"Constraint: {diag.constraint_name}")
    print(f"Source file: {diag.source_file}")
    print(f"Source line: {diag.source_line}")
    print(f"Source function: {diag.source_function}")

# Error context for nested operations
try:
    cur.execute("SELECT some_function(%s)", (invalid_param,))
except psycopg2.Error as e:
    diag = e.diag
    print(f"Internal query: {diag.internal_query}")
    print(f"Internal position: {diag.internal_position}")
    print(f"Context: {diag.context}")

def safe_execute(cursor, query, params=None, retries=3):
    """Execute query with automatic retry for transient errors."""
    for attempt in range(retries):
        try:
            cursor.execute(query, params)
            return cursor.fetchall()
        except (errors.DeadlockDetected, errors.SerializationFailure) as e:
            if attempt < retries - 1:
                print(f"Retrying due to {type(e).__name__} (attempt {attempt + 1})")
                cursor.connection.rollback()
                time.sleep(0.1 * (2 ** attempt))  # Exponential backoff
                continue
            raise
        except psycopg2.Error:
            # Don't retry for other errors
            raise

def handle_constraint_violation(e):
    """Handle different types of constraint violations."""
    if e.pgcode == errorcodes.UNIQUE_VIOLATION:
        constraint = e.diag.constraint_name
        if constraint == 'users_email_key':
            return "Email already registered"
        elif constraint == 'users_username_key':  
            return "Username already taken"
        else:
            return f"Duplicate value for {constraint}"
    
    elif e.pgcode == errorcodes.FOREIGN_KEY_VIOLATION:
        table = e.diag.table_name
        constraint = e.diag.constraint_name
        return f"Referenced record not found: {constraint} in {table}"
    
    elif e.pgcode == errorcodes.CHECK_VIOLATION:
        constraint = e.diag.constraint_name
        return f"Data validation failed: {constraint}"
    
    elif e.pgcode == errorcodes.NOT_NULL_VIOLATION:
        column = e.diag.column_name
        return f"Required field missing: {column}"
    
    else:
        return f"Data integrity error: {e.pgerror}"

# Usage
try:
    cur.execute("INSERT INTO users (username, email) VALUES (%s, %s)",
               ('existing_user', 'new@email.com'))
except psycopg2.IntegrityError as e:
    error_message = handle_constraint_violation(e)
    print(f"Cannot create user: {error_message}")

import logging
from psycopg2.extras import LoggingConnection

# Setup comprehensive error logging
logging.basicConfig(
    level=logging.ERROR,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)

logger = logging.getLogger('database')

def log_database_error(e, query=None, params=None):
    """Log database errors with context."""
    error_info = {
        'error_type': type(e).__name__,
        'pgcode': getattr(e, 'pgcode', None),
        'pgerror': getattr(e, 'pgerror', None),
        'query': query,
        'params': params
    }
    
    if hasattr(e, 'diag') and e.diag:
        error_info.update({
            'severity': e.diag.severity,
            'sqlstate': e.diag.sqlstate,
            'message_primary': e.diag.message_primary,
            'message_detail': e.diag.message_detail,
            'table_name': e.diag.table_name,
            'constraint_name': e.diag.constraint_name
        })
    
    logger.error("Database error occurred", extra=error_info)

# Usage with error logging
try:
    query = "INSERT INTO users (email) VALUES (%s)"
    params = ('invalid-email',)
    cur.execute(query, params)
except psycopg2.Error as e:
    log_database_error(e, query, params)
    raise

# Major error classes (first 2 characters of SQLSTATE)
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'

# Common specific error codes
UNIQUE_VIOLATION: str = '23505'
FOREIGN_KEY_VIOLATION: str = '23503'
CHECK_VIOLATION: str = '23514'
NOT_NULL_VIOLATION: str = '23502'
RESTRICT_VIOLATION: str = '23001'
EXCLUSION_VIOLATION: str = '23P01'
UNDEFINED_TABLE: str = '42P01'
UNDEFINED_COLUMN: str = '42703'
UNDEFINED_FUNCTION: str = '42883'
SYNTAX_ERROR: str = '42601'
INSUFFICIENT_PRIVILEGE: str = '42501'
DUPLICATE_TABLE: str = '42P07'
DUPLICATE_COLUMN: str = '42701'
DEADLOCK_DETECTED: str = '40P01'
SERIALIZATION_FAILURE: str = '40001'
QUERY_CANCELED: str = '57014'

# Exception class to SQLSTATE mapping
EXCEPTION_MAP = {
    '23505': UniqueViolation,
    '23503': ForeignKeyViolation,
    '23514': CheckViolation,
    '23502': NotNullViolation,
    '42P01': UndefinedTable,
    '42703': UndefinedColumn,
    '42883': UndefinedFunction,
    '42601': SyntaxError,
    '40P01': DeadlockDetected,
    '40001': SerializationFailure,
    '57014': QueryCanceledError,
    # ... hundreds more mappings
}