Skip to content

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

36.1 KB
Raw Blame
"""Postgres request-reply result store for the executor RPC.

Replaces Celery's ``AsyncResult`` / result backend for the *blocking* executor
dispatch when it rides the PG transport. The PG executor consumer
(``worker-pg-executor``) writes a finished task's outcome here, keyed by the
caller-chosen reply key; the blocking caller waits on :meth:`wait_for_result`
until the row appears or its timeout elapses. That wait is event-driven when the
Redis signal is enabled (below) and a capped backoff poll otherwise.

A row appears ONLY when the task finishes — ``status="completed"`` carrying the
``ExecutionResult.to_dict()`` payload, or ``status="failed"`` carrying the error
text if the task raised. Absence of a row means "not done yet"; there is
deliberately no ``pending`` state to maintain.

``failed`` has a **third** meaning, and recovery logic must account for it: a
payload could not be stored. Two constants distinguish the cases, and they point
in OPPOSITE directions for a retry:

- :data:`PAYLOAD_UNSTORABLE_ERROR` — the task *completed*; only its result was
  unstorable. Retrying this reply key re-runs an executor task that already
  finished, a second full LLM spend — the very thing the consumer's ack
  discipline avoids. **Do not retry.**
- :data:`ERROR_TEXT_UNSTORABLE` — the task *raised*, and its own error message was
  unstorable. The work never happened. **Retrying is correct.**

Seeing ``failed`` is therefore not enough; match the error text before deciding.
(Mirrored on ``PgTaskResult`` in ``backend/pg_queue/models.py``.)

Once the blocking caller consumes the reply, :meth:`PgResultBackend.forget` nulls
the payload in place — a third legal shape: ``completed``/``failed`` with
``result`` and ``error`` cleared (a tombstone the reaper deletes at
``expires_at``). Readers must not assume a finished row still carries a payload.

Two deliberate properties:

- **Idempotent writes** (``INSERT … ON CONFLICT DO NOTHING``): the PG queue is
  at-least-once, so a redelivered executor message must not clobber an already
  recorded result. First write wins.
- **Event-driven waiting with a poll fallback** (``PG_RESULT_SIGNAL_BACKEND``):
  polling ``pg_task_result`` once per in-flight task scales DB load with
  *concurrency*, not throughput (every waiter SELECTs on a backoff) — which
  saturated the DB at high concurrency. When ``PG_RESULT_SIGNAL_BACKEND=redis``,
  :meth:`store_result` RPUSHes a tiny ready-token (the reply key ONLY — never the
  payload/PII) to a per-key Redis list and :meth:`wait_for_result` BLPOPs it, then
  does an authoritative SELECT on wake — collapsing the per-task DB reads to a small
  constant (a race-check SELECT before the BLPOP plus one after the wake, versus the
  poll's many) so DB load scales with throughput. The DB stays
  the source of truth: a slow fallback SELECT still runs, so a lost/absent signal
  (Redis restart, missed publish) costs only latency, never correctness. PG
  ``LISTEN``/``NOTIFY`` was rejected because the *receiver* side cannot survive
  transaction-pooled PgBouncer (a session-scoped registration on a connection the
  pool reassigns each txn — the same constraint that makes the orchestrator hold a
  TTL lease rather than ``pg_advisory_lock``). With ``PG_RESULT_SIGNAL_BACKEND=poll``
  (the default) the behaviour is exactly the historical backoff poll.

Connection discipline mirrors :class:`~queue_backend.pg_queue.client.PgQueueClient`:
an injected connection is the caller's (tests); otherwise one is created lazily
from the backend ``DB_*`` env and owned here (rolled back on error, discarded +
reconnected when it goes bad). The executor consumer caches one backend for its
lifetime, so the write connection sits idle between tasks and PgBouncer can reap
it; :meth:`store_result` therefore retries once on a dead connection so the
result is still written (and the blocking caller unblocked) rather than dropped
— see :meth:`_store_with_reconnect`.
"""

from __future__ import annotations

import contextlib
import logging
import os
import time
from collections.abc import Callable, Iterator
from typing import TYPE_CHECKING, Any, Final, Self

import redis

from unstract.core.cache.redis_client import create_redis_client
from unstract.core.data_models import PgTaskStatus
from unstract.core.jsonb import dumps_for_jsonb, sanitize_for_jsonb
from unstract.core.polling import poll_for_row

