Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts

On Mon, Aug 31, 2026 at 12:08 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> On Mon, 31 Aug 2026 at 17:46, Melanie Plageman
> <melanieplageman(at)gmail(dot)com> wrote:
>
> > As for whether we should log full page images of the VM and FSM in
> > RelationCopyStorageUsingBuffer(), that kind of seems like the right
> > direction to go, but that doesn't feel backpatchable to me. I
> > understand this doesn't address Matthias' concern about certain AMs,
> > but it seems like a good idea to have the VM and FSM up-to-date after
> > promotion and FPIs of just those forks doesn't seem like an
> > unacceptable increase in WAL volume. I don't know if doing this will
> > have any side effects or causes bugs in other situations, though. I'd
> > have to think more about it...
>
> IIUC, CREATE DATABASE doesn't allow concurrent R/RW connections to the
> template database, for neither WAL_LOG nor FILE_COPY. In this case
> there shouldn't be any issues with copying VM pages that aren't
> already present in the file-copy approach. The only difference here
> would be that VM/FSM pages now get a more recent LSN on their pages,
> and I think this should be fine because I can't think of a mechanism
> that checks for (the consistency of) VM/FSM page LSNs.

I now think we have to do something. While exploring another adjacent
topic, I realized that tuple locking has a seemingly unsolvable
problem with this.

If you have a page set all-frozen in the VM and then do CREATE
DATABASE STRATEGY WAL_LOG, your new database will have that page set
all-frozen in the VM on the primary and set neither all-visible nor
all-frozen on the standby. When that standby is promoted, you now have
a primary with the page NOT set all-frozen and a standby with the page
set all-frozen. If you then lock the tuple on the primary, it will
emit a WAL record with no XLH_LOCK_ALL_FROZEN_CLEARED flag because the
primary did not need to clear all-frozen. The standby will update the
xmax but not clear the all-frozen bit in the VM. Now you have a heap
page with a live xmax that is set all-frozen in the VM.

Basically, if you have some all-frozen data and do CREATE DATABASE
STRATEGY WAL_LOG and then failover and then do a SELECT FOR UPDATE,
you'll have data corruption. That seems bad.

I don't see any reasonable way to fix this other than to prevent it
from happening. Because we are only clearing the all-frozen bit,
including a VM clearing operation in every lock record in which the
page is all-visible, means that as soon as a page is all-visible every
single time we lock it the WAL record will have to include the VM page
-- which is very expensive.

This should fix that particular way to get into this situation
if (use_wal)
log_newpage_buffer(dstBuf, forkNum != VISIBILITYMAP_FORKNUM &&
forkNum != FSM_FORKNUM);

But if there are other ways to get into this situation, I'm not quite
sure what we could do. I asked an LLM to come up with scenarios and
outside of RelationCopyStorageUsingBuffer(). It couldn't identify
other specific scenarios that could put us in this bad state. It
mentioned some things with incremental backups from before ed62d26caca
or bad basebackup/crash recovery/PITR scenarios, but I'd need to do
more investigation. After a cursory investigation, the possibility for
those to create this scenario and not other problems seems pretty
remote. Fixing RelationCopyStorageUsingBuffer() seems required at this
point, though.

- Melanie