Re: Offline data checksum changes can cause incorrect checksum state on standbys

On 04/09/2026 02:08, Daniel Gustafsson wrote:
>> On 3 Sep 2026, at 13:54, Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>> On Thu, Sep 03, 2026 at 12:06:42PM +0100, Zsolt Parragi wrote:
>
>>> v12 addresses these, otherwise it is unchanged to compared 11.
>>
>> Thanks! v12 LGTM.
>
> Thanks for review. I've attached a v13 where I've moved most of the new tests
> under PG_TEST_EXTRA to keep test times down. I placed most tests under
> 'checksum' and 18, 21 and 23 under 'checksum_extended', but the exact split may
> be tweaked further. Since the origin of this open item is missing test
> coverage, I prefer to add all these tests even though they aren't executed
> during normal testruns. There are at least one BF animal running the full
> suite which ensures timely execution of the tests.
>
> This concludes the only open item left (thus far). Being able to error
> standbys out of mismatched clusters would be nice, and is a potential
> development area for 20, but it's not a showstopper if we never add it IMHO.
>
> My current plan is to commit this to master only either tomorrow or Monday
> after staring at it a little bit more, to a) give it exposure in the buildfarm
> before an eventual backpatching; b) allow time for the revert discussion. If
> we decide to revert I prefer to avoid more v19 churn.

Thanks, I started to review this now. I'm still at patch 0001, haven't
looked at the rest yet, but some quick comments on that one:

> diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml
> index ec62d17fbcc..dbbfdae5e4f 100644
> --- a/doc/src/sgml/wal.sgml
> +++ b/doc/src/sgml/wal.sgml
> @@ -317,6 +317,29 @@
> verify checksums, on an offline cluster.
> </para>
>
> + <para>
> + An offline change provides durability differently from an
> + <link linkend="checksums-online-enable-disable">online change</link>.
> + An online transition is WAL-logged: it is ordered against all other
> + WAL records, it is replayed after a crash, and it propagates to
> + standbys. An offline change is recorded only in the cluster's
> + control file: it writes no WAL, it is invisible to replication, and
> + it has no defined ordering against WAL the node has not replayed
> + yet. When a node later replays WAL that contains an online checksum
> + state change, that change takes effect on the node even if it was
> + written before the offline change was made.
> + </para>
> +
> + <para>
> + An offline change only affects the data directory it is run on; the
> + new state does not propagate over replication. In a replication setup
> + the same change must be applied to all nodes while all of them are
> + stopped, as described in <xref linkend="app-pgchecksums"/>. A standby
> + whose state diverges logs a warning but keeps its local setting. The
> + mismatch persists until the states are brought together again, with
> + the offline procedure or with an online transition; do this promptly.
> + </para>
> +
> </sect2>
>
> <sect2 id="checksums-online-enable-disable" xreflabel="Online Enabling of Checksums">

Let's add a new 'sect2' for this explanation, and move it after the
"Online Enabling of Checksums" section. It's currently placed under
"Offline Enabling of Checksums", but it actually goes into a lot of
details of how *online* checksumming works, but "Online Enabling of
Checksums" is covered in the following paragraph. If you read this in
order like a novel, it feels weird.

I think these paragraphs could use some copy-editing too. It feels like
a pretty deep technical explanation, not very accessible to a DBA. Maybe
start with "The primary server and replica can have different checksum
states".

(Not new with this patch, but: )

The placement of the states in the state diagram on that page looks
bizarre. I know it's auto-generated so not sure there's much we can do
about it.. but could we, please? Maybe it'd get more clear if you leave
'initdb' out of the diagram. Or consider some completely different
representation.

I'm still trying to understand all the different states and interactions
between online and offline changes. It's really complicated :-(. I know
it's a tall order, but is there something we could do to make it
simpler? Would it help if there was a separate flag in the control file
for "checksums enabled in primary" and "checksums enabled in this
replica", for example?

- Heikki