Skip to content

LLM-Driven Extraction of Unstructured Data — Built for API Deployments & ETL Pipeline Workflows

10.0 KB
Raw Blame
"""Direct Postgres connection for the PG-queue client.

The PG queue is the first worker component to open a *direct* Postgres
connection — workers otherwise reach state via the Internal API, with
Redis the only other direct store. It reuses the **backend's** ``DB_*``
env so the target follows the deployment: in cloud those vars point at
PgBouncer (connection pooling); in OSS / on-prem they point straight at
Postgres.

``search_path`` is set to ``DB_SCHEMA`` so the bespoke
``pg_queue_message`` table resolves in the same schema the backend
manages.
"""

from __future__ import annotations

import logging
import os
import time
from collections.abc import Callable
from typing import TYPE_CHECKING

import psycopg2
import psycopg2.errors

if TYPE_CHECKING:
    from psycopg2.extensions import connection as PgConnection

logger = logging.getLogger(__name__)

# psycopg2 errors that mean the connection itself is dead (dropped socket /
# PgBouncer recycle / server termination), as opposed to a logical/data error on
# a live connection. Single source of truth for every PG-queue site that decides
# "was this a connection death?" — the dispatch ``send`` reused-guard
# (``pg_queue.client``), the ``store_result`` retry
# (``pg_queue.result_backend``) and the barrier enqueue/decrement
# (``pg_barrier``). Hoisted here (the module all of them already
# import) so the three call sites can't drift apart.
CONN_DEAD_ERRORS: tuple[type[Exception], ...] = (
    psycopg2.OperationalError,
    psycopg2.InterfaceError,
)

# The counterpart: rejections caused by the PAYLOAD rather than the connection.
# Retrying these can never succeed, so a writer degrades instead of escaping (a
# write that never lands is a reply that never comes — UN-4126). Hoisted beside
# CONN_DEAD_ERRORS, and for the same reason: the result backend and the barrier
# both classify on it and must not drift.
#
# ``ProgramLimitExceeded`` (SQLSTATE 54 — "string too long" / "index row size
# exceeds maximum") is the trap this pairing exists to document: it subclasses
# OperationalError, so it is absent from ``DataError`` AND present in
# CONN_DEAD_ERRORS above. Classifying on membership alone therefore misreads an
# oversized payload as a dead connection and pointlessly re-sends it, so the
# order is not left to each call site: :func:`is_connection_dead` below is the
# one place that decides, and every retry site asks it.
#
# Deliberately not ``Exception``: a genuine outage recorded as "payload
# unstorable" is a false diagnosis that sends the next investigator after
# content while the database is down.
PAYLOAD_REJECTED_ERRORS: tuple[type[Exception], ...] = (
    psycopg2.DataError,
    psycopg2.errors.ProgramLimitExceeded,
)


def is_connection_dead(exc: BaseException) -> bool:
    """Whether *exc* means the CONNECTION is gone, rather than the payload bad.

    ``isinstance(exc, CONN_DEAD_ERRORS)`` on its own is not that question, and
    the gap is not hypothetical: ``ProgramLimitExceeded`` subclasses
    ``OperationalError``, so an oversized payload answers "dead connection",
    gets its handle discarded, reconnects and re-sends a write that can never
    succeed — a second pass over a payload that is by definition very large,
    before the caller's payload-rejection handler finally sees it.

    Payload rejections are therefore tested FIRST. A rejected payload leaves the
    connection perfectly usable; only the write was refused.
    """
    if isinstance(exc, PAYLOAD_REJECTED_ERRORS):
        return False
    return isinstance(exc, CONN_DEAD_ERRORS)


def is_dead_after_autocommit_error(exc: BaseException) -> bool:
    """Whether a statement error on an owned AUTOCOMMIT connection means it is dead.

    A connection the server terminated already reads as dead without this: the
    error is an ``OperationalError`` and psycopg2 marks the connection closed.
    What autocommit loses is narrower: ``rollback()`` sends nothing, so it no
    longer exposes a dead connection that psycopg2 still reports as open. So a
    bare ``DatabaseError`` (one psycopg2 mapped to no SQLSTATE subclass) is
    treated as dead too; if it was only an unmapped statement error, the cost
    is one needless reconnect.

    Deliberately separate from :func:`is_connection_dead`: transactional
    connections (``pg_barrier``) get this detection from a real ``rollback()``,
    and the retry sites must not start treating a bare ``DatabaseError`` as
    retryable.
    """
    return is_connection_dead(exc) or type(exc) is psycopg2.DatabaseError


# Bounded retry for *transient* connect failures (DB restart, PgBouncer pool
# wait, brief network partition, "too many clients" spikes) — the common cloud
# blips. Opening a connection is side-effect-free, so retrying it is safe and
# purely additive. Distinct from client.send()'s single reconnect-retry, which
# fires only for the idle-reap case (the conn was reaped before the INSERT ran,
# so it provably never executed); an *ambiguous* commit-time failure is never
# retried there — that stays fail-and-surface to avoid a double-enqueue.
_DEFAULT_CONNECT_RETRIES = 3  # total attempts (1 = no retry)
_MAX_CONNECT_RETRIES = 10  # sane upper bound — a fat-fingered value can't wedge startup
_DEFAULT_CONNECT_BACKOFF = 0.5  # base seconds between attempts; doubles each retry
_CONNECT_BACKOFF_CAP = (
    5.0  # fixed ceiling on a single inter-attempt sleep (not env-tunable)
)


