pg-mdm requirements for pg_trickle in v0.9 through v0.11

pg-mdm v0.8 creates a private Graph V1 graph. v0.9 starts using that graph for live refresh. v0.10 tests the failure and recovery cases around that refresh. v0.11 qualifies the complete combination for release.

This document describes what those releases need from pg_trickle. It is based on the pg-mdm v0.9 through v0.11 roadmap entries. The repository does not yet contain separate detailed plans for those releases, so these requirements need review before implementation starts.

Terms used here

  • A graph member is a pg_trickle stream table created from one compiled MDM stage.
  • A strict refresh is a public operation that checks the expected graph contract, refreshes the graph, and returns the exact source boundary used by the refresh.
  • A source boundary is the complete, identified set of source changes that a refresh consumed. Its digest lets pg-mdm record exactly what produced an MDM publication.
  • An external graph is a graph with orchestration_mode = EXTERNAL. pg-mdm starts its refreshes. The normal pg_trickle scheduler must not refresh it independently.
  • A qualifying graph shape is a pg-mdm Graph V1 fixture whose compiled queries support differential or scoped maintenance after initial population.

v0.9 requires transactional graph refresh

v0.9 adds mdm.preview(), mdm.refresh(), and administrative rebuild. The live path must call the public pg_trickle strict-refresh function inside the same caller transaction as MDM publication.

The existing Graph V1 call has this shape:

SELECT *
FROM pgtrickle.refresh_graph_strict(
    ARRAY['mdm_graph.member_root'::regclass],
    expected_graph_digest,
    'ALLOW'
);

The result must include the following information:

  • Graph contract version 1.
  • A unique graph_refresh_id.
  • A machine-readable source_boundary.
  • A 32-byte source_boundary_digest.
  • A completeness result that can prove the boundary is complete.

For the qualifying path, source_boundary->>'completeness' must be PROVEN. pg-mdm stores the refresh ID, boundary, and boundary digest with the publication observation or publication that follows the refresh.

Keep the graph and MDM transaction together

The strict-refresh function must not commit independently. The caller owns the transaction. A successful refresh followed by a successful MDM publication must commit both changes together.

If any later step fails, PostgreSQL must roll back all of the following:

  • changes to graph member rows;
  • source-change positions consumed by the graph;
  • the graph_refresh_id and source-boundary evidence written by pg-mdm;
  • MDM memberships, golden values, reviews, and publication history; and
  • public MDM output-table changes.

An injected failure after graph maintenance must leave the graph and its source positions in their previous state. A retry must not skip source changes or apply them twice.

Check the graph contract before changing data

refresh_graph_strict() must compare the supplied graph digest with the current Graph V1 contract before it changes a member or consumes a source position. A mismatch must fail without partial graph work.

The operation must also reject a missing member, a changed member or source owner, a replaced source relation, a revoked source grant, or a changed tracked contract. The error must identify the failed condition with a stable upstream code or identifier. pg-mdm rejects a changed role binding before it calls the operation.

Return a complete source boundary

The source boundary must describe the source state that the refresh used. It must not claim success when a source change is outside the captured or scanned range, when a source is unavailable, or when a required source position cannot be proven.

When source rows change during a refresh, the operation must define which changes belong to the returned boundary. Changes after that boundary must remain for a later refresh. The operation must not silently combine an older graph result with a newer source position.

pg-mdm reads the complete terminal evidence relations after a successful strict refresh. The member and graph contracts must continue to identify those relations and their dependencies through the public Graph V1 functions.

Preserve the selected role and row security

Definition-derived SQL must run as the current owner of each graph member, with row-level security enabled. The pg-mdm selected execution role must therefore own every graph member. A helper role must not bypass that owner’s table privileges or row-level security policies.

The following cases must fail before graph mutation:

  • the execution role loses schema USAGE;
  • the execution role loses source-table SELECT;
  • the execution role loses source-table MAINTAIN;
  • the source owner changes;
  • the source relation is replaced under the same name; or
  • a tracked row-level-security policy or policy dependency changes.

Graph members retain the stricter owner-equivalent requirement. Delegated source access does not grant permission to operate a graph member.

Graph V1 does not include caller role memberships, role bindings, or session-dependent row-level-security inputs in its digest. pg_trickle rechecks current database authorization. pg-mdm must validate its selected role binding and session-dependent visibility inputs before it calls strict refresh.

v0.9 requires stable graph lifecycle behavior

The v0.8 graph must remain usable by the v0.9 refresh path without private catalog access. The following public functions and contracts must remain compatible:

pgtrickle.integration_capabilities()
pgtrickle.create_stream_table(...)
pgtrickle.stream_table_contract(regclass)
pgtrickle.graph_contract(regclass[])
pgtrickle.refresh_graph_strict(regclass[], bytea, text)
pgtrickle.drop_stream_table(text, boolean)

Every member must continue to use these settings:

initialize = false
orchestration_mode = EXTERNAL
cdc_mode = trigger
refresh_mode = AUTO

The first strict refresh may use a FULL refresh to establish the baseline. After that baseline, qualifying graph shapes must report differential or scoped maintenance in node_results. A whole-query FULL refresh remains a correctness fallback, but it must report a stable action and reason. The qualification workload must measure repeated FULL fallback so AUTO cannot hide a graph that never refreshes incrementally.

