Upgrading pg_trickle

This guide covers upgrading pg_trickle from one version to another.

0.92.0 to 0.93.0

Install the v0.93.0 shared library and extension files, then run the upgrade inside the normal quiesced boundary:

SELECT pgtrickle.preflight_upgrade();
SELECT pgtrickle.quiesce(60);
ALTER EXTENSION pg_trickle UPDATE TO '0.93.0';
SELECT pgtrickle.resume_all();

The migration adds MANAGED orchestration ownership and contract generations to existing stream tables. Existing tables remain scheduler-owned. New integrations can opt a table into EXTERNAL ownership with pgtrickle.set_orchestration_mode(). Verify the contract surface with pgtrickle.integration_capabilities(), stream_table_contract(), and graph_contract() after the upgrade.

0.91.0 to 0.92.0

Install the 0.92.0 shared library and extension files, restart PostgreSQL, and run the read-only preflight before starting the upgrade:

SELECT pgtrickle.preflight_upgrade();
SELECT pgtrickle.validate_recovery();

Do not continue when either report contains BLOCKER, REINITIALIZATION_REQUIRED, or OPERATOR_INTERVENTION_REQUIRED. Quiesce the capture boundary, apply the extension migration, and resume it only after the new library and SQL objects are installed:

SELECT pgtrickle.quiesce(60);
ALTER EXTENSION pg_trickle UPDATE TO '0.92.0';
SELECT pgtrickle.resume_all();

quiesce() drains scheduler work without changing stream-table results. resume_all() reopens capture after the upgrade; it does not repair suspended tables or invent a missing frontier.

v0.92 records the database OID and PostgreSQL system identifier that own CDC. After a dump/restore, clone, or promotion, inspect:

SELECT pgtrickle.capture_instance_status();
SELECT pgtrickle.validate_recovery();

If ownership changed, capture remains quarantined. A superuser must explicitly adopt the restored database, after which every stream table requires a protected rebuild:

SELECT pgtrickle.recover_capture_instance();
SELECT pgtrickle.reinitialize_stream_table('public.my_stream_table');

Recovery never advances a frontier when a source, trigger, slot, buffer, or required WAL history is missing. Stable reason codes (CDC_TRIGGER_MISSING, CDC_SLOT_MISSING, CDC_WAL_UNAVAILABLE, CDC_BUFFER_MISSING, CDC_BUFFER_CORRUPT, CDC_CLONE_DETECTED, and RECOVERY_FRONTIER_UNPROVEN) identify the required action in the catalog and health reports. See the v1.0 upgrade support manifest for the bounded PostgreSQL 18 source-version matrix.

0.90.0 to 0.91.0

Install the 0.91.0 shared library and extension files, restart PostgreSQL, then run:

ALTER EXTENSION pg_trickle UPDATE TO '0.91.0';

v0.91 adds protected defining-query replacement and deterministic source-schema evolution handling. Use pgtrickle.explain_alter() before changing a query; only a fully proven no-op reuses the existing materialization. Other valid changes build a shadow result and publish it with an atomic cutover.

Destructive or ambiguous source DDL suspends affected stream tables with a stable SOURCE_* reason visible in pgtrickle.health_check(). Repair the source, update the defining query when its dependency OID changed, and then run:

SELECT pgtrickle.reinitialize_stream_table('public.my_stream_table');

The migration is additive and does not rewrite existing stream-table results.

0.89.0 to 0.90.0

Install the 0.90.0 shared library and extension files, restart PostgreSQL, then run:

ALTER EXTENSION pg_trickle UPDATE TO '0.90.0';

The migration adds exact freshness evidence columns, a bounded controller summary, provenance columns to registered change buffers, and the pgtrickle.freshness() API. Existing rows remain nullable and are not treated as exact measurements. Existing interval targets continue safely and report EVIDENCE_UNAVAILABLE when PostgreSQL commit timestamps are disabled.

New interval targets require track_commit_timestamp = on and a base-table source path with managed provenance. Enable it in postgresql.conf and restart PostgreSQL before declaring a target:

track_commit_timestamp = on

Verify the upgrade and exact evidence:

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';
SELECT * FROM pgtrickle.freshness();
SELECT table_name, p95_freshness_ms, sla_status
  FROM pgtrickle.pg_stat_pgtrickle;

commit_to_visible_ms is populated only after a committed refresh is settled by a later scheduler or refresh transaction. It is never inferred from refresh duration. The controller is advisory and remains bounded by the existing max_dynamic_refresh_workers limit.

0.88.0 to 0.89.0

Install the 0.89.0 shared library and extension files, restart PostgreSQL, then run:

ALTER EXTENSION pg_trickle UPDATE TO '0.89.0';

While the 0.89.0 library and 0.88.0 SQL catalog coexist, the per-database scheduler detects the version mismatch and exits without starting a refresh. The launcher starts it again after the extension upgrade completes. Manual refresh calls can fail during this short interval, so run the ALTER EXTENSION command before resuming application maintenance.

The migration adds nullable pgt_stream_tables.window_strategy metadata and the private pgt_window_states registry. It does not rewrite stream-table storage, source data, change buffers, or existing window results.

v0.89 records a versioned plan for each window query. Every function has runtime_enabled = false and uses partition recomputation. Existing catalog rows retain their data, and the upgrade creates no active private window state. Unknown strategy versions fail closed with an actionable reason.

After the upgrade, check the extension version and window-state health rows:

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';
SELECT * FROM pgtrickle.health_check()
WHERE check_name LIKE 'window_state_%';

The second query normally returns no rows because production plans create no window state.

0.87.16 → 0.87.17

This is the breaking V1-to-V2 storage transition. The migration is non-destructive: it adds a read-only preflight and private consumer inventory, but never rewrites stream-table rows, buffers, source tables, grants, or external objects in place.

