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) -> WriteResultInsert-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) -> AssessmentReportWhat 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) -> WriteResultTable-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) -> NoneIdempotent 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) -> WriteResultA 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) -> ReadResultRaw 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) -> ReadResultThe 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) -> ReconcileReportMake 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) -> WriteResultSwap 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) -> WriteResultAtomically 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) -> AnyThe 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) -> WriteResultPersist 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) -> WriteResultInsert 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) -> WriteResultAtomically 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.