Upgrading pg_trickle

This guide covers upgrading pg_trickle from one version to another.

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.

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() — re-installs CDC triggers and re-registers stream tables after a pg_restore from a pg_dump.

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.

New SQL functions: pgtrickle.enable_outbox(name, ...), pgtrickle.enable_inbox(name, ...), and related management functions.

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; 'pg_mooncake' uses columnar storage. Both require the respective extensions 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