1. Record every consumer that reads __pgt_row_id, depends on the current storage layout, or republishes stream-table rows. Include logical replication/publications, outbox, DuckLake, dbt, and user SQL consumers.

   SELECT pgtrickle.row_identity_v2_record_inventory(
       'Inventory ticket, owners, and approved recreation window'
   );
   -- Repeat once per consumer. Names must be schema-qualified existing STs.
   SELECT pgtrickle.row_identity_v2_register_consumer(
       'consumer-name', 'owner-name',
       ARRAY['public.stream_table'],
       true, true,
       'Change the downstream identity column to BYTEA and resnapshot'
   );
   SELECT pgtrickle.row_identity_v2_acknowledge_consumer(1, 'PENDING');
   SELECT pgtrickle.row_identity_v2_acknowledge_inventory();
   

   Acknowledgment means the owner accepts the required schema change and resnapshot. PENDING is valid before teardown; mark it COMPLETE only after the fresh V2 stream table has been resnapshotted downstream.

2. Pause scheduling, drain in-flight refreshes, and verify the read-only preflight. The global setting is used because the scheduler is a separate backend; do not rely on a session-local SET.

   ALTER SYSTEM SET pg_trickle.enabled = 'off';
   SELECT pg_reload_conf();
   SELECT pgtrickle.row_identity_v2_recreation_preflight();
   

   Do not continue while ok is false. Export definitions and metadata with pg_dump --schema-only and the dependency/definition APIs before teardown.

3. Drop V1 stream tables in reverse dependency order and clean only unused V1 change buffers. This is the only destructive step, and it affects stream state—not source data. If it fails, remove the incomplete graph and restart from the export; never partially convert a relation or replay old IDs.

4. Install the 0.87.17 shared library and extension files, restart PostgreSQL, then run:

   ALTER EXTENSION pg_trickle UPDATE;
   

5. Recreate stream tables in dependency order and perform fresh initial refreshes. Update external consumers to BYTEA; old numeric IDs cannot be cast to V2 identities. Resnapshot after each affected stream table is fresh, then record COMPLETE (or SKIPPED when the consumer was removed).

6. Resume writes only after all required resnapshots. Writes made during the recreation window are not replayed by V2. Re-enable the scheduler and rerun the preflight or inspect the consumer inventory before closing the change.

The preflight is read-only and does not disclose complete identity bytes. Diagnostics should use lengths and short fingerprints only. Pass-through and keyless identities may still contain reversible source values, so apply the same permissions and dump/log handling as for those source columns.

0.87.15 → 0.87.16

Install the 0.87.16 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

This is a breaking pre-1.0 storage release. New and rebuilt stream tables and change buffers carry the complete V2 identity as __pgt_row_id BYTEA NOT NULL and record row_identity_version = 2 plus row_probe_version = 1.

The migration deliberately marks existing identity metadata unknown and does not rewrite V1 relations in place. The V2 runtime rejects those relations before consuming or mutating state. Recreate affected stream tables and their buffers using the v0.87.17 recreation workflow after it is available; keep a backup before upgrading.

Bounded identities use a direct full-ID index. Unbounded and keyless identities use a non-unique row_probe_v1(__pgt_row_id) index and exact full-ID equality. Only bounded unique identities are used for replica identity; other tables use REPLICA IDENTITY FULL. The expression probe is never published as a column.

0.87.14 → 0.87.15

Install the 0.87.15 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

The migration installs the V2 row-identity encoder and immutable probe helper. It does not change V1 stream-table storage, CDC buffers, or DVM refresh paths. Do not mix V1 and V2 identity state. Later V2 storage releases will require dropping and recreating stream tables from unchanged source tables.

Read the V2 wire-format contract before building integration code around the encoder. The canonical bytes are opaque and may contain reversible source values.

0.87.13 → 0.87.14

Install the 0.87.14 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

v0.87.14 has no catalog or SQL API changes. The migration is intentionally empty; the release completes the DVM correctness gate with exact schema and multiset checks, live metamorphic histories, observed-path coverage, an authoritative snapshot plan, deeper shrinking, and the #953 admission fix.

0.87.12 → 0.87.13

Install the 0.87.13 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

Outbox lifecycle calls now resolve and authorize the stream table under the original caller, invoke pg_tide under that same caller, and keep private bookkeeping in the pg_trickle owner phase. The extension never lends its owner identity to pg_tide.

The supported pg_tide range is 0.47.0 through 0.53.0. The integration reports these states separately: pg_tide absent, unsupported older or newer version, supported version during an incomplete upgrade, denied operation, and authorized operation. The compatibility probes use extension catalogs and registered API identities; they do not parse error text.

pgt_outbox_config now stores the pg_tide extension OID, extension version, and outbox created_at. Existing mappings are backfilled only when the live pg_tide outbox matches exactly. If provenance cannot be proven, the upgrade aborts; install the supported pg_tide version or detach and reattach the affected outbox before retrying.

Stale, renamed, dropped, or recreated pg_tide outboxes fail closed during refresh and detach. This prevents a same-named replacement from receiving events intended for the original outbox.

0.87.11 → 0.87.12

Install the 0.87.12 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

Publication management now follows the caller's PostgreSQL privileges. The caller must own the stream table and hold CREATE on the current database. The resulting publication is owned by that caller. Publication creation and drop do not use the extension owner's authority for public DDL.

Publication bindings use the live publication OID, owner, stream relation, and relation set. Rename, replacement, ownership transfer, and relation-set drift fail closed before private catalog mutation. Public DDL and private binding updates remain atomic.

Existing publication bindings are checked during the upgrade. Repair or remove any binding that no longer identifies its live publication before retrying the upgrade. No grant on pgtrickle or pgtrickle_changes is required for a stream owner.

Publication bindings contain database-local OIDs and are not portable through pg_dump/pg_restore. Detach downstream publications before a dump and recreate them through stream_table_to_publication() after restore.

0.87.10 → 0.87.11

Install the 0.87.11 shared library and extension files, then run:

ALTER EXTENSION pg_trickle UPDATE;

Snapshot functions now use a pinned SECURITY DEFINER boundary only for private pg_trickle infrastructure. Default snapshots are transferred to their creator; caller-selected schemas require caller USAGE and CREATE. Restores require destination stream ownership and snapshot SELECT; drops require snapshot ownership or superuser. Existing snapshots keep their stored owners, and legacy rows that cannot prove provenance remain read-only.

0.87.9 → 0.87.10

Install the 0.87.10 shared library and extension files, restart PostgreSQL, then run the read-only privilege check as a superuser:

SELECT pgtrickle.lifecycle_preflight();