pg_trickle must not refresh an external graph through ordinary scheduler work. pg-mdm must be the caller that starts the strict refresh.

The existing contract functions must continue to report the member owner, source identities, dependencies, output schema, contract generation, and contract digest. graph_contract() must continue to report the complete root closure, topological order, source set, member set, and graph digest.

v0.10 requires operational reliability

The v0.10 roadmap does not name a new pg_trickle SQL feature. It requires the existing Graph V1 implementation to behave correctly under operational stress. pg-mdm needs the following evidence from the packaged upstream release.

Concurrency and lifecycle locking

Concurrent refresh, graph installation, rebuild, drop, and restore operations must not use stale contracts or cross one another’s graph members.

The upstream behavior must prove that:

  • a refresh and a drop cannot both commit as if they ran alone;
  • a refresh detects a graph change instead of publishing against an old digest;
  • concurrent source writes have a defined boundary and remain available for a later refresh when they fall outside the current boundary; and
  • one execution role cannot use another role’s temporary graph-creation access or graph objects.

Crash recovery

After a PostgreSQL or backend failure during graph refresh, recovery must leave the graph at a valid transaction boundary. It must not leave a graph member partly updated or advance a source position without the corresponding graph changes.

pg-mdm then verifies that the recovered graph can pass contract inspection and can complete a later strict refresh. A failed refresh must not create a false MDM publication.

Clone, backup, restore, and upgrade

The supported package must preserve Graph V1 behavior across the backup, restore, clone, and extension-upgrade cases that pg-mdm supports.

For a clean logical restore, pg-mdm rebuilds database-local bindings after the roles and source relations are restored. The upstream contract must not require pg-mdm to copy private catalog rows or reuse old relation OIDs.

A restored or cloned database must not refresh the original database’s graph. Role bindings, source relation identities, member relation identities, and contract digests must remain local to the database where the graph runs.

An extension upgrade must preserve existing Graph V1 members and their contracts, or reject the upgrade path before it can create an inconsistent graph. The upgrade test must use the packaged artifacts, not a source checkout.

Resource limits and fallback

When the graph cannot prove a complete source boundary or cannot complete a required stage within its configured limits, the strict refresh must fail closed. It must not publish incomplete terminal evidence or advance a source position as if the work had completed. The qualification suite must name the tested lock-timeout, statement-timeout, memory, and graph-size limits.

pg-mdm later uses its full resolver as the correctness reference. A pg_trickle whole-query full refresh allowed by full_policy = 'ALLOW' is an exact graph result, not a resource-failure state. Other resource pressure must abort the caller transaction or return a defined failure state. It must never produce a partial graph that looks valid.

Errors must remain stable enough for pg-mdm to report an actionable result. The error must distinguish at least contract mismatch, incomplete source boundary, authorization failure, concurrent lifecycle change, and resource failure.

v0.11 requires release qualification

v0.11 qualifies the complete system for a supported release. It does not add a new upstream runtime feature. The release must provide a fixed, installable pg_trickle package and evidence for the public behavior used by pg-mdm.

The qualification must cover:

  • Graph V1 capability discovery and contract version checks;
  • canonical member and graph contracts;
  • durable EXTERNAL orchestration;
  • strict refresh and complete source boundaries;
  • commit and rollback of graph changes and source positions;
  • inserts, updates, and deletes in source tables;
  • no-op refreshes;
  • concurrent source writes and lifecycle operations;
  • injected refresh failures;
  • full rebuild and fallback behavior;
  • crash recovery;
  • clone isolation;
  • backup, restore, and extension upgrade; and
  • authorization, row-level security, and forbidden private-access checks.

The qualification must run against the exact package that pg-mdm uses in CI. The package record must include the upstream version, release commit, tag object, package URL, PostgreSQL major, and SHA-256. pg-mdm records those values in its DEPENDENCIES.md and reruns the admission suite for each dependency update.

The shared suite must compare generated graph results with the independent full-resolution reference path. It must cover source inserts, updates, deletes, stewardship changes, merges, splits, golden-value-only changes, rollback, concurrency, fallback, rebuild, and upgrade.

Features that are not required

The v0.9 through v0.11 pg-mdm path does not require the following upstream features:

  • output_delta_consumer or Delta V1 consumption;
  • WAL capture;
  • direct access to pg_trickle private catalogs, change buffers, or scheduler state;
  • autonomous refresh of an EXTERNAL graph; or
  • MDM identity resolution and publication logic inside pg_trickle.

The V1 integration uses trigger capture and complete terminal-relation reads after a strict Graph V1 refresh. pg_trickle maintains the changing relational facts. pg-mdm resolves identity and publishes MDM results.

Release gate

pg-mdm can complete v0.9 only when one released and checksummed pg_trickle artifact passes the strict-refresh admission tests. v0.10 cannot close until its pinned artifact passes the cumulative operational failure and recovery tests. v0.11 cannot close until its exact pinned package passes the shared conformance and differential-versus-full qualification suite. If the pinned artifact changes, pg-mdm must rerun every applicable earlier gate.

The current pg-mdm baseline is pg_trickle 0.104.0. The baseline supports the owner-equivalent Graph V1 path. It does not remove the separate v0.8 gate for delegated source authorization.