from .connection import CONN_DEAD_ERRORS as _CONN_DEAD_ERRORS
from .connection import PAYLOAD_REJECTED_ERRORS as _PAYLOAD_REJECTED_ERRORS
from .connection import (
    create_pg_connection,
    is_connection_dead,
    is_dead_after_autocommit_error,
)
from .schema import qualified

if TYPE_CHECKING:
    from psycopg2.extensions import connection as PgConnection

logger = logging.getLogger(__name__)

# ``_CONN_DEAD_ERRORS`` (the "is this a connection death?" test, shared by
# ``_cursor`` discarding the cached handle and ``_store_with_reconnect`` deciding
# retry eligibility) is imported from ``.connection`` so the dispatch/result/barrier
# sites can't drift.

# How long a stored result lives before the reaper's retention sweep may delete
# it. Defaults to the executor caller-timeout default so a result always
# outlives any caller still waiting on it.
DEFAULT_RETENTION_SECONDS = 3600

# Recorded as the ``failed`` row's error text when a finished task's result
# cannot be stored (see :meth:`PgResultBackend.store_result`). The caller reads
# this instead of waiting out its full RPC timeout, so it has to say enough to
# diagnose from the caller's side alone.
PAYLOAD_UNSTORABLE_ERROR: Final[str] = (
    "Executor task finished, but its result could not be stored: the payload is "
    "not representable in a jsonb column. The work completed; only the reply was "
    "lost. See the worker-pg-executor logs for this reply key."
)

# The twin for the failure channel, and the two must never be interchanged. A
# task that RAISED has not done the work, so recording it with the text above
# would tell a reader the opposite of the truth and suppress a retry that is not
# merely safe but correct. Kept as two constants rather than one parametrised
# string so the difference is visible at both call sites.
ERROR_TEXT_UNSTORABLE: Final[str] = (
    "Executor task failed, and its error message could not be stored: the text is "
    "not representable in the error column. The task did NOT complete — this reply "
    "key is safe to retry. See the worker-pg-executor logs for this reply key."
)


# First write wins — an at-least-once redelivery of the executor message must
# not overwrite a recorded result. Built per call so ``pg_task_result`` is
# schema-qualified from the live ``DB_SCHEMA`` (resolves through PgBouncer txn
# pooling without ``search_path`` — see :mod:`queue_backend.pg_queue.schema`).
def _store_sql() -> str:
    return (
        f"INSERT INTO {qualified('pg_task_result')} "
        "(task_id, status, result, error, created_at, expires_at) "
        "VALUES (%s, %s, %s::jsonb, %s, now(), now() + make_interval(secs => %s)) "
        "ON CONFLICT (task_id) DO NOTHING"
    )


def _get_sql() -> str:
    return (
        f"SELECT status, result, error FROM {qualified('pg_task_result')} "
        "WHERE task_id = %s"
    )


def _forget_sql() -> str:
    # Null BOTH payload channels but KEEP the row: ``result`` (success payload) and
    # ``error`` (failure text — an extraction error can embed document content too),
    # so the PII scrub covers the failure channel, not just the success one. The
    # tombstone makes a redelivered ``store_result`` a ``ON CONFLICT DO NOTHING``
    # no-op (can't re-insert the payload); ``status`` still distinguishes the outcome
    # and ``expires_at`` is left untouched so the reaper still deletes the row at
    # retention.
    return (
        f"UPDATE {qualified('pg_task_result')} "
        "SET result = NULL, error = '' WHERE task_id = %s"
    )


# Poll cadence for wait_for_result: start tight (low latency for fast tasks),
# back off to a ceiling so a long-running task doesn't hammer the DB.
_POLL_INITIAL_SECONDS = 0.2
_POLL_MAX_SECONDS = 2.0

# Status vocabulary — sourced from the shared enum in unstract.core so the writer
# (here) and the backend reader agree across the process boundary. Re-exported as
# plain strings for the SQL/tests.
STATUS_COMPLETED = PgTaskStatus.COMPLETED.value
STATUS_FAILED = PgTaskStatus.FAILED.value