If the result lists missing SELECT or schema USAGE privileges, apply the exact statements in each remediation value and rerun the check. The upgrade migration performs the same check before changing any function attributes, so a failed preflight leaves the catalog unchanged.

After the result is clear, run:

ALTER EXTENSION pg_trickle UPDATE;

This release applies the owner-checked, pinned SECURITY DEFINER boundary to manual refresh, pause/resume, repair, fuse/statistics reset, refresh/storage policy, SLA, and bulk alter/drop APIs. Existing ACLs are preserved; the new lifecycle_preflight() function is admin-only. No legacy extension-owner query execution mode is introduced.

0.87.5 → 0.87.6

v0.87.6 makes no catalog or SQL API changes. Install the new extension files and run ALTER EXTENSION pg_trickle UPDATE; the migration is intentionally empty because this release adds DVM scenario shrinking, strategy and failpoint replay, an active negative-control corpus, and a machine-enforced release gate.

0.87.4 → 0.87.5

v0.87.5 makes no catalog or SQL API changes. Install the new extension files and run ALTER EXTENSION pg_trickle UPDATE; the migration is intentionally empty because this release adds DVM schema contracts, structured snapshot planning, decision tracing, and semantic coverage checks.

0.87.3 → 0.87.4

v0.87.4 makes no catalog or SQL API changes. Install the new extension files and run ALTER EXTENSION pg_trickle UPDATE; the migration is intentionally empty because this release adds state-directed and metamorphic DVM correctness tests.

0.87.2 → 0.87.3

v0.87.3 makes no catalog or SQL API changes. Install the new extension files and run ALTER EXTENSION pg_trickle UPDATE; the migration is intentionally empty because this release adds composition-aware differential correctness coverage.

0.87.1 → 0.87.2

v0.87.2 makes no catalog or SQL API changes. Install the new extension files and run ALTER EXTENSION pg_trickle UPDATE; the migration is intentionally empty because this release adds deterministic DVM replay and regression testing infrastructure.

0.86.0 → 0.87.0

v0.87.0 keeps the existing stream-table and change-buffer schema. Large ordinary differential MERGE deltas use the bounded portal pipeline; small proven non-amplifying deltas remain direct. pg_trickle.pipeline_batch_size defaults to 4096. pg_trickle.merge_batch_size is a deprecated alias for the same setting, now meaning apply-batch size rather than a materialization threshold, and is scheduled for removal in v0.88.

Pipeline batches use internal savepoints inside one outer refresh transaction. They do not become visible to other sessions until the outer transaction commits, and they do not release row locks early. Logged change-buffer durability remains the default. pg_trickle.memory_budget_mb defaults to 256 MiB and bounds pg_trickle-owned accumulations; it is not a PostgreSQL-wide RSS limit, and the lossless change-buffer guard never drops committed rows.

0.87.17 to 0.88.0

v0.88.0 removes the deprecated pg_trickle.merge_batch_size name. Set pg_trickle.pipeline_batch_size instead. Existing stream tables need no rebuild.

The upgrade invalidates cached delta templates because v0.88.0 adds planner evidence to their validity contract. Running ANALYZE can change a shadow candidate or an enabled validated rewrite, but it cannot change query results.

Quick Upgrade (Recommended)

-- 1. Check current version
SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- 2. Replace the binary files (.so/.dylib, .control, .sql)
--    See the installation method below for your platform.

-- 3. Restart PostgreSQL (required for shared library changes)
--    sudo systemctl restart postgresql

-- 4. Run the upgrade in each database that has pg_trickle installed
ALTER EXTENSION pg_trickle UPDATE;

-- 5. Verify the upgrade
SELECT pgtrickle.version();
SELECT * FROM pgtrickle.health_check();

Step-by-Step Instructions

1. Check Current Version

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';
-- Returns your current installed version, e.g. '0.9.0'

2. Install New Binary Files

Replace the extension files in your PostgreSQL installation directory. The method depends on how you originally installed pg_trickle.

From release tarball:

# Replace <new-version> with the target release, for example 0.2.3
curl -LO https://github.com/getretake/pg_trickle/releases/download/v<new-version>/pg_trickle-<new-version>-pg18-linux-amd64.tar.gz
tar xzf pg_trickle-<new-version>-pg18-linux-amd64.tar.gz

