Skip to content

common_db.client.pg

pg.py — PGClient: the op set, each the fastest known mechanics.

Every op: composed SQL (sql.Identifier — injection impossible by construction), memory-streaming COPY (zero disk, zero csv — the legacy killer), ONE transaction boundary per logical op, and a typed result.

The op mechanics (the design contract, proven by the landscape research): append COPY straight to target upsert COPY -> TEMP staging (ON COMMIT DROP) -> INSERT..ON CONFLICT DO UPDATE (WHERE row IS DISTINCT — unchanged rows untouched, counted); xmax=0 discriminates inserted vs updated reconcile upsert legs + the stale leg: archive to {table}__retired (retired_on/reason/batch), guarded by the stale_cap tripwire replace the scope’s rows swapped for the data (DELETE + staged INSERT, no conflict machinery) — full swap is an explicit opt-in, the stale_cap tripwire guards the delete assess staging only + count queries — the target is never touched query/read composed reads -> arrow -> any output (ReadConfig control)

Classes

PGClient

Bases: BaseDBClient

The Postgres engine — fills every op with the staging/COPY mechanics.

append

def append(data: Any, table: TableRef | str, *, config: WriteConfig | None = None, target: str | None = None) -> WriteResult

Insert-only bulk — COPY straight to the target (keyless ledgers’ op of record; no conflict machinery at all).

assess

def assess(data: Any, table: TableRef | str, *, on: tuple[str, ...] | None = None, scope: dict[str, Any] | None = None, target: str | None = None) -> AssessmentReport

What WOULD happen: schema/dtype issues (against the LIVE types) + insert/update/unchanged diff counts (+ stale count when a scope is given). Never writes — and never raises: a missing table or broken input comes back as a failure REPORT.

combine

def combine(sources: list[TableRef | str], into: TableRef | str, *, replace: bool = True, target: str | None = None) -> WriteResult

Table-to-table UNION-ALL rebuild (the live legacy stage_combine pattern, no frame in the loop) — one transaction, TRUNCATE not DROP (the target’s grants/indexes survive, unlike the legacy drop). Columns are NAME-aligned from the first source — physical column order never scrambles a leg.

ensure

def ensure(schema: DBSchemaBuilder | sa.Table, *, target: str | None = None) -> None

Idempotent bootstrap — safe every process start.

key_upgrade

def key_upgrade(data: Any, table: TableRef | str, *, match_on: tuple[str, ...], placeholder: dict[str, Any], config: WriteConfig | None = None, target: str | None = None) -> WriteResult

A better-keyed row REPLACES its placeholder (the dividend period-‘unknown’ -> named-period case): target rows matching match_on AND the placeholder predicate are deleted, then the data upserts normally — same key upgraded, one transaction. An empty placeholder is REFUSED — it would delete every match_on row.

query

def query(statement: str | sql.SQL | sql.Composed, params: dict[str, Any] | tuple | None = None, *, config: ReadConfig | None = None, target: str | None = None) -> ReadResult

Raw SQL -> any output. Parameterized (%s / %(name)s), never interpolated. Streams in cfg.batch_rows chunks, built COLUMNAR (one arrow array per column, never per-row dicts); an empty result keeps its declared dtypes via the type_code (OID) map.

read

def read(table: TableRef | str, *, columns: tuple[str, ...] | None = None, where: dict[str, Any] | None = None, latest_per: tuple[str, ...] | None = None, order: str | tuple[str, ...] | None = None, descending: bool = False, limit: int | None = None, config: ReadConfig | None = None, target: str | None = None) -> ReadResult

The composed read: column scope, dict predicates (scalar, (lo,hi) range — half-open with a None bound, list membership, None -> IS NULL), latest-row-per-key, order (one column or several, descending flips), limit.

reconcile