# One reconnect-retry for the IDEMPOTENT result write (see _store_with_reconnect).
# Literals (not env-driven) so the one-shot bound can't be widened operationally.
_STORE_RETRY_ATTEMPTS: Final = 2  # total attempts: 1 initial + 1 retry
_STORE_RETRY_BACKOFF_SECONDS: Final = 0.5


# --- Event-driven result signal (Redis) -------------------------------------
# ``PG_RESULT_SIGNAL_BACKEND=redis`` swaps the per-task DB poll for a Redis
# ready-token (RPUSH producer / BLPOP consumer), collapsing DB load from ~N
# SELECTs per in-flight task to ~1. Default ``poll`` preserves the historical
# backoff-poll behaviour byte-for-byte, so producer and consumer can be rolled
# independently — a flag mismatch merely degrades to the fallback poll, never wrong.
_SIGNAL_BACKEND_ENV: Final = "PG_RESULT_SIGNAL_BACKEND"
_SIGNAL_REDIS: Final = "redis"
_SIGNAL_POLL: Final = "poll"
# BLPOP wakes instantly on the token; this is the SAFETY-NET SELECT cadence for a
# lost/missed signal (Redis restart, publish before the consumer subscribed). Large
# because it should almost never fire — the token normally arrives first.
_REDIS_FALLBACK_POLL_SECONDS: Final = 30.0
# Socket read timeout for the SIGNAL client. MUST exceed the longest BLPOP block
# (``_REDIS_FALLBACK_POLL_SECONDS``): redis-py enforces ``socket_timeout`` on the
# blocking read, so a shorter value would raise ``redis.TimeoutError`` and abort
# every BLPOP mid-block — degrading each wait to poll and defeating the signal.
# The +5s headroom keeps the socket timeout strictly above the server-side block.
_REDIS_SOCKET_TIMEOUT_SECONDS: Final = _REDIS_FALLBACK_POLL_SECONDS + 5
# The ready-token only has to outlive the RPUSH -> BLPOP gap (normally ms). A
# generous, self-cleaning TTL covers a slow consumer; the fallback SELECT is the
# real backstop, so this need not match the result retention.
_READY_TOKEN_TTL_SECONDS: Final = 300
# Cooldown before retrying a failed signal-client build. A transient blip (Redis
# restart/failover) must NOT permanently disable the signal for the process life,
# but we also don't want to re-attempt the build on every wait — so we skip the
# rebuild only within this window after the last failure, then try again.
_REDIS_CLIENT_RETRY_COOLDOWN_SECONDS: Final = 30.0


def _signal_backend() -> str:
    """``redis`` or ``poll`` (default), read per call so a flag flip needs no
    redeploy and producer/consumer roll independently.
    """
    value = os.environ.get(_SIGNAL_BACKEND_ENV, _SIGNAL_POLL).strip().lower()
    return _SIGNAL_REDIS if value == _SIGNAL_REDIS else _SIGNAL_POLL


def _ready_key(task_id: str) -> str:
    """Redis list key carrying the ready-token for one reply key. The task_id (a
    UUID) is the ONLY thing that ever touches Redis — never the result/PII.
    """
    return f"pg_result:ready:{task_id}"


# Process-cached Redis client for the ready-signal (redis-py pools are
# thread-safe; a first-call race just orphans one short-lived pool — not a
# correctness issue). Stays ``None`` between build attempts; a build failure is
# recorded as a monotonic timestamp (not a permanent latch) so a transient blip
# only suppresses the rebuild for ``_REDIS_CLIENT_RETRY_COOLDOWN_SECONDS``, after
# which the next wait retries — Redis restart/failover self-heals.
_redis_client_singleton: redis.Redis | None = None
_redis_client_last_failure: float | None = None


