Spock Node Monitoring: Design of the spock_monitor subsystem

Date: 2026-09-23 Status: Implemented, regression tested Component: src/spock_monitor.c, include/spock_monitor.h PostgreSQL: 15 to 19

A Word version of this document is kept next to it as spock-monitor-design.docx.

1. Summary

Operators of a Spock cluster have had to read the PostgreSQL server log to answer routine questions: is every subscription replicating, did a worker restart overnight, why was a subscription disabled, how much WAL is a slot holding back, did the extension get upgraded. The catalog and the existing statistics views describe configuration and cumulative counters but not state changes or failures.

The monitoring subsystem records state changes as events and keeps per-subscription activity counters in shared memory, and exposes them together with worker, slot and replication progress through eleven views in the spock schema. A query against spock.node_status or spock.subscription_status gives the complete picture of a node; spock.events gives the recent history. The module is also the single home of all monitoring C code, absorbing the channel counters, the apply group progress function and the slot confirmation wait.

2. Goals and non-goals

Goals

• Replace log reading for day to day operation: every notable state change is queryable.
• One row per node, per subscription, per worker and per slot, with column names that need no legend.
• Cheap on the apply hot path: a spinlock and a few integer adds per message, no catalog writes, no WAL.
• Safe in error paths: recording an error must never allocate memory, block or raise.
• Same source on every supported PostgreSQL version; version specific data such as the PG18 conflict counters degrade to NULL.
• One module for all monitoring code, with PostgreSQL naming and layout conventions.

Non-goals

• Durability across a postmaster restart. Events and counters live in shared memory only; the catalog and pgstat remain the durable stores.
• Transactional semantics. An event is recorded when the reporting code runs, whether or not its transaction commits.
• Cluster wide aggregation. Each node reports about itself; a fleet view is left to external tooling that reads the views on every node.

3. Architecture

The subsystem has three layers: shared memory structures written by spock processes, C functions that read them and return rows, and SQL views that join those rows with the catalogs and with core views such as pg_stat_replication and pg_stat_activity.

Producers never format SQL or touch the catalog. They call one of a handful of functions declared in spock_monitor.h, which copy fixed size data into shared memory under a lock. Readers take a consistent copy of each entry and build tuples outside any spinlock.

4. Shared memory

4.1 Event ring

Events are stored in a fixed size ring inside one ShmemInitStruct allocation named spock monitor. The capacity is the spock.event_history_size GUC (default 1024, PGC_POSTMASTER).

typedef struct SpockMonitorEvent
{
    uint64      event_id;      /* increases by one, never reused */
    TimestampTz event_time;
    SpockMonitorEventType event_type;
    Oid         dboid;         /* database of the reporting process */
    Oid         subid;         /* subscription, or InvalidOid */
    int         pid;
    int         sqlerrcode;    /* SQLSTATE of an error event, else 0 */
    XLogRecPtr  lsn;           /* origin LSN, if any */
    char        detail[512];
} SpockMonitorEvent;

The header holds the LWLock, the time shared memory was initialized (reported as spock_started_at), the next event id, and head and count indexes. Appending when full advances the head, so the oldest event is dropped. Event ids keep increasing across resets, which lets a poller detect both new events and gaps. The ring is cluster wide; the views filter on the current database and keep events without a database, such as those of the supervisor.

Each event type has a fixed name and severity in a table indexed by the enum, so adding a type means adding one enum member and one table row. Severities are info, warning and error; warning covers state changes an operator would want to know about even when they were requested, such as a subscription being disabled, repair mode being switched on or a transaction being skipped.

4.2 Per-subscription counters

Counters live in a fixed size shared hash keyed by (dboid, subid), sized at max(64, 4 * max_worker_processes) entries. The multiple leaves room for disabled subscriptions whose history should survive while they are down.

typedef struct SpockMonitorStatsEntry
{
    SpockMonitorStatsKey key;
    int64       counter[SPOCK_MONITOR_NUM_COUNTERS];
    TimestampTz counter_time[SPOCK_MONITOR_NUM_COUNTERS];
    XLogRecPtr  last_error_lsn;
    int         last_error_sqlerrcode;
    int         last_error_pid;
    char        last_error_message[512];
    TimestampTz stats_reset;
    slock_t     mutex;
} SpockMonitorStatsEntry;