def reconcile(data: Any, table: TableRef | str, *, scope: dict[str, Any], on: tuple[str, ...] | None = None, update_cols: tuple[str, ...] | None = None, coalesce: tuple[str, ...] = (), on_missing: ReconcilePolicy | str | None = None, config: WriteConfig | None = None, target: str | None = None) -> ReconcileReport

Make the table match the data WITHIN the declared scope — update changed, insert new, and stale rows (in target’s scope, absent from data) handled per policy. Scope is MANDATORY: a partial fetch can never nuke history. The stale_cap tripwire refuses mass-removal. update_cols/coalesce thread to the merge leg exactly as in upsert (defaults preserve the every-non-key-column behavior).

replace

def replace(data: Any, table: TableRef | str, *, scope: dict[str, Any] | None = None, full: bool = False, config: WriteConfig | None = None, target: str | None = None) -> WriteResult

Swap the scope’s rows for the data — count, DELETE, staged INSERT, ONE transaction, no conflict machinery (the truncate-and-reload pattern made scope-safe). scope=None means FULL swap and demands the explicit full=True opt-in; the stale_cap tripwire REFUSES a delete larger than the cap (target untouched — refusal is total).

replace_many

def replace_many(replacements: list[tuple[Any, TableRef | str, WriteConfig | None]], *, target: str | None = None) -> tuple[WriteResult, ...]

Atomically full-replace several co-versioned tables.

This is deliberately narrower than repeated replace calls: every target is a full snapshot and every staging/alignment check completes before the first target is mutated. One failed insert rolls the whole group back, and every returned receipt reports failure in that case.

replace_partitions

def replace_partitions(data: Any, table: TableRef | str, *, partition_by: tuple[str, ...], config: WriteConfig | None = None, target: str | None = None) -> WriteResult

Atomically replace only the composite partitions present in data.

Unlike a cartesian dict scope, the distinct partition tuples from the staged input are the deletion witness. This is the correction-safe snapshot verb: an amended fund report can remove a position without touching another fund or report date, and every partition in the batch commits or rolls back together.

retrieve

def retrieve(name: str, *, key: dict[str, Any] | None = None, table: TableRef | str = 'public.stash', target: str | None = None) -> Any

The stash’s other half — returns the object (frames dtype-faithful) or None when absent. NOT fail-safe by design (the pipes contract: a broken handoff must raise loudly, never limp).

stash

def stash(name: str, obj: Any, *, key: dict[str, Any] | None = None, table: TableRef | str = 'public.stash', prune_days: int | None = 7, target: str | None = None) -> WriteResult

Persist an object for another run/pod to retrieve. Frames travel as FAITHFUL arrow-ipc bytes (dtypes intact — better than the legacy jsonb round-trip); anything else as compressed pickle. Prunes THIS name’s rows older than prune_days on each save (the legacy pattern kept, scoped so one producer never reaps another’s stash).

upsert

def upsert(data: Any, table: TableRef | str, *, on: tuple[str, ...] | None = None, update_cols: tuple[str, ...] | None = None, coalesce: tuple[str, ...] = (), config: WriteConfig | None = None, target: str | None = None) -> WriteResult

Insert new + update changed on key. on defaults to the table’s PK; update_cols defaults to every non-key column; coalesce columns enrich-never-null-out. Unchanged rows are NOT touched (row IS DISTINCT guard) and are counted honestly.

upsert_append

def upsert_append(upsert_data: Any, append_data: Any, table: TableRef | str, *, on: tuple[str, ...] | None = None, update_cols: tuple[str, ...] | None = None, coalesce: tuple[str, ...] = (), config: WriteConfig | None = None, target: str | None = None) -> WriteResult

Atomically merge keyed rows and append server-default-key rows.

Incarnation tables earn this combined verb: rows matched to an existing identity upsert by id while genuinely new incarnations omit that identity so Postgres can allocate it. Two public calls would create two transactions and permit a half-applied universe. This verb commits both legs together; either failure rolls both back.