The subscription reset that did nothing

A counter that has never counted anything

There is a category of bug that only shows up in the one thing nobody tests, which is the recovery procedure. This is one of those.

PostgreSQL 15 introduced a view reporting how many times a subscription’s apply worker had failed and how many times its initial table synchronisation had, along with a function to clear those counters and a column recording when they were last cleared. The counters are genuinely useful: a subscription that is erroring repeatedly looks healthy in every other view, because the worker restarts and the slot keeps its position, so the error count is the signal that something is looping.

The clearing function is what runbooks use. Before a change, clear the counters; after the change, any non-zero value belongs to the change. On 15 that procedure has a hole in it, and the hole is invisible from the call site.

PostgreSQL 16 closed it. The one-line entry under Monitoring in the PostgreSQL 16 release notes says that statistics entries are now created when the subscription is created, and that this makes the reset timestamp accurate. Both halves of that sentence are doing work.

A subscription with nowhere to connect

The behaviour does not need a working publisher, which is convenient, because it means it can be demonstrated on a single server. A subscription created without connecting is a real subscription with a real statistics row.

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=127.0.0.1 port=1 dbname=absent'
  PUBLICATION orders_pub
  WITH (connect = false);

CREATE SUBSCRIPTION

Both servers warn that nothing is connected, which is expected and not the point. What matters is that both now have a subscription, and both can be asked about it.

The same three statements on each version

Read the counters, clear them, and read them again. This is the runbook, compressed.

SELECT subname, apply_error_count, sync_error_count, stats_reset FROM pg_stat_subscription_stats;
SELECT pg_stat_reset_subscription_stats((SELECT oid FROM pg_subscription WHERE subname = 'orders_sub'));
SELECT subname, apply_error_count, stats_reset IS NOT NULL AS reset_was_recorded
FROM pg_stat_subscription_stats;

  subname   | apply_error_count | sync_error_count | stats_reset 
------------+-------------------+------------------+-------------
 orders_sub |                 0 |                0 | 
(1 row)

 pg_stat_reset_subscription_stats 
----------------------------------
 
(1 row)

  subname   | apply_error_count | reset_was_recorded 
------------+-------------------+--------------------
 orders_sub |                 0 | f
(1 row)

The function returned successfully. It raised nothing, warned about nothing, and the timestamp that exists to record the reset is still empty afterwards. There is no entry for this subscription in the statistics system yet, because on 15 an entry is only created when the subscription first reports something, and a reset of an entry that does not exist is a no-op that reports success.

The same three statements on 16:

SELECT subname, apply_error_count, sync_error_count, stats_reset FROM pg_stat_subscription_stats;
SELECT pg_stat_reset_subscription_stats((SELECT oid FROM pg_subscription WHERE subname = 'orders_sub'));
SELECT subname, apply_error_count, stats_reset IS NOT NULL AS reset_was_recorded
FROM pg_stat_subscription_stats;

  subname   | apply_error_count | sync_error_count | stats_reset 
------------+-------------------+------------------+-------------
 orders_sub |                 0 |                0 | 
(1 row)

 pg_stat_reset_subscription_stats 
----------------------------------
 
(1 row)

  subname   | apply_error_count | reset_was_recorded 
------------+-------------------+--------------------
 orders_sub |                 0 | t
(1 row)

The reset is recorded, because the entry was there from the moment the subscription was created.

Why the visible counters look the same either way

Both versions report zero for the error counts before and after, which is the reason this went unnoticed for a release. The view is not reading a stored row directly; it is built from the subscription catalog, and a subscription with no statistics entry yields zeros rather than no row. From the outside, “no errors have ever been recorded” and “no entry exists” are the same picture.

The timestamp is the only column that distinguishes them, and on 15 it is empty in both cases. So the question a runbook actually wants answered, which is “is this zero a fresh zero or an old one”, has no answer on 15 until the subscription has failed at least once. A subscription that has never failed cannot have its counters meaningfully cleared, which is a sentence that sounds harmless until you notice it means the check is useless precisely on the subscriptions that are working.