# Copy files to PostgreSQL directories
sudo cp pg_trickle-<new-version>-pg18-linux-amd64/lib/* $(pg_config --pkglibdir)/
sudo cp pg_trickle-<new-version>-pg18-linux-amd64/extension/* $(pg_config --sharedir)/extension/

From source (cargo-pgrx):

cargo pgrx install --release

3. Restart PostgreSQL

The shared library (.so / .dylib) is loaded at server start via shared_preload_libraries. A restart is required for the new binary to take effect.

sudo systemctl restart postgresql
# or on macOS with Homebrew:
brew services restart postgresql@18

4. Run ALTER EXTENSION UPDATE

Connect to each database where pg_trickle is installed and run:

ALTER EXTENSION pg_trickle UPDATE;

This executes the upgrade migration scripts in order (for example, pg_trickle--0.5.0--0.6.0.sql → pg_trickle--0.6.0--0.7.0.sql). PostgreSQL automatically determines the full upgrade chain from your current version to the new default_version.

0.82.0 → 0.83.0 row-identity rebuild

The migration adds row_identity_version to pgt_stream_tables and pgt_change_buffers, creates the durable private set-operation registry, and locks tracked source/storage relations in OID order. Pending CDC rows are discarded except for each buffer's sentinel, then the buffers are marked version 2; the migration rolls back if a registered buffer is missing. Existing stream tables are marked needs_reinit with legacy version 1.

The next protected FULL refresh rebuilds each table from source data and marks its row identity version 2. Incremental refresh remains blocked until that reinitialization succeeds. INTERSECT and EXCEPT continue to use FULL/AUTO until their private multiplicity state is admitted by the semantic gate.

0.83.0 → 0.84.0 catalog and privilege integrity

This hop repairs migration-era catalogs and installs a deny-first function ACL policy. Before upgrading, inspect roles that rely on PostgreSQL's default PUBLIC EXECUTE privilege and generate exact grants from scripts/sql_api_policy.json; do not replace them with GRANT EXECUTE ON ALL FUNCTIONS.

Run the preflight checks in every database:

SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';
SELECT pgtrickle.migrate(); -- read-only diagnostic
SELECT snapshot_id, snapshot_schema, snapshot_table
FROM pgtrickle.pgt_snapshots
WHERE snapshot_relid IS NULL OR snapshot_provenance_token IS NULL;

Unresolved legacy snapshots remain cataloged but cannot be restored or dropped until their relation identity and provenance are repaired. Invalid numeric configuration (including NaN/infinity) and ambiguous ownership are rejected; the migration never clamps or guesses values.

Install the released 0.83.0 shared library and SQL together, restart PostgreSQL, then run ALTER EXTENSION pg_trickle UPDATE before switching to the 0.84.0 package. Verify that pgtrickle.version(), pg_extension.extversion, and the latest migration audit row all report 0.84.0. Automatic logical restore reconciliation remains disabled; use a physical backup for resumable stream tables or recreate them from their definitions after restoring source data.

0.84.0 → 0.85.0 scheduler and resource resilience

Install the 0.85.0 shared library and SQL together, restart PostgreSQL to initialize the new shared-memory layout, then run:

ALTER EXTENSION pg_trickle UPDATE TO '0.85.0';

The migration adds typed refresh outcomes, worker-generation tracking, and self-healing state to the scheduler catalogs. Drain requests now remain active after completion or timeout; resume dispatch explicitly with:

SELECT pgtrickle.resume_after_drain();

Before upgrading, review parser, bulk-control, and metrics settings against the new finite limits. is_drained() returns NULL before the first drain request, and metrics remain loopback-bound unless pg_trickle.metrics_bind_address is configured explicitly.

5. Verify the Upgrade

-- Check version
SELECT pgtrickle.version();

-- Run health check
SELECT * FROM pgtrickle.health_check();

-- Verify stream tables are intact
SELECT * FROM pgtrickle.stream_tables_info;

-- Test a refresh
SELECT pgtrickle.refresh_stream_table('your_stream_table');

Version-Specific Notes

0.1.3 → 0.2.0

New functions added:

• pgtrickle.list_sources(name) — list source tables for a stream table
• pgtrickle.change_buffer_sizes() — inspect CDC change buffer sizes
• pgtrickle.health_check() — diagnostic health checks
• pgtrickle.dependency_tree() — visualize the dependency DAG
• pgtrickle.trigger_inventory() — audit CDC triggers
• pgtrickle.refresh_timeline(max_rows) — refresh history
• pgtrickle.diamond_groups() — diamond dependency group info
• pgtrickle.version() — extension version string
• pgtrickle.pgt_ivm_apply_delta(...) — internal IVM delta application
• pgtrickle.pgt_ivm_handle_truncate(...) — internal TRUNCATE handler
• pgtrickle._signal_launcher_rescan() — internal launcher signal

No schema changes to pgtrickle.pgt_stream_tables or pgtrickle.pgt_dependencies catalog tables.

No breaking changes. All v0.1.3 functions and views continue to work as before.

0.2.0 → 0.2.1

Three new catalog columns added to pgtrickle.pgt_stream_tables:

The migration script (pg_trickle--0.2.0--0.2.1.sql) adds these columns via ALTER TABLE … ADD COLUMN IF NOT EXISTS.

No breaking changes. All v0.2.0 functions, views, and event triggers continue to work as before.

What's also new:

• Upgrade migration safety infrastructure (scripts, CI, E2E tests)
• GitHub Pages book expansion (6 new documentation pages)
• User-facing upgrade guide (this document)

0.2.1 → 0.2.2

No catalog table DDL changes. The topk_offset column needed for paged TopK was already added in v0.2.1.

Two SQL function updates are applied by pg_trickle--0.2.1--0.2.2.sql:

• pgtrickle.create_stream_table(...)
  • default schedule changes from '1m' to 'calculated'
  • default refresh_mode changes from 'DIFFERENTIAL' to 'AUTO'
• pgtrickle.alter_stream_table(...)
  • adds the optional query parameter used by ALTER QUERY support

Because PostgreSQL stores argument defaults and function signatures in pg_proc, the migration script must DROP FUNCTION and recreate both signatures during ALTER EXTENSION ... UPDATE.

Behavioral notes:

• Existing stream tables keep their current catalog values. The migration only changes the defaults used by future create_stream_table(...) calls.
• Existing applications can opt a table into the new defaults explicitly via pgtrickle.alter_stream_table(...) after the upgrade.
• After installing the new binary and restarting PostgreSQL, the scheduler now warns if the shared library version and SQL-installed extension version do not match. This helps detect stale .so/.dylib files after partial upgrades.

0.2.2 → 0.2.3

One new catalog column is added to pgtrickle.pgt_stream_tables:

The upgrade script also recreates two SQL functions:

• pgtrickle.create_stream_table(...)
  • adds the optional cdc_mode parameter
• pgtrickle.alter_stream_table(...)
  • adds the optional cdc_mode parameter

Monitoring view updates:

• pgtrickle.pg_stat_stream_tables gains the cdc_modes column
• pgtrickle.pgt_cdc_status is added for per-source CDC visibility

Because PostgreSQL stores function signatures and defaults in pg_proc, the upgrade script drops and recreates both lifecycle functions during ALTER EXTENSION ... UPDATE.

0.6.0 → 0.7.0

One new catalog column is added to pgtrickle.pgt_stream_tables:

Two new catalog tables are added:

The upgrade script also updates and adds SQL functions:

• Recreates pgtrickle.pgt_status() so the result includes scc_id
• Adds pgtrickle.pgt_scc_status() for circular-dependency monitoring
• Adds pgtrickle.advance_watermark(source, watermark)
• Adds pgtrickle.create_watermark_group(name, sources[], tolerance_secs)
• Adds pgtrickle.drop_watermark_group(name)
• Adds pgtrickle.watermarks()
• Adds pgtrickle.watermark_groups()
• Adds pgtrickle.watermark_status()

Behavioral notes:

• Circular stream table dependencies can now run to convergence when pg_trickle.allow_circular = true and every member of the cycle is safe for monotone DIFFERENTIAL refresh.
• The scheduler can now hold back refreshes until related source tables are aligned within a configured watermark tolerance.
• Existing non-circular stream tables continue to work as before. The new catalog objects are additive.

0.7.0 → 0.8.0

No catalog schema changes. The upgrade migration script contains no DDL.

New operational features:

• pg_dump / pg_restore support: stream tables are now safely exported and re-connected after restore without manual intervention.
• Connection pooler opt-in was introduced at the per-stream level (superseded by the more comprehensive pooler_compatibility_mode added in v0.10.0).

No breaking changes. All v0.7.0 functions, views, and event triggers continue to work as before.

0.8.0 → 0.9.0

No catalog schema DDL changes to pgtrickle.pgt_stream_tables or the dependency catalog.

New API function added:

• pgtrickle.restore_stream_tables() — reserved for logical reconciliation. The current implementation fails closed because it cannot yet prove restored relation, ownership, CDC, and frontier identity.

Hidden auxiliary columns for AVG / STDDEV / VAR aggregates. Stream tables using these aggregates will automatically receive hidden __pgt_aux_* columns on the next refresh after upgrading. No manual action is needed — pg_trickle detects missing auxiliary columns and performs a single full reinitialise to add them.

Behavioral notes:

• COUNT, SUM, and AVG now update in constant time (O(changed rows)) instead of rescanning the whole group.
• STDDEV and VAR variants likewise update in O(changed rows) via hidden sum-of-squares auxiliary columns.
• MIN/MAX still requires a group rescan only when the deleted value is the current extreme.
• Refresh groups (create_refresh_group, drop_refresh_group, refresh_groups()) are available starting from this version.

0.9.0 → 0.10.0

Two new catalog columns added to pgtrickle.pgt_stream_tables:

One new catalog table is added:

The upgrade script also updates and adds SQL functions:

• pgtrickle.create_stream_table(...) gains the pooler_compatibility_mode parameter
• pgtrickle.create_stream_table_if_not_exists(...) likewise
• pgtrickle.create_or_replace_stream_table(...) likewise
• pgtrickle.alter_stream_table(...) likewise
• Adds pgtrickle.create_refresh_group(name, members, isolation)
• Adds pgtrickle.drop_refresh_group(name)
• Adds pgtrickle.refresh_groups() — lists all declared groups

Behavioral notes:

• pooler_compatibility_mode defaults to false. Existing stream tables are unaffected. Enable it only for stream tables accessed through PgBouncer transaction-mode pooling.
• pg_trickle.auto_backoff now defaults to on (was off). The backoff threshold is raised from 80 % → 95 % and the maximum slowdown is capped at 8× (was 64×). If you relied on the old opt-in behaviour, set pg_trickle.auto_backoff = off explicitly.
• diamond_consistency now defaults to 'atomic' for new stream tables (was 'none'). Existing stream tables keep their current setting.
• The scheduler now uses row-level locking for concurrency control instead of session-level advisory locks, making pg_trickle compatible with PgBouncer transaction-pool and similar connection poolers.
• Statistical aggregates (CORR, COVAR_*, REGR_*) now update incrementally using Welford-style accumulation, no longer requiring a group rescan.
• Materialized view sources can now be used in DIFFERENTIAL mode when pg_trickle.matview_polling = on is set.
• Recursive CTE stream tables with DELETE/UPDATE now use the Delete-and-Rederive algorithm (O(delta) instead of O(n)).

0.10.0 → 0.11.0

New catalog columns added to pgtrickle.pgt_stream_tables:

Updated function signatures — existing calls continue to work because new parameters all have defaults:

• pgtrickle.create_stream_table(...) gains partition_by TEXT DEFAULT NULL
• pgtrickle.create_stream_table_if_not_exists(...) likewise
• pgtrickle.create_or_replace_stream_table(...) likewise
• pgtrickle.alter_stream_table(...) gains fuse TEXT DEFAULT NULL, fuse_ceiling BIGINT DEFAULT NULL, fuse_sensitivity INT DEFAULT NULL

New functions:

• pgtrickle.reset_fuse(name TEXT, action TEXT DEFAULT 'apply') — clear a blown fuse and resume scheduling
• pgtrickle.fuse_status() — returns circuit-breaker state for every stream table
• pgtrickle.explain_refresh_mode(name TEXT) — shows configured mode, effective mode, and the reason for any downgrade

Behavioral notes:

• Stream-table-to-stream-table chains now refresh incrementally — downstream tables receive a small insert/delete delta rather than cascading full refreshes.
• pg_trickle.tiered_scheduling now defaults to on.
• Declaratively partitioned stream tables are supported via partition_by — the refresh MERGE is automatically restricted to only the changed partitions.

0.11.0 → 0.12.0

No schema changes. This release adds four new diagnostic SQL functions only:

Behavioral notes:

• The incremental engine now handles multi-table join deletes correctly — phantom rows after simultaneous deletes from multiple join sides no longer occur.
• Stream-table-to-stream-table row identity is now computed consistently between the change buffer and the downstream table, eliminating stale duplicate rows after upstream UPDATEs.
• pg_trickle.tiered_scheduling defaults to on (same as 0.11.0 runtime behaviour; this release makes it the explicit default).

0.12.0 → 0.13.0

Ten new catalog columns added to pgtrickle.pgt_stream_tables:

All columns use ADD COLUMN IF NOT EXISTS for idempotent upgrades.

Nine new SQL functions (plus one replacement with new signature):

New GUC variables:

Behavioral notes:

• Shared change buffers: Multiple stream tables reading from the same source now automatically share a single change buffer. No migration action required — existing per-source buffers continue to work.
• Columnar change tracking: Wide-table UPDATEs that touch only value columns (not GROUP BY / JOIN / WHERE columns) now generate significantly less delta volume. This is fully automatic.
• Auto buffer partitioning: Set pg_trickle.buffer_partitioning = 'auto' to let high-throughput buffers self-promote to partitioned mode for O(1) cleanup.
• dbt macros: If you use dbt-pgtrickle, update your macros to the matching v0.13.0 version. New config options: partition_by, fuse, fuse_ceiling, fuse_sensitivity.

No breaking changes. All v0.12.0 functions, views, and event triggers continue to work as before.

0.13.0 → 0.14.0

Two new catalog columns added to pgtrickle.pgt_stream_tables:

Updated function signature (return type gained new columns):

• pgtrickle.st_refresh_stats() — gains consecutive_errors, schedule, refresh_tier, and last_error_message columns. The upgrade script drops and recreates the function. No behavior change for existing callers that ignore unknown columns.

New SQL functions (available immediately after ALTER EXTENSION ... UPDATE):

New GUC variables:

Deprecated GUCs (still accepted but ignored at runtime):

• pg_trickle.merge_planner_hints → use pg_trickle.planner_aggressive
• pg_trickle.merge_work_mem_mb → use pg_trickle.planner_aggressive

Behavioral notes:

• Error-state circuit breaker: A single permanent refresh failure (e.g. a function that doesn't exist for the column type) now immediately sets the stream table status to ERROR with a message stored in last_error_message. The scheduler skips ERROR tables. Use pgtrickle.resume_stream_table(name) followed by pgtrickle.alter_stream_table(name, query => ...) to recover.
• Tiered scheduling NOTICE: Demoting a stream table from hot to cold or frozen now emits a NOTICE so operators are aware the effective refresh interval has changed (10× for cold, suspended for frozen).
• SECURITY DEFINER triggers: All CDC trigger functions now run with SECURITY DEFINER and an explicit SET search_path, hardening against privilege-escalation attacks. This is applied automatically on upgrade — no manual action needed.

No breaking changes. All v0.13.0 functions, views, and event triggers continue to work as before.

0.14.0 → 0.15.0

No schema changes. New features: interactive dashboard, bulk create_stream_tables_from_schema(), and per-table runaway-refresh protection (max_refresh_duration_ms).

New GUC variables:

0.15.0 → 0.16.0

No schema changes. Performance improvements to the delta pipeline and refresh path. L2 catalog-backed template cache (pgtrickle.pgt_template_cache) introduced.

0.16.0 → 0.17.0

No schema changes. Query intelligence improvements: window function differentiation, correlated-sublink rewriting.

0.17.0 → 0.18.0

No schema changes. Hardening pass: tightened unsafe blocks, improved error propagation, delta performance improvements (prepared-statement MERGE path).

0.18.0 → 0.19.0

No schema changes. Security enhancements: SECURITY DEFINER on all public-facing functions, improved RLS awareness in delta generation.

0.19.0 → 0.20.0

New catalog table: pgtrickle.pgt_self_monitoring for extension health metrics. New function: pgtrickle.metrics_summary().

0.20.0 → 0.21.0

No schema changes. Reliability improvements: advisory-lock hardening, WAL-receiver retry, graceful SIGTERM in background workers.

0.21.0 → 0.22.0

No schema changes. New features: downstream CDC pipeline, parallel refresh scheduling, predictive cost model for FULL vs DIFFERENTIAL selection.

0.22.0 → 0.23.0

No schema changes. Performance tuning and diagnostics: delta amplification detection, EXPLAIN capture (PGS_PROFILE_DELTA), adaptive threshold auto-tuning.

New GUC variables:

0.23.0 → 0.24.0

No schema changes. Join correctness hardening: phantom-row detection infrastructure, durability improvements for committed change buffers.

0.24.0 → 0.25.0

No schema changes. Scheduler scalability: worker pool, L1 template cache with LRU eviction.

New GUC variables:

0.25.0 → 0.26.0

No schema changes. Concurrency hardening: improved lock ordering, stress test suite, fixed MERGE race under high concurrency.

0.26.0 → 0.27.0

New catalog columns added to pgtrickle.pgt_stream_tables:

New catalog table: pgtrickle.pgt_template_cache for L2 cross-backend delta SQL storage.

New SQL functions:

New GUC variables:

No breaking changes.

0.27.0 → 0.28.0

New catalog tables: pgtrickle.outbox_events, pgtrickle.inbox_messages, pgtrickle.inbox_dead_letters for transactional outbox and inbox patterns.

Historical SQL functions: this release introduced pg_trickle-managed outbox/inbox functions such as enable_outbox() and enable_inbox(). Those APIs were later extracted to pg_tide in v0.46.0; current pg_trickle keeps only the integration hooks attach_outbox() and detach_outbox().

No breaking changes.

0.28.0 → 0.29.0

Added relay catalog tables and SQL functions (set_relay_outbox, set_relay_inbox, enable_relay, disable_relay, delete_relay, get_relay_config, list_relay_configs) and the standalone pgtrickle-relay binary. These were later extracted to pg_tide in v0.46.0.

No breaking changes.

0.29.0 → 0.30.0

No schema changes. All improvements are confined to the Rust extension binary. The migration file (sql/pg_trickle--0.29.0--0.30.0.sql) is empty other than documentation comments.

New GUC variables:

Behavioral changes:

• restore_from_snapshot() now returns a typed error (SnapshotSchemaVersionMismatch) when the snapshot has no __pgt_snapshot_version column (pre-v0.27 snapshots). Previously it silently treated the missing column as compatible.
• snapshot_stream_table() and restore_from_snapshot() now wrap critical operations in PostgreSQL subtransactions. A failed catalog INSERT rolls back the snapshot table creation, preventing orphan tables.
• Cross-cycle phantom rows are now cleaned up unconditionally after every differential refresh of a join query.

No breaking changes.

0.30.0 → 0.31.0

No schema changes. All improvements are confined to the Rust extension binary and scheduler logic.

New GUC variables:

Behavioral changes:

• Scheduler now uses a predictive cost model to decide DIFFERENTIAL vs. FULL refresh per cycle; the model activates after pg_trickle.prediction_min_samples samples.
• Event-driven wake now debounces duplicate NOTIFY payloads within a single tick to avoid redundant wakeups on bulk writes.

No breaking changes.

0.31.0 → 0.32.0

No schema changes. Citus stable naming infrastructure is added without altering the public catalog schema.

Behavioral changes:

• pgtrickle.source_stable_name(rel_oid) introduced as a deterministic, version-stable WAL slot name for Citus distributed sources.
• Per-source last_frontier column added to pgtrickle.pgt_stream_tables via ADD COLUMN IF NOT EXISTS — existing rows receive NULL.

No breaking changes.

0.32.0 → 0.33.0

Schema additions — new catalog tables for Citus distributed CDC:

New SQL functions:

• pgtrickle.ensure_worker_slot(st_name, worker_host, worker_port) — creates the WAL slot on a Citus worker if it does not exist.
• pgtrickle.poll_worker_slot_changes(st_name, worker_host, worker_port) — drains pending WAL changes from a worker slot into the coordinator change buffer.
• pgtrickle.handle_vp_promoted(payload TEXT) — processes a pg_ripple.vp_promoted NOTIFY payload and signals the scheduler.
• pgtrickle.check_citus_version_compat() — verifies that all worker nodes run the same pg_trickle version.
• pgtrickle.check_worker_wal_level() — verifies that wal_level = logical on every worker.

New create_stream_table() parameter:

• output_distribution_column TEXT — when provided (and Citus is installed), converts the output storage table to a Citus distributed table on that column immediately after creation.

New GUC:

No application-level breaking changes. Existing stream tables on non-Citus deployments are completely unaffected.

0.33.0 → 0.34.0

Schema additions — the pgtrickle.citus_status view gains five new columns:

Behavioral changes:

• The scheduler now drives the full per-worker slot lifecycle automatically for stream tables with source_placement = 'distributed': ensure_worker_slot() on first tick (and after topology changes), poll_worker_slot_changes() on every tick, and pgt_st_locks lease acquire/extend/release. Manual wiring via LISTEN "pg_ripple.vp_promoted" + handle_vp_promoted() is no longer required (though harmless if left in place).
• Shard rebalance auto-recovery: the scheduler detects pg_dist_node topology changes, prunes stale pgt_worker_slots rows, inserts new ones, and marks the stream table for a full refresh — no operator intervention required.
• Worker failure isolation: per-worker poll_worker_slot_changes() failures are caught, logged, and skipped for that tick; healthy workers continue uninterrupted.

New GUC:

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.34.0';

The migration script adds the five new columns to citus_status via CREATE OR REPLACE VIEW. No data loss.

No breaking changes. Non-Citus deployments are completely unaffected.

0.34.0 → 0.35.0

New catalog columns added to pgtrickle.pgt_stream_tables:

New catalog table:

New SQL functions:

Behavioral notes:

• Zero-downtime schema evolution (ALTER STREAM TABLE) now builds a shadow table in the background and cuts over atomically. The in_shadow_build column tracks progress; check pgtrickle.view_evolution_status() to monitor.
• pgtrickle.sla_summary() queries pgt_refresh_history using the pg_trickle.sla_window_hours GUC (default 24 h).
• Reactive subscriptions emit pg_notify(channel, '') after each non-empty refresh cycle. Debounce interval is controlled by pg_trickle.notify_coalesce_ms.

New GUCs:

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.35.0';

No breaking changes. All v0.34.0 functions continue to work.

0.35.0 → 0.36.0

New catalog columns added to pgtrickle.pgt_stream_tables:

New SQL functions:

Updated function signatures:

• pgtrickle.create_stream_table(...) gains temporal BOOLEAN DEFAULT FALSE and storage_backend TEXT DEFAULT 'heap' parameters.

Behavioral notes:

• Temporal IVM (CORR-1): Stream tables created with temporal := true maintain SCD Type 2 history. Each row carries __pgt_valid_from TIMESTAMPTZ and __pgt_valid_to TIMESTAMPTZ. Existing tables are unaffected.
• Alternative storage backends: storage_backend = 'citus' creates the stream table storage as a Citus distributed table. Requires the Citus extension to be installed.
• Drain mode (A35): pgtrickle.drain() is a safety mechanism for maintenance windows. The scheduler completes all in-flight refreshes, then stops dispatching new ones until the drain is cancelled or the server restarts.
• WAL slot backpressure (A12): The pg_trickle.enforce_backpressure GUC is now wired — when slot lag exceeds slot_lag_critical_threshold_mb, CDC writes are suppressed. See CONFIGURATION.md for details and the discard semantics of cdc_paused.

New GUCs:

TRUNCATE and CDC semantics: When pg_trickle.cdc_paused = on, CDC trigger bodies return NULL — changes are discarded. This is the discard mode. After un-pausing, stream tables must be reinitialized (FULL refresh) to recover from the gap. A future cdc_capture_mode = 'hold' option is planned.

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.36.0';

No breaking changes. The column_lineage column is additive. The temporal_mode and storage_backend columns have safe defaults.

0.36.0 → 0.37.0

Schema change: All existing change buffer tables in pgtrickle_changes.* gain a __pgt_trace_context TEXT column via dynamic ALTER TABLE. This is applied automatically by the upgrade script.

Behavioral notes:

• W3C Trace Context (F10): When pg_trickle.enable_trace_propagation = true, CDC triggers capture the session pg_trickle.trace_id GUC into the __pgt_trace_context column. At refresh time, the stored trace context is propagated to any OTLP span exported to pg_trickle.otel_endpoint.
• pgVectorMV (F4): avg(vector_col) and sum(vector_col) in defining queries are now handled incrementally when pg_trickle.enable_vector_agg = true. Requires pgvector ≥ 0.7.0.

New GUCs:

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.37.0';

The upgrade script applies ALTER TABLE ... ADD COLUMN IF NOT EXISTS __pgt_trace_context TEXT to each existing change buffer table. This is idempotent and safe for large installations. Expect a brief metadata lock on each buffer table during the upgrade.

No breaking changes. The __pgt_trace_context column is NULL unless enable_trace_propagation = true and a session trace_id is set.

0.37.0 → 0.38.0

No schema changes. This is a correctness and diagnostic release.

Behavioral notes:

• EC-01 correctness closeout: Join phantom row elimination is complete. Property tests now prove convergence across join patterns including three-way joins with simultaneous multi-side deletes.
• Fuzz regression fixes: All known fuzz corpus failures are resolved.

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.38.0';

No breaking changes.

0.38.0 → 0.39.0

New SQL function:

Extended SQL function:

• pgtrickle.explain_stream_table(name TEXT) — now includes CDC status, backpressure state, and explicit DIFF/FULL reasoning.

New GUC:

Behavioral notes:

• O39-6 SQLSTATE-first retry: When use_sqlstate_classification = true (default), scheduler retry decisions use the bracketed SQLSTATE code from PgTrickleError::SpiErrorCode instead of English error message text, making retry behavior locale-independent.
• O39-8 CDC capture mode: The cdc_paused discard semantics are now explicitly documented and operator-visible via pgtrickle.cdc_pause_status(). The cdc_capture_mode GUC is reserved for a future hold mode.

TRUNCATE/CDC semantics (explicit):

When pg_trickle.cdc_paused = on:

• cdc_capture_mode = 'discard' (default): All DML against source tables passes through the CDC trigger, but the trigger returns NULL immediately without writing to the change buffer. Changes are permanently lost. After un-pausing, run a FULL refresh on any stream table that received DML during the pause:

  SELECT pgtrickle.refresh_stream_table('my_stream', 'FULL');
  

• cdc_capture_mode = 'hold': Not yet implemented. Emits a WARNING and falls back to 'discard'.

When TRUNCATE occurs on a source table:

• In trigger-based CDC mode, the TRUNCATE trigger calls pgtrickle.pgt_ivm_handle_truncate() which schedules a FULL refresh. If cdc_paused = on, the trigger returns NULL and the TRUNCATE is not recorded. After un-pausing, the stream table will not be aware that a TRUNCATE occurred — reinitialize explicitly.
• In WAL-based CDC mode, TRUNCATEs are detected during the next logical decoding poll and scheduled for FULL refresh.

Migration note:

ALTER EXTENSION pg_trickle UPDATE TO '0.39.0';

No catalog schema changes. The upgrade script is a no-op DDL-wise.

0.39.0 → 0.40.0

No SQL schema changes. This release contains internal improvements only.

ALTER EXTENSION pg_trickle UPDATE TO '0.40.0';

0.40.0 → 0.50.0

No SQL schema changes. This release contains internal improvements and new documentation only.

ALTER EXTENSION pg_trickle UPDATE TO '0.50.0';

0.50.0 → 0.51.0

Breaking changes:

• pg_trickle.event_driven_wake GUC has been removed (CQ-10-02). Remove any ALTER SYSTEM SET pg_trickle.event_driven_wake ... or postgresql.conf entries for this GUC before upgrading.
• pg_trickle.wake_debounce_ms GUC has been removed (CQ-10-02). Remove any references to this GUC from your configuration.

No SQL schema changes. Only code and documentation changes.

Migration:

-- Remove obsolete GUC settings before upgrading (run as superuser):
ALTER SYSTEM RESET pg_trickle.event_driven_wake;
ALTER SYSTEM RESET pg_trickle.wake_debounce_ms;
SELECT pg_reload_conf();

-- Then upgrade:
ALTER EXTENSION pg_trickle UPDATE TO '0.51.0';

Supported Upgrade Paths

The following migration hops are available. PostgreSQL chains them automatically when you run ALTER EXTENSION pg_trickle UPDATE.

Any installation from 0.1.3 onward can be upgraded to 0.51.0 in a single ALTER EXTENSION pg_trickle UPDATE — PostgreSQL chains the hops automatically after the new binaries are installed and the server has been restarted.

Rollback / Downgrade

PostgreSQL does not support automatic extension downgrades. To roll back:

1. Export stream table definitions (if you want to recreate them later):

cargo run --bin pg_trickle_dump -- --output backup.sql

Or, if the binary is already installed in your PATH:

pg_trickle_dump --output backup.sql

Use --dsn '<connection string>' or standard PG* / DATABASE_URL environment variables when the default local connection parameters are not sufficient.

2. Drop the extension (destroys all stream tables):

   DROP EXTENSION pg_trickle CASCADE;
   

3. Install the old version and restart PostgreSQL.

4. Recreate the extension at the old version:

   CREATE EXTENSION pg_trickle VERSION '0.1.3';
   

5. Recreate stream tables from your backup.

Troubleshooting

"function pgtrickle.xxx does not exist" after upgrade

This means the upgrade script is missing a function. Workaround:

-- Check what version PostgreSQL thinks is installed
SELECT extversion FROM pg_extension WHERE extname = 'pg_trickle';

-- If the version looks correct but functions are missing,
-- the upgrade script may be incomplete. Try a clean reinstall:
DROP EXTENSION pg_trickle CASCADE;
CREATE EXTENSION pg_trickle CASCADE;
-- Warning: this destroys all stream tables!

Report this as a bug — upgrade scripts should never silently drop functions.

"could not access file pg_trickle" after restart

The new shared library file was not installed correctly. Verify:

ls -la $(pg_config --pkglibdir)/pg_trickle*

ALTER EXTENSION UPDATE says "already at version X"

The binary files are already the new version but the SQL catalog wasn't upgraded. This usually means the .control file's default_version matches your current version. Check:

cat $(pg_config --sharedir)/extension/pg_trickle.control

Multi-Database Environments

ALTER EXTENSION UPDATE must be run in each database where pg_trickle is installed. A common pattern:

for db in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')"); do
  psql -d "$db" -c "ALTER EXTENSION pg_trickle UPDATE;" 2>/dev/null || true
done

CloudNativePG (CNPG)

For CNPG deployments, see cnpg/README.md for upgrade instructions specific to the Kubernetes operator.

Upgrading to v0.23.0

New GUCs

Behavioral Changes

DI-2 aggregate UPDATE-split: The DIFF output row format for aggregate stream tables changes from UPDATE rows to DELETE+INSERT pairs. This is the algebraically correct form that enables O(Δ) performance for multi-join queries.

Impact: Application code that reads the change buffer or outbox and checks op = 'UPDATE' will silently produce incorrect results.

Migration path:

1. Set pg_trickle.diff_output_format = 'merged' before upgrading
2. Migrate application code to handle DELETE+INSERT pairs
3. Switch to pg_trickle.diff_output_format = 'split' (default)

Rollback Strategy

The DI-2/DI-6 code paths are gated by detecting UPDATE rows in the change buffer. Downgrading to v0.22.0 is safe if no writes have occurred to upgraded stream tables.

Pre-Upgrade Validation

# Verify version files are in sync
just check-version-sync

New SQL Functions

• pgtrickle.explain_diff_sql(name TEXT) — Returns the delta SQL template for a stream table (for inspection/EXPLAIN)
• pgtrickle.pgtrickle_refresh_stats() — Per-stream-table timing stats with avg/p95/p99 percentiles