def _get_result_redis_client() -> redis.Redis | None:
    """Return the process-cached Redis client, or ``None`` if it can't be built.

    Mirrors ``redis_barrier._get_redis_client``: use the canonical ``REDIS_``
    prefix so Sentinel/SSL config is inherited (``create_redis_client``'s getenv
    fallback does NOT cross-fall-back SENTINEL_MODE/SSL). The token value is
    ignored — only its arrival matters — so ``decode_responses`` is irrelevant.

    ``socket_timeout`` MUST exceed the longest BLPOP block — see
    ``_REDIS_SOCKET_TIMEOUT_SECONDS``. A build failure is not permanent: it's
    time-stamped and retried once the cooldown elapses, so a Redis restart doesn't
    disable the signal for the rest of the process life.
    """
    global _redis_client_singleton, _redis_client_last_failure
    if _redis_client_singleton is not None:
        return _redis_client_singleton
    if (
        _redis_client_last_failure is not None
        and time.monotonic() - _redis_client_last_failure
        < _REDIS_CLIENT_RETRY_COOLDOWN_SECONDS
    ):
        # Still inside the post-failure cooldown — don't hammer the rebuild.
        return None
    try:
        _redis_client_singleton = create_redis_client(
            env_prefix="REDIS_",
            # Long enough that a full BLPOP block never trips the socket read
            # timeout (which would abort the block and force a fallback poll).
            socket_timeout=_REDIS_SOCKET_TIMEOUT_SECONDS,
            socket_connect_timeout=5,
        )
        _redis_client_last_failure = None
    except Exception:
        _redis_client_last_failure = time.monotonic()
        logger.warning(
            "PgResultBackend: could not build the Redis client for the result "
            "signal; result waits fall back to poll (retry after cooldown)",
            exc_info=True,
        )
    return _redis_client_singleton


def _signal_ready(task_id: str) -> None:
    """Best-effort wake-up: RPUSH a ready-token + (re)set its TTL. NEVER raises —
    the result is already durably committed to ``pg_task_result``, so a failed
    signal only means the waiter falls back to its slow poll. Only the reply key is
    sent to Redis (no result payload / PII).
    """
    client = _get_result_redis_client()
    if client is None:
        return
    key = _ready_key(task_id)
    try:
        pipe = client.pipeline()
        pipe.rpush(key, b"1")
        pipe.expire(key, _READY_TOKEN_TTL_SECONDS)
        pipe.execute()
    except Exception:
        logger.warning(
            "PgResultBackend: result ready-signal RPUSH failed for task_id=%s; the "
            "waiter's fallback poll will still deliver the result",
            task_id,
            exc_info=True,
        )