What to change in a procedure

If any of your fleet is on 15, the reset step in a replication runbook cannot be trusted to have happened. Three adjustments are worth making, and none of them requires the upgrade.

• Record the wall-clock time of the reset in your own notes or in the change record, rather than relying on the column to hold it. This is what the column is for on 16 and it is what you have to do by hand on 15.
• After clearing, read the timestamp back and treat an empty value as a signal rather than as a pass. On 15 it tells you the entry did not exist; on 16 an empty value after a reset would be a genuine surprise.
• Do not compare error counts across a version upgrade. The counters are cluster state and they do not survive the move, so the first reading on the new cluster is a fresh zero regardless.

The wider habit this argues for is treating any statistics reset as an event worth recording somewhere the database does not control. Resets are the one operation that destroys monitoring history rather than adding to it, and a reset that half-worked is worse than one that did not run at all, because the counters that remain look like a measurement of the window you think you established. The same class of mistake appears in a different place on the next major, where a reset function acquired an argument-free form that clears considerably more than most callers expect.

One further consequence is worth drawing out, because it affects the upgrade itself. A fleet that runs 15 and 16 side by side has two clusters that respond differently to the same automation, and the difference is invisible from the caller. Configuration management that applies the same replication runbook everywhere will produce a fleet where some clusters have an accurate reset history and some have none, with nothing marking which is which. Reading the timestamp back is what turns that into a fact you can act on rather than a difference you never see.

The general shape of a reset that lies

It is worth pulling back from this one function, because the pattern it exhibits is not unique to it and recognising the pattern is worth more than remembering the fix.

A reset is the only statistics operation that destroys information rather than adding to it, and it is the only one whose success cannot be confirmed by looking at the thing it acted on. A counter that reads zero after a reset reads zero whether the reset worked, whether the reset did nothing, or whether the counter was already zero. The only witness is a timestamp, and on the version this page is about that witness was not being written.

That makes the reset timestamp the column to read after any reset, everywhere, not only here. Several statistics surfaces carry one, they are all written by the same kind of code path, and reading one back costs a single query. A procedure that resets and then confirms is barely longer than a procedure that resets and hopes.

The second habit is narrower and more useful: record the reset somewhere the database does not own. A change record, a deployment log, an annotation on a dashboard. Every statistics comparison a team makes is implicitly of the form “since the last reset”, and if the only record of when that was lives in a column that a restart or a failover can clear, then the comparison has no stated starting point at all.

The third is about alerting. An error counter that has just been reset is indistinguishable from a healthy one for as long as it takes the next failure to arrive, which means an alert on these counters is quietly disarmed by the very procedure that is supposed to make it meaningful. If a change window includes a reset, the alert that covers the window has to be a rate over a short period rather than a threshold on a total, or it will not fire when it matters most.

Cleaning up a subscription that cannot connect

Worth including because it is the step people get stuck on. A subscription created with a slot name it never created still tries to drop that slot on the publisher, and the publisher is not there.

ALTER SUBSCRIPTION orders_sub DISABLE;
ALTER SUBSCRIPTION orders_sub SET (slot_name = NONE);
DROP SUBSCRIPTION orders_sub;

ALTER SUBSCRIPTION
ALTER SUBSCRIPTION
DROP SUBSCRIPTION

Disassociating the slot before dropping is the sequence that works, and it is worth knowing before an incident rather than during one. The same sequence is what you need when a publisher has genuinely been decommissioned and its subscribers are left holding references to it.

What it costs

Nothing, on either side. The entry created at subscription time is small, the counters are maintained whether or not anybody reads them, and the view assembles on request. There is no setting, no extension and no restart involved in any of it.

The cost that is real is the one already spent: a 15 cluster has runbooks that contain a step which quietly does not do what it says. That is not fixed by upgrading the runbook, because the step is correct; it is fixed by upgrading the server, or by adding the read-back above so the failure stops being silent. On a fleet that spans both versions the read-back is worth adding anyway, because a procedure that behaves differently depending on which cluster it runs against is a procedure nobody can reason about.