Every counter carries the time of its last increment. That one rule gives the view its last_worker_start, last_provider_connect, last_xact_applied and last_apply_error columns without separate fields, and keeps the reader generic: two small tables in spock_get_apply_stats() map counters to count columns and to timestamp columns, checked at compile time against the column enum.

A worker caches a pointer to its entry in MySpockMonitorStats when it attaches. Entries in a HASH_FIXED_SIZE shared hash are never moved, so the pointer stays valid for the life of the process. Counting is then a spinlock, an add and a timestamp. Backends that only record events do not touch the hash.

Subscription ids come from a cycling sequence, so an id can be reused. Each entry counts the workers attached to it. Dropping a subscription removes the entry at once when no worker is attached; otherwise the entry is only marked dropped and the last worker to detach removes it, so a worker never writes through a pointer into a freed element. A worker that attaches to an entry marked dropped, because the id was reused, resets it.

4.3 Locking

• One LWLock in the tranche spock_monitor protects the ring and the hash structure. Individual entries have a spinlock for their counters.
• The monitor lock is a leaf: no other lock is taken while holding it. It may be acquired while SpockCtx->lock is held, never the other way round.
• The error reporting path uses LWLockConditionalAcquire and skips the event if the lock is busy, so error reporting can never block.
• Readers hold the monitor lock only while copying the ring or the entries into local memory, so that error reporting, which only tries the lock, is not held off while a result is formatted.

5. Instrumentation points

Each producer adds one call at the place where the state change is already logged, so the event and the log line always agree.

Worker start and stop are recorded in the generic attach and detach code, so the manager, every apply worker and every sync worker are covered by two call sites. The detach hook runs before SpockCtx->lock is taken, which keeps the lock order simple.

6. Error capture

Rather than instrumenting each error site, the module hooks the existing emit_log_hook installed by spock.c. spock_monitor_report_error() is called for every report and applies these rules:

1. Ignore unless the process is a spock worker (MySpockWorker is set) or a walsender running the spock output plugin, and shared memory is attached.
2. Ignore levels below ERROR and at or above PANIC; a PANIC path must not take locks.
3. Treat connection class errors (connection failure, admin shutdown, cannot connect now and the related codes) as a lost peer, never as an apply error. In a worker the hook records them as provider_disconnected with the message kept as the last error, which covers failures at connect or START_REPLICATION time that no handler catches. apply_work() uses the same predicate for its connection-loss branch and reports through the same function before rethrowing; it flags the rethrow so the hook does not count it again. In a walsender such an error is the subscriber going away and nothing is recorded.
4. In a walsender, record a stream_error event with the slot name, the SQLSTATE and the message. The output plugin marks the process at startup and clears the mark at shutdown. Only a walsender is marked: a regular backend decoding a slot through the SQL functions gets no shutdown call when decoding fails, and its later errors must not be reported as stream errors.
5. For apply and sync workers, bump apply_errors or sync_errors and the matching class counter (deadlock, lock timeout, constraint violation, resource error), and store the message, SQLSTATE, pid and the current origin LSN as the last error.
6. Record a worker_error event with the message and SQLSTATE, using a conditional lock acquire.

The message stored is the error message followed by the DETAIL and CONTEXT lines, so a constraint violation carries the failing row and the apply context. The function is guarded against re-entry and does no allocation: the text is formatted with snprintf into fixed buffers.

An ERROR caught by a PG_CATCH is never emitted: PostgreSQL longjmps before EmitErrorReport(), and the apply worker's handler copies and flushes the error. The hook therefore only sees errors that end the worker. The two paths of apply_work() that swallow an error, the first exception that enters replay and the sub_disable path, call spock_monitor_report_error() on the copied ErrorData themselves; the paths that rethrow leave it to the hook so that an error is counted once. Under sub_disable the replay ends with a synthetic error requesting the disable; the worker remembers that the transaction's first error was already reported and does not report that one. Row level errors during a discard replay are not counted again, so apply_errors counts failed transactions rather than failed rows.

7. SQL surface

7.1 Functions

Functions return raw shared memory rows and are the tooling interface. Set returning functions use InitMaterializedSRF and OUT parameters; the C code checks the column count against its enum with an assertion.

7.2 Views

Views are named by what they show: *_status for state, *_stats for counters. Columns use full words, never abbreviations, and the same word for the same thing everywhere (subscription_name, provider_node, worker_pid, replication_lag_bytes).