def _connect_env[T](suffix: str, default: T, cast: Callable[[str], T]) -> T:
    """Read ``WORKER_PG_QUEUE_CONNECT_{suffix}``; fall back to ``default``.

    Empty/unset → default; an unparseable value warns and uses the default
    (a bad knob must not wedge the only direct-DB worker path).
    """
    raw = os.getenv(f"WORKER_PG_QUEUE_CONNECT_{suffix}")
    if raw is None or raw == "":
        return default
    try:
        return cast(raw)
    except (TypeError, ValueError):
        logger.warning(
            "PG-queue: invalid WORKER_PG_QUEUE_CONNECT_%s=%r; using default %r",
            suffix,
            raw,
            default,
        )
        return default


def _connect_retry_policy() -> tuple[int, float]:
    """Resolve ``(attempts, base_backoff)`` from env, clamping out-of-range values.

    Out-of-range knobs are clamped *and warned* (not silently coerced), mirroring
    the unparseable-value path: attempts to ``[1, _MAX_CONNECT_RETRIES]``, backoff
    to ``>= 0``. The per-sleep ``_CONNECT_BACKOFF_CAP`` is a separate fixed ceiling.
    """
    raw_retries = _connect_env("RETRIES", _DEFAULT_CONNECT_RETRIES, int)
    attempts = min(_MAX_CONNECT_RETRIES, max(1, raw_retries))
    if attempts != raw_retries:
        logger.warning(
            "PG-queue: WORKER_PG_QUEUE_CONNECT_RETRIES=%d out of range [1, %d]; "
            "clamped to %d",
            raw_retries,
            _MAX_CONNECT_RETRIES,
            attempts,
        )
    raw_backoff = _connect_env("BACKOFF", _DEFAULT_CONNECT_BACKOFF, float)
    backoff = max(0.0, raw_backoff)
    if backoff != raw_backoff:
        logger.warning(
            "PG-queue: WORKER_PG_QUEUE_CONNECT_BACKOFF=%s is negative; clamped to "
            "0.0 (retry without sleep)",
            raw_backoff,
        )
    return attempts, backoff


def create_pg_connection(env_prefix: str = "DB_") -> PgConnection:
    """Open a direct Postgres connection from ``{env_prefix}*`` env.

    The default ``DB_`` prefix reads the same variable *names* the backend
    uses (``DB_HOST`` / ``DB_PORT`` / ``DB_NAME`` / ``DB_USER`` /
    ``DB_PASSWORD`` / ``DB_SCHEMA``), so the queue connects to the
    backend's database and schema. Tests pass ``env_prefix="TEST_DB_"`` to
    target a dev DB. The fallbacks below are dev-compose values (the host
    is the compose service name); real deployments always set the env
    explicitly, so the fallback host intentionally differs from the
    backend's own ``base.py`` default.

    A transient ``OperationalError`` is retried with exponential backoff
    (``WORKER_PG_QUEUE_CONNECT_RETRIES`` total attempts, base
    ``WORKER_PG_QUEUE_CONNECT_BACKOFF`` seconds, each sleep capped at
    ``_CONNECT_BACKOFF_CAP``). Non-operational errors (e.g. a bad ``options``
    string) are not transient and raise immediately. **Permanent** misconfigs
    (auth failure, unknown database, bad host) also surface as ``OperationalError``,
    so they are retried-then-raised — the final ``logger.error`` makes them obvious.
    """
    host = os.getenv(f"{env_prefix}HOST", "unstract-db")
    port = os.getenv(f"{env_prefix}PORT", "5432")
    dbname = os.getenv(f"{env_prefix}NAME", "unstract_db")
    user = os.getenv(f"{env_prefix}USER", "unstract_dev")
    schema = os.getenv(f"{env_prefix}SCHEMA", "unstract")

    attempts, backoff = _connect_retry_policy()

    for attempt in range(1, attempts + 1):
        try:
            return psycopg2.connect(
                host=host,
                port=port,
                dbname=dbname,
                user=user,
                password=os.getenv(f"{env_prefix}PASSWORD", "unstract_pass"),
                options=f"-c search_path={schema}",
            )
        except psycopg2.OperationalError:
            if attempt >= attempts:
                # Every attempt exhausted — keep the failure self-identifying so a
                # misconfigured DB_* var is obvious. Secrets (password) omitted.
                logger.error(
                    "PG-queue: failed to connect to Postgres after %d attempt(s) "
                    "(host=%s port=%s dbname=%s schema=%s, env_prefix=%r)",
                    attempts,
                    host,
                    port,
                    dbname,
                    schema,
                    env_prefix,
                )
                raise
            sleep_for = min(backoff * (2 ** (attempt - 1)), _CONNECT_BACKOFF_CAP)
            logger.warning(
                "PG-queue: connect attempt %d/%d failed "
                "(host=%s port=%s dbname=%s schema=%s); retrying in %.2fs",
                attempt,
                attempts,
                host,
                port,
                dbname,
                schema,
                sleep_for,
            )
            time.sleep(sleep_for)