class PgResultBackend:
    """``store_result`` / ``get_result`` / ``wait_for_result`` over ``pg_task_result``."""

    def __init__(self, conn: PgConnection | None = None) -> None:
        self._conn = conn
        # Injected connections belong to the caller — never close/recycle them.
        self._owns_conn = conn is None

    @property
    def conn(self) -> PgConnection:
        if self._conn is None:
            self._conn = create_pg_connection()
            # Every write and read here is ONE idempotent statement, so run in
            # autocommit and skip psycopg2's BEGIN … COMMIT round trips (UN-4254;
            # same reasoning as PgQueueClient.conn). Owned connections only.
            self._conn.autocommit = True
        return self._conn

    @contextlib.contextmanager
    def _cursor(self) -> Iterator[Any]:
        """Yield a cursor; commit on success, roll back + recover on error."""
        conn = self.conn
        try:
            with conn.cursor() as cur:
                yield cur
            conn.commit()
        except Exception as exc:
            conn_dead = is_dead_after_autocommit_error(exc)
            try:
                conn.rollback()
            except Exception:
                # A failed rollback proves the connection is unusable regardless
                # of the original error subclass — recycle it (and surface why,
                # rather than swallowing it silently).
                logger.warning(
                    "PgResultBackend: rollback failed; treating connection as dead",
                    exc_info=True,
                )
                conn_dead = True
            if self._owns_conn and (conn_dead or conn.closed):
                with contextlib.suppress(Exception):
                    conn.close()
                self._conn = None
            raise

    def _store_with_reconnect(self, operation: Callable[[Any], None]) -> None:
        """Run an idempotent write ``operation(cur)`` in a committed cursor,
        retrying ONCE if the cached connection was reaped while idle.

        The executor consumer caches one ``PgResultBackend`` for its lifetime
        (``consumer.py``), so this connection sits idle BETWEEN tasks and can be
        dropped server-side (PgBouncer ``server_idle_timeout`` / failover) — and
        ``conn.closed`` is a client-side flag only. ``_cursor``'s discard only
        heals the *next* call, so without a retry the *current* ``store_result``
        fails, the result is silently dropped, and the blocking caller is
        stranded forever (the exec-b11ba2f3 hang). On a dead-connection error
        ``_cursor`` (when it OWNS the connection) has already dropped the handle,
        so the retry reconnects.

        Only **owned** connections are retried: ``_cursor`` clears a dead handle
        only when ``_owns_conn`` (an injected connection is the caller's), so for
        an injected connection the retry would re-acquire the same dead handle and
        buy nothing — re-raise immediately. Production is always owned (the
        executor consumer constructs ``PgResultBackend()``).

        Safe to retry unconditionally (no reused-vs-fresh guard needed, unlike
        the non-idempotent ``PgQueueClient.send``) because **every** write routed
        through here is idempotent: ``store_result``'s
        ``INSERT … ON CONFLICT (task_id) DO NOTHING`` and ``forget``'s
        ``UPDATE … SET result = NULL, error = ''`` (re-nulling an already-nulled
        tombstone is a no-op). Re-running either after an ambiguous failure can
        neither duplicate nor clobber a recorded result. Keep this invariant if a
        third caller is added — a non-idempotent write must NOT use this helper.
        """
        for attempt in range(1, _STORE_RETRY_ATTEMPTS + 1):
            try:
                with self._cursor() as cur:
                    operation(cur)
                return
            except _CONN_DEAD_ERRORS as exc:
                # A payload rejection also lands here (ProgramLimitExceeded
                # subclasses OperationalError). Re-sending it can only fail
                # again, so hand it straight to store_result's degradation
                # rather than paying a reconnect and a second large write.
                if not is_connection_dead(exc):
                    raise
                # Last attempt, or an injected (non-owned) conn _cursor won't
                # have dropped — retrying can't reconnect, so re-raise.
                if attempt >= _STORE_RETRY_ATTEMPTS or not self._owns_conn:
                    raise
                # Describe what was observed, not an inferred cause: this catch
                # also covers deadlock / statement-timeout / admin-shutdown, not
                # only a stale idle reap. exc_info carries the type + message.
                logger.warning(
                    "PgResultBackend: result write failed with a connection-level "
                    "error; dropping the cached connection and retrying once",
                    exc_info=True,
                )
                time.sleep(_STORE_RETRY_BACKOFF_SECONDS)

    def store_result(
        self,
        task_id: str,
        *,
        result: dict[str, Any] | None = None,
        error: str | None = None,
        retention_seconds: int = DEFAULT_RETENTION_SECONDS,
    ) -> None:
        """Record a finished task's outcome under *task_id* (the reply key).

        ``result`` (a dict, e.g. ``ExecutionResult.to_dict()``) → ``completed``.
        Otherwise → ``failed`` with ``error`` text. Idempotent: a second write
        for the same key (at-least-once redelivery) is a no-op. Survives a stale
        cached connection via a one-shot reconnect-retry (see
        :meth:`_store_with_reconnect`).

        In ``PG_RESULT_SIGNAL_BACKEND=redis`` mode, once the row is committed it
        RPUSHes a ready-token so the blocking waiter wakes immediately instead of
        polling. Best-effort: a signal failure only slows the waiter (its fallback
        poll still delivers). A redelivery re-signals harmlessly — a stale token
        just expires.

        **A row is written for every outcome the database will accept, and the
        waiter is signalled unconditionally.** The payload is encoded with
        :func:`~unstract.core.jsonb.dumps_for_jsonb`, which repairs the strings a
        ``jsonb`` cast would refuse (a NUL from ``native_text`` extraction, a lone
        surrogate) so a completed task still delivers its result. If it is
        *still* unstorable — an unrepairable value such as ``NaN``, or a column
        rejection this encoder does not model — the outcome degrades to a
        ``failed`` row carrying :data:`PAYLOAD_UNSTORABLE_ERROR` rather than
        escaping. Letting it escape is what stranded callers for the full
        ``EXECUTOR_RESULT_TIMEOUT`` (UN-4126): the consumer logs and acks, so a
        write that never lands is a reply that never comes.
        """
        try:
            self._write_outcome(task_id, result, error, retention_seconds)
        finally:
            # Signal in a ``finally``: a missed wake-up costs the caller its
            # entire EXECUTOR_RESULT_TIMEOUT, which is the failure this method
            # exists to prevent, so it must not be skipped by any escape above.
            # Harmless when no row landed — the waiter re-checks and keeps
            # waiting; and a no-op in poll mode.
            self._signal_stored(task_id)

    def _write_outcome(
        self,
        task_id: str,
        result: dict[str, Any] | None,
        error: str | None,
        retention_seconds: int,
    ) -> None:
        """Write the outcome row, degrading to ``failed`` rather than escaping."""
        if result is None:
            # ``error`` lands in a ``text`` column, which rejects a NUL just as
            # ``jsonb`` does — and an extraction error can embed document content,
            # so it can also be long enough to hit SQLSTATE 54. Same net as the
            # completed branch below: without it a rejection here escapes with no
            # row, which is the UN-4126 strand reached from the failure channel.
            try:
                self._insert_outcome(
                    task_id,
                    STATUS_FAILED,
                    None,
                    sanitize_for_jsonb(error or ""),
                    retention_seconds,
                )
            except _PAYLOAD_REJECTED_ERRORS:
                logger.exception(
                    "PgResultBackend: the database rejected the error text for "
                    "reply_key=%s; recording a degraded failed row instead.",
                    task_id,
                )
                self._insert_degraded(
                    task_id, retention_seconds, message=ERROR_TEXT_UNSTORABLE
                )
            return

        try:
            result_json = dumps_for_jsonb(result)
        except (TypeError, ValueError):
            logger.exception(
                "PgResultBackend: result for reply_key=%s is not encodable for "
                "jsonb; recording a failed outcome so the caller fails fast "
                "instead of waiting out its RPC timeout.",
                task_id,
            )
            self._insert_degraded(task_id, retention_seconds)
            return

        try:
            self._insert_outcome(
                task_id, STATUS_COMPLETED, result_json, "", retention_seconds
            )
        except _PAYLOAD_REJECTED_ERRORS:
            # Wider than `DataError` alone, but deliberately NOT `Exception`.
            # `ProgramLimitExceeded` ("string too long" / "index row size exceeds
            # maximum", SQLSTATE 54) subclasses OperationalError, so a
            # DataError-only net misses it: it is first misread as a dead
            # connection by `_store_with_reconnect`, pointlessly retried, then
            # escapes — no row, caller strands for the full timeout.
            #
            # Catching bare `Exception` would fix that too, but at a worse cost:
            # a genuine connection failure would be recorded as "payload
            # unstorable", hiding a database outage behind a content error and
            # breaking the deliberate contract that infrastructure errors
            # propagate (see TestStoreResultReconnectRetry). Content rejections
            # are a knowable, closed family — enumerate them.
            # `_cursor` has already rolled back, so the connection is reusable
            # and no row exists for this key yet.
            logger.exception(
                "PgResultBackend: the database rejected the result for "
                "reply_key=%s even after sanitisation; recording a failed "
                "outcome. If this is a content rejection it is a gap in "
                "unstract.core.jsonb — capture the payload and extend it.",
                task_id,
            )
            self._insert_degraded(task_id, retention_seconds)

    def _insert_degraded(
        self,
        task_id: str,
        retention_seconds: int,
        *,
        message: str = PAYLOAD_UNSTORABLE_ERROR,
    ) -> None:
        """Record an outcome whose real payload could not be stored. Never raises.

        *message* says WHICH of the two cases this is, and it is load-bearing:
        :data:`PAYLOAD_UNSTORABLE_ERROR` means the task completed, so a retry is a
        second full LLM spend; :data:`ERROR_TEXT_UNSTORABLE` means it raised, so
        retrying is correct. The default serves the completed branch, which is the
        common one; the failure branch passes the twin explicitly.

        Last line of defence: the real payload is already lost, so a failure here
        must not propagate and cost the caller its timeout as well. If even this
        cannot be written (the database is genuinely unreachable) the caller does
        still time out — nothing in this process can prevent that — but it is
        logged as such rather than silently swallowed.
        """
        try:
            self._insert_outcome(task_id, STATUS_FAILED, None, message, retention_seconds)
        except Exception:
            logger.exception(
                "PgResultBackend: could not record even the degraded failed row "
                "for reply_key=%s — the caller will wait out its full timeout.",
                task_id,
            )

    def _insert_outcome(
        self,
        task_id: str,
        status: str,
        result_json: str | None,
        error_text: str,
        retention_seconds: int,
    ) -> None:
        """Insert one outcome row (first write wins), with the reconnect retry."""
        self._store_with_reconnect(
            lambda cur: cur.execute(
                _store_sql(),
                (str(task_id), status, result_json, error_text, retention_seconds),
            )
        )

    def _signal_stored(self, task_id: str) -> None:
        """Wake any blocking waiter once a row is committed (redis mode only)."""
        if _signal_backend() == _SIGNAL_REDIS:
            _signal_ready(str(task_id))

    def get_result(self, task_id: str) -> dict[str, Any] | None:
        """Return ``{status, result, error}`` if the row exists, else ``None``.

        ``result`` is the decoded JSONB dict (psycopg2 parses ``jsonb`` to a
        Python ``dict``) or ``None``. A *missing* row (this returns ``None``) means
        the task has not finished. A *present* row with ``result is None`` is either
        a ``failed`` outcome or a ``completed`` **tombstone** whose payload was
        already cleared by :meth:`forget` post-consume — ``status`` distinguishes
        them, so a payload-poll must not treat a completed row as carrying a result.

        No reconnect-retry here (unlike :meth:`store_result`). The invariant it
        relies on: the connection is never idle long enough to be reaped when
        ``get_result`` runs. In ``poll`` mode the wait polls every ~0.2–2s so the
        connection stays warm; in ``redis`` mode the immediate race-check runs on a
        just-used connection, and every in-loop ``get_result`` is preceded by a
        :meth:`close` before the (possibly long) ``BLPOP`` — so each in-loop read
        reconnects fresh rather than reusing a connection idled across the block.
        Either way a failure here is a genuine error, not a stale reap. If a
        long-lived backend ever gains a one-shot ``get_result`` lookup that can sit
        idle, revisit this.
        """
        with self._cursor() as cur:
            cur.execute(_get_sql(), (str(task_id),))
            row = cur.fetchone()
        if row is None:
            return None
        status, result, error = row
        return {"status": status, "result": result, "error": error}

    def wait_for_result(
        self,
        task_id: str,
        timeout: float,
        *,
        poll_interval: float = _POLL_INITIAL_SECONDS,
    ) -> dict[str, Any] | None:
        """Block until the result row appears or *timeout* seconds elapse.

        Returns the ``{status, result, error}`` dict, or ``None`` on timeout.

        ``PG_RESULT_SIGNAL_BACKEND=redis`` waits on a Redis ready-token (``BLPOP``)
        and does one authoritative :meth:`get_result` on wake — DB load scales with
        throughput, not concurrency. The DB stays the source of truth: an immediate
        check closes the publish-before-wait race, a slow fallback ``get_result``
        every ``_REDIS_FALLBACK_POLL_SECONDS`` backstops a lost signal, and any Redis
        error degrades to the poll path for the rest of the wait. ``=poll`` (default)
        is exactly the historical capped-exponential backoff poll (PgBouncer-safe),
        shared with the backend's ``DjangoQueueTransport`` poller.
        """
        if _signal_backend() != _SIGNAL_REDIS:
            return self._poll_for_result(task_id, timeout, poll_interval)
        return self._wait_via_redis(task_id, timeout, poll_interval)

    def _poll_for_result(
        self, task_id: str, timeout: float, poll_interval: float
    ) -> dict[str, Any] | None:
        """The historical backoff poll — primary path in ``poll`` mode and the
        degradation target when Redis is unavailable mid-wait.
        """
        return poll_for_row(
            lambda: self.get_result(task_id),
            timeout,
            initial=poll_interval,
            maximum=_POLL_MAX_SECONDS,
        )

    def forget(self, task_id: str) -> None:
        """Drop the stored payload once the blocking caller has consumed it.

        Nulls both payload columns (``result`` and ``error``) but keeps the row as a
        tombstone (see :func:`_forget_sql`), so a redelivered executor message can't
        re-insert the payload and the reaper still flushes the empty row at
        ``expires_at``. Cutting the payload here shrinks the window that customer
        extraction data (PII) sits in ``pg_task_result`` from the full retention TTL
        down to the read-and-clear latency; the TTL sweep remains the backstop for
        any result that is never consumed (caller crash / timeout).

        Runs through :meth:`_store_with_reconnect` because the UPDATE is idempotent
        (same rationale as ``store_result``): a transient dead-connection blip
        self-heals on the one-shot retry rather than silently costing the full TTL.

        **Best-effort by contract — never raises.** Every caller must sit under a
        never-raises guard (today: ``PgExecutionDispatcher.dispatch``'s
        ``except Exception -> failure``), because ``forget`` runs AFTER the caller
        already holds the result — a raise here would turn a successful RPC into a
        failure. A failure that survives the retry is logged and swallowed; the
        retention sweep still bounds the exposure. A *systematic* failure (revoked
        UPDATE privilege, recurring errors) is therefore observable only as a stream
        of this WARNING — alert on the ``"could not clear result"`` string until a
        ``forget_failures`` metric is wired (deferred to the pg_queue metrics work).
        """
        try:
            self._store_with_reconnect(
                lambda cur: cur.execute(_forget_sql(), (str(task_id),))
            )
        except Exception:
            logger.warning(
                "PgResultBackend.forget: could not clear result for task_id=%s "
                "after retry; the retention sweep will flush it at expires_at",
                task_id,
                exc_info=True,
            )

    def _wait_via_redis(
        self, task_id: str, timeout: float, poll_interval: float
    ) -> dict[str, Any] | None:
        """Redis ``BLPOP`` fast-path + DB-source-of-truth fallback poll.

        Correctness never depends on the signal: every wake does an authoritative
        ``get_result``, and a missed/absent token is caught by the fallback SELECT
        (bounded by ``_REDIS_FALLBACK_POLL_SECONDS``) or the final check at deadline.
        """
        deadline = time.monotonic() + timeout
        # Close the publish-before-wait race: the row may already be committed
        # (executor finished + RPUSHed before we started waiting).
        row = self.get_result(task_id)
        if row is not None:
            return row
        client = _get_result_redis_client()
        if client is None:
            # Redis unavailable up front → degrade to pure poll, but for the
            # REMAINING budget: the deadline was set above and ``get_result`` has
            # already burned some of it, so passing the full ``timeout`` here would
            # let ``wait_for_result`` overshoot its contract. Mirrors the mid-BLPOP
            # RedisError fallback below.
            return self._poll_for_result(
                task_id, max(0.0, deadline - time.monotonic()), poll_interval
            )
        key = _ready_key(task_id)
        while True:
            # Do NOT pin the DB connection across the (possibly long) BLPOP: an
            # idle server connection would be reaped by PgBouncer and the next
            # get_result (no reconnect-retry) would fail. Closing here means each
            # get_result below runs on a freshly-opened connection — preserving the
            # "fresh, never idle-reaped" invariant get_result relies on.
            self.close()
            remaining = deadline - time.monotonic()
            if remaining <= 0:
                return self.get_result(task_id)  # final authoritative check
            # BLPOP wakes instantly on the token; cap the block at the fallback
            # cadence so a missed signal still triggers a backstop SELECT. Timeout
            # is > 0 here (remaining > 0), and 0 would mean "block forever".
            block = min(remaining, _REDIS_FALLBACK_POLL_SECONDS)
            try:
                client.blpop([key], timeout=block)
            except redis.exceptions.RedisError:
                # A Redis-level error (blip, disconnect, ...) → degrade to the poll
                # path for the remainder. Narrowed to ``RedisError`` so a normal
                # empty BLPOP (returns None, not an exception) stays on the loop and
                # non-Redis programmer errors propagate rather than silently degrade.
                logger.warning(
                    "PgResultBackend: BLPOP failed for task_id=%s; falling back to "
                    "poll for the rest of the wait",
                    task_id,
                    exc_info=True,
                )
                return self._poll_for_result(
                    task_id, max(0.0, deadline - time.monotonic()), poll_interval
                )
            row = self.get_result(task_id)
            if row is not None:
                return row
            # Woken but row not yet visible (rare) or a fallback tick → loop.

    def close(self) -> None:
        """Close an owned connection (injected connections are the caller's)."""
        if self._owns_conn and self._conn is not None and not self._conn.closed:
            with contextlib.suppress(Exception):
                self._conn.close()
        self._conn = None

    def __enter__(self) -> Self:
        return self

    def __exit__(self, *exc: object) -> None:
        self.close()