popoto.backends.postgres.plan

popoto.backends.postgres.plan

SQL rendering of query plans for the Postgres backend (#759 M1b).

Two steps, kept apart on purpose:

1. :func:popoto.backends.planning.plan_from_call (backend-neutral, run by the query layer for every non-Redis backend) turns the public call (:class:QueryCall, the call exactly as Query.filter/count received it) into a protocol :class:QueryPlan -- where, order_by, limit, project -- while reproducing the query layer's validation (the same QueryException text for an unknown parameter, a non-boolean __isnull, a malformed __between, a partitioned SortedField queried without its partition) and its ordering rules (explicit order_by > Meta.order_by > the first filtered SortedField's score order). On Redis those rules live in the moved filter body; here they become plan terms.
2. :func:render_where / :func:render_order (this module) turn plan terms into SQL over a :class:~popoto.backends.postgres.schema.TableSpec.

Null handling mirrors Redis's set algebra, not SQL's three-valued logic: a negated subtree renders as NOT coalesce(…, false), so ~Q(x=1) includes rows whose x is NULL exactly as all_keys - matches does.

to_db_value(py_type, value)

A Python value as the column wants it. A naive datetime is UTC -- the same instant SortedFieldMixin.convert_to_numeric scores it as (#519) -- so comparisons and ordering agree with Redis.

Source code in src/popoto/backends/postgres/plan.py

def to_db_value(py_type: type, value: Any) -> Any:
    """A Python value as the column wants it. A naive ``datetime`` is UTC --
    the same instant ``SortedFieldMixin.convert_to_numeric`` scores it as
    (#519) -- so comparisons and ordering agree with Redis."""
    if value is None:
        return None
    if py_type is datetime.datetime and isinstance(value, datetime.datetime):
        if value.tzinfo is None:
            return value.replace(tzinfo=datetime.timezone.utc)
        return value
    return value

to_column_value(ts, name, value)

value of field name as its column stores it (M1.1): the collections as jsonb (:mod:.codec), a TagField as its normalised text[], a Relationship as the target's key string.

Source code in src/popoto/backends/postgres/plan.py

def to_column_value(ts: TableSpec, name: str, value: Any) -> Any:
    """``value`` of field ``name`` as its column stores it (M1.1): the
    collections as ``jsonb`` (:mod:`.codec`), a ``TagField`` as its
    normalised ``text[]``, a ``Relationship`` as the target's key string."""
    py_type = ts.field_types[name]
    kind = ts.kind(name)
    if kind in TAG_KINDS:
        from ...fields.tag_field import TagFieldMixin

        return TagFieldMixin._normalize(getattr(value, "_data", value))
    if value is None:
        return None
    if kind in RELATIONSHIP_KINDS:
        return value if isinstance(value, str) else value.db_key.redis_key
    if ts.is_json(name):
        return _jsonb(encode_json(py_type, value, capped=name in ts.capped_fields))
    if py_type is bytes and isinstance(value, (bytearray, memoryview)):
        return bytes(value)
    return to_db_value(py_type, value)

render_where(ts, kinds, where, *, live=True)

(" WHERE …", params), or ("", []) for no predicate.

On a Meta.ttl model the TTL read filter is ANDed on (M5, .ttl): every reader that scopes the record table through this -- select, count, ranking, search, recall -- sees only rows that have not expired. live=False leaves it off. A model without Meta.ttl renders exactly as before.

Source code in src/popoto/backends/postgres/plan.py

def render_where(
    ts: TableSpec,
    kinds: dict[str, str],
    where: Optional[Predicate],
    *,
    live: bool = True,
) -> tuple[str, list[Any]]:
    """``(" WHERE …", params)``, or ``("", [])`` for no predicate.

    On a ``Meta.ttl`` model the TTL read filter is ANDed on (M5, ``.ttl``):
    every reader that scopes the record table through this -- ``select``,
    ``count``, ranking, search, ``recall`` -- sees only rows that have not
    expired. ``live=False`` leaves it off. A model without ``Meta.ttl``
    renders exactly as before."""
    from .ttl import and_live

    params: list[Any] = []
    sql = "" if where is None else " WHERE " + _pred_sql(ts, kinds, where, params)
    return (and_live(ts, sql) if live else sql), params

non_null_fields(where)

Fields the predicate guarantees are not NULL: a range lookup (or a non-None exact match) in the top-level conjunction.

Source code in src/popoto/backends/postgres/plan.py

def non_null_fields(where: Optional[Predicate]) -> frozenset[str]:
    """Fields the predicate guarantees are not ``NULL``: a range lookup (or a
    non-``None`` exact match) in the top-level conjunction."""
    items = where.items if isinstance(where, And) else (where,) if where else ()
    out = set()
    for item in items:
        if isinstance(item, Cond) and (
            item.op in _NON_NULL_OPS or (item.op is Op.EXACT and item.value is not None)
        ):
            out.add(item.field)
    return frozenset(out)

render_order(ts, terms, not_null=frozenset())

ORDER BY for terms plus the deterministic tie-break _pk COLLATE "C" (Redis's bytewise member order).

A NULL sorts as the type's zero value, as prepare_results's getattr(obj, f) or type() does; a descending primary term reverses the whole order, as list(reversed(…)) does on Redis. For a field in not_null (the WHERE already excludes its NULL rows) the bare column is ordered, so its B-tree can serve the ORDER BY … LIMIT.

Source code in src/popoto/backends/postgres/plan.py

def render_order(
    ts: TableSpec,
    terms: tuple[OrderTerm, ...],
    not_null: frozenset[str] = frozenset(),
) -> str:
    """``ORDER BY`` for ``terms`` plus the deterministic tie-break
    ``_pk COLLATE "C"`` (Redis's bytewise member order).

    A ``NULL`` sorts as the type's zero value, as ``prepare_results``'s
    ``getattr(obj, f) or type()`` does; a descending primary term reverses
    the whole order, as ``list(reversed(…))`` does on Redis. For a field in
    ``not_null`` (the WHERE already excludes its ``NULL`` rows) the bare
    column is ordered, so its B-tree can serve the ``ORDER BY … LIMIT``.
    """
    parts = []
    descending = bool(terms) and terms[0].descending
    for term in terms:
        if term.random:
            parts.append("random()")
            continue
        assert term.field is not None
        if ts.is_json(term.field):
            raise _collection_order_error(term.field)
        col = quote_ident(term.field)
        py_type = ts.field_types[term.field]
        zero = _ZERO.get(py_type)
        known_not_null = term.field in not_null
        if known_not_null:
            zero = None
        expr = f"coalesce({col}, {zero})" if zero is not None else col
        if py_type is str:
            expr += ' COLLATE "C"'
        direction = "DESC" if term.descending else "ASC"
        nulls = ""
        if zero is None and not known_not_null:
            nulls = " NULLS LAST" if term.descending else " NULLS FIRST"
        parts.append(f"{expr} {direction}{nulls}")
    parts.append(f'"_pk" COLLATE "C" {"DESC" if descending else "ASC"}')
    return " ORDER BY " + ", ".join(parts)