The status text of a subscription is computed in SQL with the same rule as spock.sub_show_status(): replicating, initializing or unknown while a worker runs, otherwise disabled or down. Computing it in the view keeps node_status usable before a local node exists, where the function would raise an error.

The slot view uses only columns present in pg_replication_slots since PostgreSQL 15, because one SQL script serves every version. It falls back to pg_last_wal_replay_lsn() on a standby so it never raises during recovery.

7.3 The report

spock.node_report() is a plpgsql function that calls spock.report_section() once per view. The formatter runs the query twice: once through row_to_json to learn the column names in query order, then with every column cast to text so that values appear as PostgreSQL prints them rather than as JSON. Each section's query runs inside its own exception block, so a failing view produces one error line instead of aborting the report. Passwords never appear: the views already redact connection strings, and spock.settings redacts the DSN parameters.

The exception handler keeps one shared slot per subscription with the commit LSN of the transaction in flight and the first error raised for it. spock.get_pending_exceptions() reports only slots with an error, because the LSN alone is set for every transaction.

8. Compatibility and upgrade

• The same SQL section is appended to spock--6.0.0.sql and to spock--5.0.13--6.0.0.sql. Every catalog column the views reference exists since 5.0.6, so an upgraded node ends with the same objects as a fresh install.
• spock.get_apply_worker_status() is removed; nothing in the repository used it. spock.get_worker_status() and spock.worker_status replace it.
• spock_monitoring.c is removed. spock.wait_slot_confirm_lsn(), spock.get_channel_stats(), spock.reset_channel_stats() and spock.apply_group_progress() keep their SQL definitions and C symbols and now live in spock_monitor.c.
• spock.reset_subscription_stats() keeps its SQL definition and C symbol, so the existing upgrade script needs no change, but it now resets both counter sets and no longer raises before PostgreSQL 18.
• Conflict counters are fetched through the pgstat entry under PG_VERSION_NUM >= 180000 and are NULL otherwise.
• Shared memory grows by the ring plus the hash. SpockMonitorStatsEntry is 800 bytes, dominated by its 512-byte last_error_message and the two per-counter arrays, so the hash costs roughly that plus dynahash overhead for each subscription tracked, bounded by max(64, 4 * max_worker_processes) entries. SpockMonitorEvent is 560 bytes, dominated by its 512-byte detail, so the ring costs spock.event_history_size * 560 bytes: 560 kB at the default 1024, and a megabyte or more if the setting is raised. Size shared memory from that figure rather than from the event count alone.

9. Testing

The regression test tests/regress/sql/monitor.sql runs after basic and checks, on the provider, node_status, worker_status, slot_status and the provider side events, and on the subscriber, subscription_status, worker_status, table_sync_status, subscription_stats after real traffic, the events of subscription creation and worker start, both reset functions, and that disabling and enabling a subscription produces the expected events and a worker restart that is visible in the counters. Values that vary between runs are compared, not printed. The full suite of 41 tests passes on PostgreSQL 18.

10. Limitations and future work

• The views and the functions they call (get_system_info(), get_events(), get_apply_stats(), get_pending_exceptions(), replication_origin_status(), which stands in for the superuser-only pg_replication_origin_status) are granted to pg_monitor; a superuser or a member of that role reads them. wal_size_bytes() stays callable by any role, as the total WAL size is not sensitive. The reset functions are superuser only.
• The subscription stats hash is bounded at max(64, 4 * max_worker_processes) entries. Past that, spock_monitor_attach_stats() returns NULL and logs it; the worker carries on and the subscription operates normally, but its counters are not recorded: spock.subscription_stats still has its row, with every counter read as 0 and every timestamp as NULL, so a subscription past the bound looks idle rather than unrecorded. spock.node_status.channel_stats_full reports the same condition for the channel counter hash.
• The ring is cluster wide, so a database with heavy activity can age out the events of a quiet one. A per-database ring would remove this at the cost of more shared memory.
• Events and counters are lost at postmaster restart. If history across restarts is wanted, a background worker could periodically append events to a spock catalog table.
• pg_replication_slots columns added in PostgreSQL 17 (failover, invalidation_reason, inactive_since) are not shown; a version conditional view would be needed.
• Replication set changes on the provider (table added or removed) are not events; they are visible in the catalog and could be added with two call sites.