pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
30+ messages / 10 participants
[nested] [flat]

Commits from this thread:
  2026-09-14 3245795e13 d69b06e5ab  Handle no-op visibility map set during redo
* WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
@ 2024-05-13 12:31 Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 30+ messages in thread

From: Matthias van de Meent @ 2024-05-13 12:31 UTC (permalink / raw)
  To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>

Hi,

My collegue Konstantin Knizhnik pointed out that we fail to mark pages
with a non-standard page layout with page_std=false in
RelationCopyStorageUsingBuffer when we WAL-log them. This causes us to
interpret the registered buffer as a standard buffer, and omit the
hole in the page, which for FSM/VM pages covers the whole page.

The immediate effect of this bug is that replicas and primaries in a
physical replication system won't have the same data in their VM- and
FSM-forks until the first VACUUM on the new database has WAL-logged
these pages again. Whilst not actively harmful for the VM/FSM
subsystems, it's definitely suboptimal.
Secondary unwanted effects are that AMs that use the buffercache- but
which don't use or update the pageheader- also won't see the main data
logged in WAL, thus potentially losing user data in the physical
replication stream or with a system crash. I've not looked for any
such AMs and am unaware of any that would have this issue, but it's
better to fix this.


PFA a patch that fixes this issue, by assuming that all pages in the
source database utilize a non-standard page layout.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)

Attachments:

v1-0001-Fix-logging-of-non-standard-pages-in-RelationCopy.patch (application/octet-stream, 1.4K) download From ca3e957b7684c5e67ff8868f6d53cbcf9e4e4d1f Mon Sep 17 00:00:00 2001 From: Matthias van de Meent <boekewurm+postgres@gmail.com> Date: Mon, 13 May 2024 13:48:04 +0200 Subject: [PATCH v1] Fix logging of non-standard pages in RelationCopyStorageUsingBuffer If the data in the page is non-standard (e.g. the VM, FSM, or an access method that doesn't update PageHeader's fields as required) then we may fail to WAL-log the data on that page. When the server crashes after flushing the WAL record, but before flushing the file writes, this data may be permanently lost. Reported-By: Konstantin Knizhnik --- src/backend/storage/buffer/bufmgr.c | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 49637284f9..6a3b9357db 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -4695,9 +4695,13 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, memcpy(dstPage, srcPage, BLCKSZ); MarkBufferDirty(dstBuf); - /* WAL-log the copied page. */ + /* + * WAL-log the copied page. + * Note that we don't know about the type of data contained in the + * page, so we can't report that the buffer is a standard page. + */ if (use_wal) - log_newpage_buffer(dstBuf, true); + log_newpage_buffer(dstBuf, false); END_CRIT_SECTION(); -- 2.40.1
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
@ 2024-05-13 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Tom Lane @ 2024-05-13 14:13 UTC (permalink / raw)
  To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>

Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> PFA a patch that fixes this issue, by assuming that all pages in the
> source database utilize a non-standard page layout.

Surely that cure is worse than the disease?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
@ 2024-05-13 14:52   ` Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Matthias van de Meent @ 2024-05-13 14:52 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>; Robert Haas <robertmhaas@gmail.com>

On Mon, 13 May 2024 at 16:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Matthias van de Meent <boekewurm+postgres@gmail.com> writes:
> > PFA a patch that fixes this issue, by assuming that all pages in the
> > source database utilize a non-standard page layout.
>
> Surely that cure is worse than the disease?

I don't know where we would get the information whether the selected
relation fork's pages are standard-compliant. We could base it off of
the fork number (that info is available locally) but that doesn't
guarantee much.
For VM and FSM-pages we know they're essentially never
standard-compliant (hence this thread), but for the main fork it is
anyone's guess once the user has installed an additional AM - which we
don't detect nor pass through to the offending
RelationCopyStorageUsingBuffer.

As for "worse", the default template database is still much smaller
than the working set of most databases. This will indeed regress the
workload a bit, but only by the fraction of holes in the page + all
FSM/VM data.
I think the additional WAL volume during CREATE DATABASE is worth it
when the alternative is losing that data with physical
replication/secondary instances. Note that this does not disable page
compression, it just stops the logging of holes in pages; holes which
generally are only a fraction of the whole database.

It's not inconceivable that this will significantly increase WAL
volume, but I think we should go for correctness rather than fastest
copy. If we went with fastest copy, we'd better just skip logging the
FSM and VM forks because we're already ignoring the data of the pages,
so why not ignore the pages themselves, too? I don't think that holds
water when we want to be crash-proof in CREATE DATABASE, with a full
data copy of the template database.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
@ 2024-05-13 19:43     ` Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Robert Haas @ 2024-05-13 19:43 UTC (permalink / raw)
  To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

On Mon, May 13, 2024 at 10:53 AM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
> It's not inconceivable that this will significantly increase WAL
> volume, but I think we should go for correctness rather than fastest
> copy.

I don't think we can afford to just do this blindly for the sake of a
hypothetical non-core AM that uses nonstandard pages. There must be
lots of cases where the holes are large, and where the WAL volume
would be a multiple of what it is currently. That's a *big*
regression.

> If we went with fastest copy, we'd better just skip logging the
> FSM and VM forks because we're already ignoring the data of the pages,
> so why not ignore the pages themselves, too? I don't think that holds
> water when we want to be crash-proof in CREATE DATABASE, with a full
> data copy of the template database.

This seems like a red herring. Either assuming standard pages is a
good idea or it isn't, and either logging the FSM and VM forks is a
good idea or it isn't, but those are two separate questions.

-- 
Robert Haas
EDB: http://www.enterprisedb.com





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
@ 2024-09-14 18:57       ` Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Akshat Jaimini @ 2024-09-14 18:57 UTC (permalink / raw)
  To: pgsql-hackers@lists.postgresql.org; +Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>

Hi,

Quick question, are there any more revisions left to be done on this patch from the previous feedback?
Or should I continue with reviewing the current patch?

Regards,
Akshat Jaimini

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
@ 2024-12-10 06:25         ` Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2024-12-10 06:25 UTC (permalink / raw)
  To: Akshat Jaimini <destrex271@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>

On Sat, Sep 14, 2024 at 06:57:21PM +0000, Akshat Jaimini wrote:
> Quick question, are there any more revisions left to be done on this
> patch from the previous feedback? 

This patch is still listed in the CF app waiting on author with what
looks like Robert and Tom objecting to it, because it makes all
callers of CreateAndCopyRelationData() much more expensive in terms of
WAL generated when copying these files as the holes of the pages would
be included.  This would also make the compression of the FPWs more
expensive.

One potential way to move forward would be to spread the knowledge of
page_std when logging a new page higher in the callers, then allow
dbcommands.c to be smarter about that?
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
@ 2026-02-02 15:26           ` Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Álvaro Herrera @ 2026-02-02 15:26 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Akshat Jaimini <destrex271@gmail.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>

On 2024-Dec-10, Michael Paquier wrote:

> One potential way to move forward would be to spread the knowledge of
> page_std when logging a new page higher in the callers, then allow
> dbcommands.c to be smarter about that?

Matthias, is this patch still under consideration?

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Update: super-fast reaction on the Postgres bugs mailing list. The report
was acknowledged [...], and a fix is under discussion.
The wonders of open-source !"
             https://twitter.com/gunnarmorling/status/1596080409259003906





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
@ 2026-08-23 17:04             ` Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  0 siblings, 1 reply; 30+ messages in thread

From: Rogers Wang @ 2026-08-23 17:04 UTC (permalink / raw)
  To: pgsql-hackers@lists.postgresql.org; +Cc: Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Michael Paquier <michael@paquier.xyz>

Hi,

Reviving this thread: the bug reported here in 2024 is still present
on master, and has become more harmful since.

To recap the root cause: RelationCopyStorageUsingBuffer() copies every
fork page by page and WAL-logs each copied page with
log_newpage_buffer(buf, page_std = true).  page_std=true punches the
[pd_lower, pd_upper) range out of the full-page image as a hole, to
save WAL.  That works for heap and index pages, but VM and FSM pages
never maintain pd_lower/pd_upper - they keep whatever PageInit() left
(pd_lower = SizeOfPageHeaderData, pd_upper = BLCKSZ).  So for those
forks the "hole" covers the whole content area, the FPI carries
nothing but the header, and replay zero-fills it.  The primary lands
the real VM/FSM contents on disk, while the standby replays all-zero
pages.  As far as I can see, this call site is the only one that logs
VM/FSM pages in standard mode - everywhere else they are logged with
page_std=false.

What changed since the last discussion
--------------------------------------
The divergence was assessed back then as "not actively harmful". That
no longer holds in v19: commit add323da40a added
Assert(BufferIsDirty(vmbuffer)) to heap_xlog_prune_freeze(), which
makes the divergence fatal on assert builds after a switchover:

1. The promoted standby has a zeroed VM, so VACUUM FREEZE sets the
    bits again, emitting PRUNE records with no FPI for the VM buffer.
2. The old primary, rejoined as standby, still has the bits set with
    an older page LSN, so replay takes the redo branch - but
    visibilitymap_set() is a no-op there (bits already set) and the
    buffer stays clean.
3. Assert(BufferIsDirty(vmbuffer)) fires, and the node crash-loops on
    that record.

And even without assertions, the new database on the standby silently
loses all all-visible/all-frozen bits and all free space data of every
copied relation.

Repro
-----
With a streaming standby attached:

     -- on the primary
     CREATE DATABASE src_db;
     \c src_db
     CREATE EXTENSION pageinspect;
     CREATE TABLE t(a int);
     INSERT INTO t SELECT generate_series(1, 10000);
     VACUUM FREEZE t;
     -- back in postgres
     CREATE DATABASE dst_db TEMPLATE src_db STRATEGY WAL_LOG;

     -- then on both nodes, in dst_db:
     SELECT encode(get_raw_page('t', 'vm', 0), 'hex');
     SELECT encode(get_raw_page('t', 'fsm', 0), 'hex');
     -- primary shows real content, standby is all zeros

For the assert crash: run with full_page_writes=off so the PRUNE
record carries no FPI for the VM page, promote the standby, rejoin the
old primary as its standby, then VACUUM (FREEZE, DISABLE_PAGE_SKIPPING)
t on the new primary.  The old primary dies on the assert while
replaying the PRUNE records.

0002 adds a TAP test to src/test/recovery/t/034_create_database.pl
which automates the scenario above: all new checks fail without 0001
and pass with it (verified on REL_19_STABLE with --enable-cassert).

Fix
---
The v1 patch logged all copied pages with page_std=false, which was
objected to because of the WAL volume increase on the main fork: holes
would no longer be punched from heap/index pages.  Attached v2 instead
makes the decision per fork, along the lines Michael suggested. The
VM and FSM forks are the only ones whose pages never maintain a
standard page layout, so only their pages are logged whole:

-        /* WAL-log the copied page. */
+        /*
+         * WAL-log the copied page.  VM/FSM pages never maintain
+         * pd_lower/pd_upper, so hole punching would omit their entire
+         * content from the FPI.
+         */
          if (use_wal)
-            log_newpage_buffer(dstBuf, true);
+            log_newpage_buffer(dstBuf,
+                               forkNum != VISIBILITYMAP_FORKNUM &&
+                               forkNum != FSM_FORKNUM);

Main and init forks keep hole punching, so their WAL volume is
unchanged; the extra WAL is limited to the VM/FSM forks, which are
small.  The fork number is available locally in
RelationCopyStorageUsingBuffer() (it already branches on it for
use_wal), so no plumbing through dbcommands.c is needed.

The bug goes back to v15, where STRATEGY WAL_LOG was added
(9c08aea6a30); the assertion crash additionally requires v19
(add323da40a), so older branches see only the VM/FSM divergence.
This should be backpatched to all supported versions.

Regards,
Rogers Wang

Attachments: download all (2 files, .tar.gz)

0001-Fix-hole-punching-of-VM-FSM-pages-in-CREATE-DATABASE.patch (text/plain, 754B) download diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index 169829eb020..6cee2e21954 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -5454,9 +5454,15 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, memcpy(dstPage, srcPage, BLCKSZ); MarkBufferDirty(dstBuf); - /* WAL-log the copied page. */ + /* + * WAL-log the copied page. VM/FSM pages never maintain + * pd_lower/pd_upper, so hole punching would omit their entire + * content from the FPI. + */ if (use_wal) - log_newpage_buffer(dstBuf, true); + log_newpage_buffer(dstBuf, + forkNum != VISIBILITYMAP_FORKNUM && + forkNum != FSM_FORKNUM); END_CRIT_SECTION();
0002-Add-test-for-VM-FSM-divergence-after-CREATE-DATABASE.patch (text/plain, 6.3K) download diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile index 9c4102b6b2c..dfd3617c84c 100644 --- a/src/test/recovery/Makefile +++ b/src/test/recovery/Makefile @@ -9,7 +9,8 @@ # #------------------------------------------------------------------------- -EXTRA_INSTALL=contrib/pg_prewarm \ +EXTRA_INSTALL=contrib/pageinspect \ + contrib/pg_prewarm \ contrib/pg_stat_statements \ contrib/test_decoding \ src/test/modules/injection_points \ diff --git a/src/test/recovery/t/034_create_database.pl b/src/test/recovery/t/034_create_database.pl index 194d1837e23..bff6c65a32b 100644 --- a/src/test/recovery/t/034_create_database.pl +++ b/src/test/recovery/t/034_create_database.pl @@ -42,4 +42,133 @@ is($result, "0", "check that there are no tables from template on new database after crash" ); +# Check that the VM and FSM contents of relations copied with the +# WAL_LOG strategy are preserved on a standby. +# +# The copy WAL-logs each page via log_newpage_buffer(). VM and FSM +# pages never maintain pd_lower/pd_upper after PageInit(), so logging +# them with page_std=true would omit their whole content area from the +# FPI and replay would zero-fill it. When the promoted standby later +# vacuums the table, replaying the VM-setting PRUNE records on the old +# primary (where the bits are already set) would be a no-op that leaves +# the VM buffer clean, tripping Assert(BufferIsDirty(vmbuffer)). + +# full_page_writes=off guarantees that the VM-setting record generated +# below carries no FPI; the large checkpoint_timeout prevents WAL writes +# while the old primary is demoted, so no rewind is needed then. +my $node_primary = PostgreSQL::Test::Cluster->new('primary'); +$node_primary->init(allows_streaming => 1); +$node_primary->append_conf( + 'postgresql.conf', qq{ +full_page_writes = off +autovacuum = off +checkpoint_timeout = 1h +}); +$node_primary->start; + +# Create a source database with a table whose VM bits are all set +# (all-visible and all-frozen) and whose FSM is filled. +$node_primary->safe_psql('postgres', "CREATE DATABASE src_db"); +$node_primary->safe_psql( + 'src_db', qq{ +CREATE EXTENSION pageinspect; +CREATE TABLE test (a int); +INSERT INTO test SELECT generate_series(1, 10000); +VACUUM FREEZE test; +}); + +# Check that the VM page has real content beyond the 24-byte page +# header, so that the comparison below is meaningful. +my $vm_hex = $node_primary->safe_psql('src_db', + "SELECT encode(get_raw_page('test', 'vm', 0), 'hex')"); +like(substr($vm_hex, 48), + qr/[^0]/, 'VM bits are set on src_db before CREATE DATABASE'); + +# Flush and take a base backup; the backup copies the VM/FSM files +# verbatim. +$node_primary->safe_psql('postgres', "CHECKPOINT"); +my $backup_name = 'my_backup'; +$node_primary->backup($backup_name); +my $node_standby = PostgreSQL::Test::Cluster->new('standby'); +$node_standby->init_from_backup($node_primary, $backup_name, + has_streaming => 1); +$node_standby->start; + +# Create dst_db from src_db: the primary lands the copied forks on +# disk, while the standby replays the FPIs. +$node_primary->safe_psql('postgres', + "CREATE DATABASE dst_db TEMPLATE src_db STRATEGY WAL_LOG"); +$node_primary->wait_for_catchup($node_standby); + +# Compare the VM and FSM pages with pageinspect. +my $p_vm = $node_primary->safe_psql('dst_db', + "SELECT encode(get_raw_page('test', 'vm', 0), 'hex')"); +my $s_vm = $node_standby->safe_psql('dst_db', + "SELECT encode(get_raw_page('test', 'vm', 0), 'hex')"); +is($s_vm, $p_vm, 'new database: VM page identical on both nodes'); + +my $p_fsm = $node_primary->safe_psql('dst_db', + "SELECT encode(get_raw_page('test', 'fsm', 0), 'hex')"); +my $s_fsm = $node_standby->safe_psql('dst_db', + "SELECT encode(get_raw_page('test', 'fsm', 0), 'hex')"); +is($s_fsm, $p_fsm, 'new database: FSM page identical on both nodes'); + +# Switch over and check that the old primary survives replaying the +# PRUNE records generated by vacuuming the table on the new primary. + +# Move the redo point past the CREATE DATABASE records, so that crash +# recovery of the old primary does not replay the CREATE DATABASE FPIs, +# and wait until the standby has replayed the checkpoint. +$node_primary->safe_psql('postgres', "CHECKPOINT"); +$node_primary->wait_for_catchup($node_standby); + +# Promote the standby, then demote the old primary to a standby of the +# new primary: its WAL ends exactly at the promotion fork point, so no +# rewind is needed. The slot preserves the WAL generated below. +$node_standby->promote; + +$node_primary->stop('immediate'); +$node_standby->safe_psql('postgres', + "SELECT pg_create_physical_replication_slot('old_primary')"); +my $new_primary_connstr = $node_standby->connstr; +$node_primary->append_conf( + 'postgresql.conf', qq{ +primary_conninfo = '$new_primary_connstr application_name=old_primary' +primary_slot_name = 'old_primary' +}); +$node_primary->set_standby_mode; +$node_primary->start; + +# Wait until the new standby is fully caught up. +$node_standby->wait_for_catchup($node_primary); + +# Note the log offset now: the crash can follow the VACUUM within +# milliseconds. +my $log_offset = -s $node_primary->logfile; + +# If the VM bitmap is all zero on the new primary, vacuum sets the bits +# again, generating PRUNE records without an FPI for the VM buffer. +$node_standby->safe_psql('dst_db', + "VACUUM (FREEZE, DISABLE_PAGE_SKIPPING) test"); + +# Replay of the PRUNE records on the new standby would then be a no-op +# that leaves the VM buffer clean and crashes on +# Assert(BufferIsDirty(vmbuffer)), in which case the standby never +# replays past the target LSN and this poll times out. The crash +# follows the VACUUM within milliseconds, so a short timeout suffices. +my $target_lsn = $node_standby->lsn('flush'); +my $saved_timeout = $PostgreSQL::Test::Utils::timeout_default; +$PostgreSQL::Test::Utils::timeout_default = 15; +my $caught_up = $node_standby->poll_query_until( + 'postgres', + "SELECT '$target_lsn' <= replay_lsn AND state = 'streaming' + FROM pg_catalog.pg_stat_replication + WHERE application_name = 'old_primary'"); +$PostgreSQL::Test::Utils::timeout_default = $saved_timeout; +ok($caught_up, 'new standby catches up after replaying PRUNE records'); +ok(!$node_primary->log_contains(qr/TRAP:.*BufferIsDirty/, $log_offset), + 'no assertion crash during replay'); + +$node_primary->stop; + done_testing();
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
@ 2026-08-31 05:37               ` Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Michael Paquier @ 2026-08-31 05:37 UTC (permalink / raw)
  To: Rogers Wang <rogers.ww@qq.com>; +Cc: pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; melanieplageman@gmail.com

On Mon, Aug 24, 2026 at 01:04:44AM +0800, Rogers Wang wrote:
> What changed since the last discussion
> --------------------------------------
> The divergence was assessed back then as "not actively harmful". That
> no longer holds in v19: commit add323da40a added
> Assert(BufferIsDirty(vmbuffer)) to heap_xlog_prune_freeze(), which
> makes the divergence fatal on assert builds after a switchover:
> 
> 1. The promoted standby has a zeroed VM, so VACUUM FREEZE sets the
>    bits again, emitting PRUNE records with no FPI for the VM buffer.
> 2. The old primary, rejoined as standby, still has the bits set with
>    an older page LSN, so replay takes the redo branch - but
>    visibilitymap_set() is a no-op there (bits already set) and the
>    buffer stays clean.
> 3. Assert(BufferIsDirty(vmbuffer)) fires, and the node crash-loops on
>    that record.
> 
> And even without assertions, the new database on the standby silently
> loses all all-visible/all-frozen bits and all free space data of every
> copied relation.

Hmm.  This warrants an open item for v19, at least to be discussed.
Adding Melanie in CC as the committer of add323da40a.
--
Michael

Attachments:

  signature.asc (application/pgp-signature, 832B) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
@ 2026-08-31 15:46                 ` Melanie Plageman <melanieplageman@gmail.com>
  2026-08-31 16:08                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2026-09-11 20:44                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  0 siblings, 3 replies; 30+ messages in thread

From: Melanie Plageman @ 2026-08-31 15:46 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Mon, Aug 31, 2026 at 1:37 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> On Mon, Aug 24, 2026 at 01:04:44AM +0800, Rogers Wang wrote:
> > What changed since the last discussion
> > --------------------------------------
> > The divergence was assessed back then as "not actively harmful". That
> > no longer holds in v19: commit add323da40a added
> > Assert(BufferIsDirty(vmbuffer)) to heap_xlog_prune_freeze(), which
> > makes the divergence fatal on assert builds after a switchover:
> >
> > 1. The promoted standby has a zeroed VM, so VACUUM FREEZE sets the
> >    bits again, emitting PRUNE records with no FPI for the VM buffer.
> > 2. The old primary, rejoined as standby, still has the bits set with
> >    an older page LSN, so replay takes the redo branch - but
> >    visibilitymap_set() is a no-op there (bits already set) and the
> >    buffer stays clean.
> > 3. Assert(BufferIsDirty(vmbuffer)) fires, and the node crash-loops on
> >    that record.
> >
> > And even without assertions, the new database on the standby silently
> > loses all all-visible/all-frozen bits and all free space data of every
> > copied relation.
>
> Hmm.  This warrants an open item for v19, at least to be discussed.
> Adding Melanie in CC as the committer of add323da40a.

This part

> > And even without assertions, the new database on the standby silently
> > loses all all-visible/all-frozen bits and all free space data of every
> > copied relation.

is true completely independently of my commit. Only the assert is new
and would require changes to v19. I didn't think of this way of ending
up with a discrepancy between primary and standby VM. I assumed that
if the VM bit was not set on the primary it would not be set on the
standby. Even if we commit something to eliminate this corner case
(with RelationCopyStorageUsingBuffer()), you could probably end up
with a lost or truncated VM on the primary and the VM being set on the
standby in some other way, so we shouldn't assume that the
visibilitymap_set() will dirty the buffer. We can't set the page LSN
unless we marked the buffer dirty, so for 19, I think I could just do
if (BufferIsDirty()) -> PageSetLSN()
In master, it probably makes sense to change back the signature of
visibilitymap_set() to report if it did actually set the bit(s) and
then use that to decide whether to set the page LSN. Unless we don't
think it is too late in 19 to change the visibilitymap_set() API. It
does mean all the other callers of visibilitymap_set() are going to
discard the return value, but maybe that's okay?

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...

- Melanie





^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-08-31 16:08                   ` Matthias van de Meent <boekewurm+postgres@gmail.com>
  2026-09-16 14:26                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2 siblings, 1 reply; 30+ messages in thread

From: Matthias van de Meent @ 2026-08-31 16:08 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Mon, 31 Aug 2026 at 17:46, Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> On Mon, Aug 31, 2026 at 1:37 AM Michael Paquier <michael@paquier.xyz> wrote:
> >
> > Hmm.  This warrants an open item for v19, at least to be discussed.
> > Adding Melanie in CC as the committer of add323da40a.
>
> This part

I think a message got cut off.

> 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.


Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-08-31 16:08                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
@ 2026-09-16 14:26                     ` Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 18:32                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-09-16 14:26 UTC (permalink / raw)
  To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>; Robert Haas <robertmhaas@gmail.com>

On Mon, Aug 31, 2026 at 12:08 PM Matthias van de Meent
<boekewurm+postgres@gmail.com> wrote:
>
> On Mon, 31 Aug 2026 at 17:46, Melanie Plageman
> <melanieplageman@gmail.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






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-08-31 16:08                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2026-09-16 14:26                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-09-16 18:32                       ` Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Melanie Plageman @ 2026-09-16 18:32 UTC (permalink / raw)
  To: Matthias van de Meent <boekewurm+postgres@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>; Robert Haas <robertmhaas@gmail.com>

On Wed, Sep 16, 2026 at 10:26 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> 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 attached an LLM-generated reproducer.

- Melanie

Attachments:

  repro-noop-vm-frozen-lock-portable.sh (application/x-shellscript, 9.0K) download

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-09-11 20:44                   ` Melanie Plageman <melanieplageman@gmail.com>
  2026-09-14 15:49                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-09-11 20:44 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Mon, Aug 31, 2026 at 11:46 AM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> This part
>
> > > And even without assertions, the new database on the standby silently
> > > loses all all-visible/all-frozen bits and all free space data of every
> > > copied relation.
>
> is true completely independently of my commit. Only the assert is new
> and would require changes to v19. I didn't think of this way of ending
> up with a discrepancy between primary and standby VM. I assumed that
> if the VM bit was not set on the primary it would not be set on the
> standby. Even if we commit something to eliminate this corner case
> (with RelationCopyStorageUsingBuffer()), you could probably end up
> with a lost or truncated VM on the primary and the VM being set on the
> standby in some other way, so we shouldn't assume that the
> visibilitymap_set() will dirty the buffer. We can't set the page LSN
> unless we marked the buffer dirty, so for 19, I think I could just do
> if (BufferIsDirty()) -> PageSetLSN()
> In master, it probably makes sense to change back the signature of
> visibilitymap_set() to report if it did actually set the bit(s) and
> then use that to decide whether to set the page LSN. Unless we don't
> think it is too late in 19 to change the visibilitymap_set() API. It
> does mean all the other callers of visibilitymap_set() are going to
> discard the return value, but maybe that's okay?

Since we are putting out a beta 4, I feel fine about changing the
visiblitymap_set() API back. Here is a patch that does that.

- Melanie

Attachments:

v1-0001-Handle-no-op-visibility-map-setting-during-redo.patch (text/x-patch, 6.9K) download From 4781d34b85a55fca467ebe6ae46d11f790be5bb8 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Fri, 11 Sep 2026 16:33:56 -0400 Subject: [PATCH v1] Handle no-op visibility map setting during redo add323da40a consolidated visibility map updates into XLOG_HEAP2_PRUNE_FREEZE records. It assumed that a record containing a visibility map update would always modify the VM page during replay. That is not true when the requested bits are already set on the standby. Primary and standby visibility maps can diverge for several reasons. This issue was exposed by a CREATE DATABASE ... STRATEGY WAL_LOG bug. After a standby with an out-of-date VM is promoted, VACUUM may set bits that were already present on the former primary. When the former primary later replays that record as a standby, visibilitymap_set() is a no-op. Restore visibilitymap_set()'s former API, which returns the state of the VM bits before setting the requested flags. Use that result to set the VM page LSN only when the operation actually modifies the page. --- src/backend/access/heap/heapam.c | 10 +++++----- src/backend/access/heap/heapam_xlog.c | 18 ++++++------------ src/backend/access/heap/pruneheap.c | 5 +++-- src/backend/access/heap/vacuumlazy.c | 16 ++++++++-------- src/backend/access/heap/visibilitymap.c | 6 +++++- src/include/access/visibilitymap.h | 6 +++--- 6 files changed, 30 insertions(+), 31 deletions(-) diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 10766d330a9..b9bf47fd798 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -2484,11 +2484,11 @@ heap_multi_insert(Relation relation, TupleTableSlot **slots, int ntuples, { PageSetAllVisible(page); PageClearPrunable(page); - visibilitymap_set(BufferGetBlockNumber(buffer), - vmbuffer, - VISIBILITYMAP_ALL_VISIBLE | - VISIBILITYMAP_ALL_FROZEN, - relation->rd_locator); + (void) visibilitymap_set(BufferGetBlockNumber(buffer), + vmbuffer, + VISIBILITYMAP_ALL_VISIBLE | + VISIBILITYMAP_ALL_FROZEN, + relation->rd_locator); } /* diff --git a/src/backend/access/heap/heapam_xlog.c b/src/backend/access/heap/heapam_xlog.c index 7a7bc7ea740..c66b30f4c58 100644 --- a/src/backend/access/heap/heapam_xlog.c +++ b/src/backend/access/heap/heapam_xlog.c @@ -283,10 +283,8 @@ heap_xlog_prune_freeze(XLogReaderState *record) if (PageIsNew(vmpage)) PageInit(vmpage, BLCKSZ, 0); - visibilitymap_set(blkno, vmbuffer, vmflags, rlocator); - - Assert(BufferIsDirty(vmbuffer)); - PageSetLSN(vmpage, lsn); + if (visibilitymap_set(blkno, vmbuffer, vmflags, rlocator) != vmflags) + PageSetLSN(vmpage, lsn); } if (BufferIsValid(vmbuffer)) @@ -692,19 +690,15 @@ heap_xlog_multi_insert(XLogReaderState *record) &vmbuffer) == BLK_NEEDS_REDO) { Page vmpage = BufferGetPage(vmbuffer); + uint8 vmflags = VISIBILITYMAP_ALL_VISIBLE | + VISIBILITYMAP_ALL_FROZEN; /* initialize the page if it was read as zeros */ if (PageIsNew(vmpage)) PageInit(vmpage, BLCKSZ, 0); - visibilitymap_set(blkno, - vmbuffer, - VISIBILITYMAP_ALL_VISIBLE | - VISIBILITYMAP_ALL_FROZEN, - rlocator); - - Assert(BufferIsDirty(vmbuffer)); - PageSetLSN(vmpage, lsn); + if (visibilitymap_set(blkno, vmbuffer, vmflags, rlocator) != vmflags) + PageSetLSN(vmpage, lsn); } if (BufferIsValid(vmbuffer)) diff --git a/src/backend/access/heap/pruneheap.c b/src/backend/access/heap/pruneheap.c index 29f4722b02d..6f6253d7f66 100644 --- a/src/backend/access/heap/pruneheap.c +++ b/src/backend/access/heap/pruneheap.c @@ -1310,8 +1310,9 @@ heap_page_prune_and_freeze(PruneFreezeParams *params, */ PageSetAllVisible(prstate.page); PageClearPrunable(prstate.page); - visibilitymap_set(prstate.block, prstate.vmbuffer, prstate.new_vmbits, - prstate.relation->rd_locator); + (void) visibilitymap_set(prstate.block, prstate.vmbuffer, + prstate.new_vmbits, + prstate.relation->rd_locator); } MarkBufferDirty(prstate.buffer); diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c index 063ef2208de..8e1f660bc2f 100644 --- a/src/backend/access/heap/vacuumlazy.c +++ b/src/backend/access/heap/vacuumlazy.c @@ -1964,11 +1964,11 @@ lazy_scan_new_or_empty(LVRelState *vacrel, Buffer buf, BlockNumber blkno, PageSetAllVisible(page); PageClearPrunable(page); - visibilitymap_set(blkno, - vmbuffer, - VISIBILITYMAP_ALL_VISIBLE | - VISIBILITYMAP_ALL_FROZEN, - vacrel->rel->rd_locator); + (void) visibilitymap_set(blkno, + vmbuffer, + VISIBILITYMAP_ALL_VISIBLE | + VISIBILITYMAP_ALL_FROZEN, + vacrel->rel->rd_locator); /* * Emit WAL for setting PD_ALL_VISIBLE on the heap page and @@ -2846,9 +2846,9 @@ lazy_vacuum_heap_page(LVRelState *vacrel, BlockNumber blkno, Buffer buffer, */ PageSetAllVisible(page); PageClearPrunable(page); - visibilitymap_set(blkno, - vmbuffer, vmflags, - vacrel->rel->rd_locator); + (void) visibilitymap_set(blkno, + vmbuffer, vmflags, + vacrel->rel->rd_locator); conflict_xid = newest_live_xid; } diff --git a/src/backend/access/heap/visibilitymap.c b/src/backend/access/heap/visibilitymap.c index 15bd5cf717c..fe5ce437e1b 100644 --- a/src/backend/access/heap/visibilitymap.c +++ b/src/backend/access/heap/visibilitymap.c @@ -255,8 +255,10 @@ visibilitymap_pin_ok(BlockNumber heapBlk, Buffer vmbuf) * containing heapBlk. * * rlocator is used only for debugging messages. + * + * Returns the state of the page's VM bits before setting flags. */ -void +uint8 visibilitymap_set(BlockNumber heapBlk, Buffer vmBuf, uint8 flags, RelFileLocator rlocator) @@ -299,6 +301,8 @@ visibilitymap_set(BlockNumber heapBlk, map[mapByte] |= (flags << mapOffset); MarkBufferDirty(vmBuf); } + + return status; } /* diff --git a/src/include/access/visibilitymap.h b/src/include/access/visibilitymap.h index b860c4ef3ca..165efd1c00e 100644 --- a/src/include/access/visibilitymap.h +++ b/src/include/access/visibilitymap.h @@ -31,9 +31,9 @@ extern bool visibilitymap_clear(RelFileLocator rlocator, BlockNumber heapBlk, extern void visibilitymap_pin(Relation rel, BlockNumber heapBlk, Buffer *vmbuf); extern bool visibilitymap_pin_ok(BlockNumber heapBlk, Buffer vmbuf); -extern void visibilitymap_set(BlockNumber heapBlk, - Buffer vmBuf, uint8 flags, - RelFileLocator rlocator); +extern uint8 visibilitymap_set(BlockNumber heapBlk, + Buffer vmBuf, uint8 flags, + RelFileLocator rlocator); extern uint8 visibilitymap_get_status(Relation rel, BlockNumber heapBlk, Buffer *vmbuf); extern void visibilitymap_count(Relation rel, BlockNumber *all_visible, BlockNumber *all_frozen); extern BlockNumber visibilitymap_prepare_truncate(Relation rel, -- 2.43.0
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-11 20:44                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-09-14 15:49                     ` Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Melanie Plageman @ 2026-09-14 15:49 UTC (permalink / raw)
  To: Michael Paquier <michael@paquier.xyz>; +Cc: Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Fri, Sep 11, 2026 at 4:44 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> > In master, it probably makes sense to change back the signature of
> > visibilitymap_set() to report if it did actually set the bit(s) and
> > then use that to decide whether to set the page LSN. Unless we don't
> > think it is too late in 19 to change the visibilitymap_set() API. It
> > does mean all the other callers of visibilitymap_set() are going to
> > discard the return value, but maybe that's okay?
>
> Since we are putting out a beta 4, I feel fine about changing the
> visiblitymap_set() API back. Here is a patch that does that.

I've pushed a fix for this and marked the open item as resolved.

- Melanie






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-09-16 21:59                   ` Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2 siblings, 1 reply; 30+ messages in thread

From: Robert Haas @ 2026-09-16 21:59 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Mon, Aug 31, 2026 at 11:47 AM Melanie Plageman
<melanieplageman@gmail.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...

I feel like treating the VM and FSM as non-standard pages is the right
thing to do, because in fact they are not standard pages. Unless you
know something I don't, I think we need to fix
RelationCopyStorageUsingBuffer to treat them that way. What's your
reason for not wanting to back-patch that change?

AFAICS, the tougher question is what to do about the main fork. I
originally resisted the idea of treating all pages as non-standard
pages here because, well, most of the time they will be standard, and
treating them as non-standard adds considerable expense. However, a
bit of exploration with Claude poked a few holes in this idea. It
notes that:

1. RelationCopyStorage treats all pages as non-standard, so having
RelationCopyStorageUsingBuffer do otherwise is inconsistent.

2. ginDeletePostingPage has a comment saying that GIN uses
non-standard pages prior to 9.4.

3. The value of STRATEGY WAL_LOG is that it's faster for small
databases, so the additional WAL volume may not be a huge deal anyway.

So I'm starting to think Matthias had the right idea back in 2024. It
still seems like a lamentable change to me, but to quote Tom quoting
an unnamed wise man: "I can make this code arbitrarily fast, if it
doesn't have to give the right answer." As far as that goes, point #2
is the strongest argument: there aren't likely many people who have a
GIN index that hasn't been rewritten since pre-9.4 and who have that
index inside of a database that they use as a template, but any such
people who do exist will be justifiably annoyed if CREATE DATABASE
with any set of options produces a corrupt copy.

-- 
Robert Haas






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
@ 2026-09-22 23:12                     ` Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-09-22 23:12 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Wed, Sep 16, 2026 at 5:59 PM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Aug 31, 2026 at 11:47 AM Melanie Plageman
> <melanieplageman@gmail.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...
>
> I feel like treating the VM and FSM as non-standard pages is the right
> thing to do, because in fact they are not standard pages. Unless you
> know something I don't, I think we need to fix
> RelationCopyStorageUsingBuffer to treat them that way. What's your
> reason for not wanting to back-patch that change?

That was before I realized that it allows us to trivially cause a
serious data corruption bug with tuple locking. We should definitely
backpatch the part that passes false for VM and FSM.

> AFAICS, the tougher question is what to do about the main fork. I
> originally resisted the idea of treating all pages as non-standard
> pages here because, well, most of the time they will be standard, and
> treating them as non-standard adds considerable expense. However, a
> bit of exploration with Claude poked a few holes in this idea. It
> notes that:
>
> 1. RelationCopyStorage treats all pages as non-standard, so having
> RelationCopyStorageUsingBuffer do otherwise is inconsistent.
>
> 2. ginDeletePostingPage has a comment saying that GIN uses
> non-standard pages prior to 9.4.
>
> 3. The value of STRATEGY WAL_LOG is that it's faster for small
> databases, so the additional WAL volume may not be a huge deal anyway.

Andres suggested off-list having CreateDatabaseUsingWalLog() thread
information from the catalog about whether the page is a normal heap
page down into RelationCopyStorageUsingBuffer().

I looked into it and we could, when scanning source database pg_class
(ScanSourceDatabasePgClassTuple()), check something like:
RELKIND_HAS_TABLE_AM(classForm->relkind) && classForm->relam ==
HEAP_TABLE_AM_OID
and allowing the "hole punching" if so.

And, it's even easier to do this in RelationCopyStorage() without any
extra catalog info because the callers know what type of relation it
is. So, we could fix RelationCopyStorageUsingBuffer() and make it
consistent with RelationCopyStorage() at the same time (master only).

- Melanie






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-10-12 20:06                       ` Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  0 siblings, 2 replies; 30+ messages in thread

From: Melanie Plageman @ 2026-10-12 20:06 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Tue, Sep 22, 2026 at 7:12 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> Andres suggested off-list having CreateDatabaseUsingWalLog() thread
> information from the catalog about whether the page is a normal heap
> page down into RelationCopyStorageUsingBuffer().

Upon further off-list discussion, instead of checking the AM, we can
just check if the space between pd_upper and pd_lower is all zeroes.
If it is, there is no harm in omitting it. It should always work,
allows the optimization in more cases, and requires less code.
Attached patch does that.

I also added a test. It compares the pages of all forks of a table
between a primary and standby after doing CREATE DATABASE USING
STRATEGY WAL_LOG. I compare the whole page byte-by-byte including page
header and LSN. Are those always guaranteed to be the same on the
standby as well?

- Melanie

Attachments:

v1-0001-Preserve-nonstandard-pages-in-WAL-logged-database.patch (text/x-patch, 8.5K) download From 68303b6ad5bb88835b5f55c779b0d196f0c791d4 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Mon, 12 Oct 2026 15:14:02 -0400 Subject: [PATCH v1] Preserve nonstandard pages in WAL-logged database copies RelationCopyStorageUsingBuffer() passed page_std=true when WAL-logging every copied relation fork. This allowed WAL insertion to omit the region between pd_lower and pd_upper from full-page images and restore it as zeroes during replay. That is safe only for pages using the standard page layout. On nonstandard pages, the apparent hole can contain meaningful data, causing the primary and standby to diverge. Known affected cases include free space maps, visibility maps, and old GIN indexes, though any access method using nonstandard pages could be affected. Losing VM contents can lead to data corruption after promotion. For example, a tuple lock on a promoted standby may find the all-frozen bit already clear and therefore emit no WAL to clear it. The old primary then replays the xmax change without clearing the all-frozen bit, retaining the stale bit. This can cause vacuum failures and make invisible data visible again. Only permit hole removal when the apparent free-space region is already all zeroes, making the omission lossless regardless of page layout. Use a shared bounds helper so RelationCopyStorageUsingBuffer() and XLogRecordAssemble() identify the region using the same conditions. Reported-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reported-by: Rogers Wang <rogers.ww@qq.com> Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/flat/202602021525.qv4m7oge4eca%40alvherre.pgsql#2dd58a8f7d87e15ef37199d5b8a05ac4 --- src/backend/access/transam/xloginsert.c | 13 +++---- src/backend/storage/buffer/bufmgr.c | 20 +++++++++-- src/include/storage/bufpage.h | 15 ++++++++ src/test/recovery/Makefile | 3 +- src/test/recovery/t/034_create_database.pl | 41 +++++++++++++++++++--- 5 files changed, 76 insertions(+), 16 deletions(-) diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 2195df85b10..8de52e3c653 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -730,16 +730,13 @@ XLogRecordAssemble(RmgrId rmid, uint8 info, */ if (regbuf->flags & REGBUF_STANDARD) { - /* Assume we can omit data between pd_lower and pd_upper */ - uint16 lower = ((const PageHeaderData *) page)->pd_lower; - uint16 upper = ((const PageHeaderData *) page)->pd_upper; + const PageHeaderData *phdr = (const PageHeaderData *) page; - if (lower >= SizeOfPageHeaderData && - upper > lower && - upper <= BLCKSZ) + /* Assume we can omit data between pd_lower and pd_upper */ + if (PageFreeSpaceBoundsAreValid(page)) { - bimg.hole_offset = lower; - cbimg.hole_length = upper - lower; + bimg.hole_offset = phdr->pd_lower; + cbimg.hole_length = phdr->pd_upper - phdr->pd_lower; } else { diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index f81c7732e68..e19e76deeb1 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -65,6 +65,7 @@ #include "storage/smgr.h" #include "storage/standby.h" #include "utils/memdebug.h" +#include "utils/memutils.h" #include "utils/ps_status.h" #include "utils/rel.h" #include "utils/resowner.h" @@ -5460,9 +5461,24 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, memcpy(dstPage, srcPage, BLCKSZ); MarkBufferDirty(dstBuf); - /* WAL-log the copied page. */ + /* + * WAL-log the copied page. It is safe to omit the apparent free space + * even if this is not a standard page, provided that the range + * contains only zeroes. + */ if (use_wal) - log_newpage_buffer(dstBuf, true); + { + bool allow_page_std = false; + + if (PageFreeSpaceBoundsAreValid(dstPage)) + { + PageHeader phdr = (PageHeader) dstPage; + + allow_page_std = pg_memory_is_all_zeros(dstPage + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + log_newpage_buffer(dstBuf, allow_page_std); + } END_CRIT_SECTION(); diff --git a/src/include/storage/bufpage.h b/src/include/storage/bufpage.h index 713b6222286..593ad2ee9c6 100644 --- a/src/include/storage/bufpage.h +++ b/src/include/storage/bufpage.h @@ -241,6 +241,21 @@ typedef PageHeaderData *PageHeader; */ #define SizeOfPageHeaderData (offsetof(PageHeaderData, pd_linp)) +/* + * Returns true if the page header describes a non-empty, in-bounds free space + * range. This does not prove that the page actually uses the standard page + * layout. + */ +static inline bool +PageFreeSpaceBoundsAreValid(const PageData *page) +{ + const PageHeaderData *phdr = (const PageHeaderData *) page; + + return phdr->pd_lower >= SizeOfPageHeaderData && + phdr->pd_upper > phdr->pd_lower && + phdr->pd_upper <= BLCKSZ; +} + /* * PageIsEmpty * returns true iff no itemid has been allocated on the page diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile index 9c4102b6b2c..dfd3617c84c 100644 --- a/src/test/recovery/Makefile +++ b/src/test/recovery/Makefile @@ -9,7 +9,8 @@ # #------------------------------------------------------------------------- -EXTRA_INSTALL=contrib/pg_prewarm \ +EXTRA_INSTALL=contrib/pageinspect \ + contrib/pg_prewarm \ contrib/pg_stat_statements \ contrib/test_decoding \ src/test/modules/injection_points \ diff --git a/src/test/recovery/t/034_create_database.pl b/src/test/recovery/t/034_create_database.pl index 194d1837e23..a14699743f4 100644 --- a/src/test/recovery/t/034_create_database.pl +++ b/src/test/recovery/t/034_create_database.pl @@ -10,36 +10,67 @@ use PostgreSQL::Test::Utils; use Test::More; my $node = PostgreSQL::Test::Cluster->new('node'); -$node->init; +$node->init(allows_streaming => 1); $node->start; +$node->backup('backup'); +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($node, 'backup', has_streaming => 1); +$standby->start; + # This checks that any DDLs run on the template database that modify pg_class # are persisted after creating a database from it using the WAL_LOG strategy, # as a direct copy of the template database's pg_class is used in this case. my $db_template = "template1"; my $db_new = "test_db_1"; -# Create table. It should persist on the template database. +# Create a table in the template database and add data then vacuum it, ensuring +# it will have both a VM and FSM. +$node->safe_psql($db_template, q{ +CREATE TABLE tab_db_before_create_1 AS + SELECT generate_series(1, 10000) AS a; +VACUUM (FREEZE, ANALYZE) tab_db_before_create_1; +}); + +# Copy the template database $node->safe_psql("postgres", "CREATE DATABASE $db_new STRATEGY WAL_LOG TEMPLATE $db_template;"); +$node->safe_psql($db_new, 'CREATE EXTENSION pageinspect;'); +# Create a table after copying it. This table should persist in the template +# database but not be in the newly created copy of it. $node->safe_psql($db_template, "CREATE TABLE tab_db_after_create_1 (a INT);"); # Flush the changes affecting the template database, then replay them. $node->safe_psql("postgres", "CHECKPOINT;"); +# Template database has the table we created after copying. $node->stop('immediate'); $node->start; my $result = $node->safe_psql($db_template, - "SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';"); + "SELECT count(*) FROM pg_class WHERE relname = 'tab_db_after_create_1';"); is($result, "1", "check that table exists on template after crash, with checkpoint"); -# The new database should have no tables. +# The new database should not have the table we created after copying. $result = $node->safe_psql($db_new, - "SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';"); + "SELECT count(*) FROM pg_class WHERE relname = 'tab_db_after_create_1';"); is($result, "0", "check that there are no tables from template on new database after crash" ); +$node->wait_for_replay_catchup($standby); + +# The new database should have the same data for all forks on the primary and +# standby for the table created before it was copied from the template. +for my $fork (qw(vm fsm)) +{ + my $query = + "SELECT encode(get_raw_page('tab_db_before_create_1', '$fork', 0), 'hex');"; + my $primary_page = $node->safe_psql($db_new, $query); + my $standby_page = $standby->safe_psql($db_new, $query); + + is($standby_page, $primary_page, "copied $fork page matches standby"); +} + done_testing(); -- 2.43.0
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-10-14 00:38                         ` surya poondla <suryapoondla4@gmail.com>
  2026-11-10 22:25                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  1 sibling, 1 reply; 30+ messages in thread

From: surya poondla @ 2026-10-14 00:38 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

Hi Melanie,

Thanks for the patch. I applied it on master (6b3f8857aa8). It applies and
builds cleanly.


> Upon further off-list discussion, instead of checking the AM, we can
> just check if the space between pd_upper and pd_lower is all zeroes.
> If it is, there is no harm in omitting it. It should always work,
> allows the optimization in more cases, and requires less code.
> Attached patch does that.
>
> I also added a test. It compares the pages of all forks of a table
> between a primary and standby after doing CREATE DATABASE USING
> STRATEGY WAL_LOG. I compare the whole page byte-by-byte including page
> header and LSN. Are those always guaranteed to be the same on the
> standby as well?
>
>
The approach looks correct to me. On replay, RestoreBlockImage() fills the
hole with zeros, so leaving out a hole that is already all zeros
can't change the page. Because RelationCopyStorageUsingBuffer() and
XLogRecordAssemble() now share PageFreeSpaceBoundsAreValid(), the range
checked for zeros is exactly the range that gets omitted. I like that it
needs no AM or fork knowledge.

On your question about the page header, I don't think the whole page is
guaranteed to match.
pd_lsn should match. log_newpage() sets it on the primary, and
XLogReadBufferForRedoExtended() sets it to the same record LSN after
restoring the image.

pd_checksum doesn't, though. The image is taken right after the memcpy, so
it carries the template page's checksum. After that, each node's
in-memory pd_checksum depends on whether that node has flushed the page
(which, since 41d3d64e87a, updates the checksum in place) or re-read it
from disk. The test checkpoints and restarts the primary, but does nothing
comparable on the standby.

I ran some experiments with a streaming standby (checksums on), page 0 of
the copied table's vm fork looked like this:

                                  lsn         checksum
    primary, after CHECKPOINT     0/3082BA50    -24598
    standby, just replayed        0/3082BA50     -7694   (= template's)
    standby, after CHECKPOINT     0/3082BA50    -24598

The fsm page behaves the same way. After the standby's CHECKPOINT the raw
pages matched exactly.

The test passes as written, but I think only because allows_streaming sets
shared_buffers = 1MB, so the standby evicts (and re-checksums) the copied
pages before the comparison.
Adding $standby->append_conf('postgresql.conf', 'shared_buffers = 128MB');
before $standby->start makes both the vm and fsm checks fail here.
The only differing bytes are 8-9 (pd_checksum); pd_lsn matches. A
CHECKPOINT on the standby before comparing, or comparing from
SizeOfPageHeaderData onward, should make it robust.

A few things about the test:
1. The loop compares only block 0 of the vm and fsm forks. Your mail
mentions comparing all forks, but the main fork isn't checked.
If that was left out on purpose (hint bits, say), a comment explaining why
would help. Comparing every block of each fork would also make
the test more robust. The FSM is multi-level, and the old bug affected all
of its pages.

2. In the unpatched build, every vm and fsm image the copy wrote had "hole:
offset: 24, length: 8168", i.e. only the page header was
logged. So a vm/fsm check on any non-empty block will catch a regression.

Regarding WAL volume, in my test, after deletes and VACUUM, almost no heap
or btree page had an all-zero hole. Pruning and compaction leave the old
tuple bytes behind. So for templates with any update/delete history, most
main-fork pages are now logged in full. I measured WAL from
CREATE DATABASE ... STRATEGY WAL_LOG with and without the bufmgr.c changes.
The two test templates each had a 1M-row table with a btree index:

    template                                     unpatched   patched
    template1 (stock)                               4.5 MB    6.0 MB
    INSERT 1M rows; VACUUM FREEZE                   113 MB    114 MB
    same, then DELETE 1/3, UPDATE 10%, VACUUM        79 MB    117 MB

In the delete/update case, pg_waldump --bkp-details showed all 11364 heap
pages and 2735 of 2745 btree pages logged as full images, compared with
almost none before. When VACUUM prunes a page, it compacts the remaining
tuples but doesn't zero the space it frees, so the hole still
holds old tuple data and fails the all-zeros check. Btree pages behave the
same way once VACUUM removes index entries. Even stock template1 loses the
hole on 125 catalog pages, presumably because initdb updates and deletes
catalog rows.

I think correctness has to come first, and the append-only case costs
almost nothing extra. But for a template whose tables have had deletes
or updates followed by VACUUM, CREATE DATABASE wrote about 50% more WAL in
my test (79 MB vs 117 MB), so it might be worth mentioning in the commit
message. Do you think it's worth keeping hole punching for pages we know
are standard (e.g. heap and btree), or is the simpler rule the better
trade-off?

Regarding back-patching, pg_memory_is_all_zeros() was added in v18 (commit
07e9e28b56d), so v15-v17 will need it back-patched or an open-coded
loop. The XLogRecordAssemble() refactor isn't needed for the fix and could
stay master-only, if you'd prefer the back branches to change as
little as possible.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
@ 2026-11-10 22:25                           ` Melanie Plageman <melanieplageman@gmail.com>
  2026-11-11 23:14                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-11-10 22:25 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

Thanks for the review and analysis.

On Tue, Oct 13, 2026 at 8:39 PM surya poondla <suryapoondla4@gmail.com> wrote:
>
>> I also added a test. It compares the pages of all forks of a table
>> between a primary and standby after doing CREATE DATABASE USING
>> STRATEGY WAL_LOG. I compare the whole page byte-by-byte including page
>> header and LSN. Are those always guaranteed to be the same on the
>> standby as well?
>
> The approach looks correct to me. On replay, RestoreBlockImage() fills the hole with zeros, so leaving out a hole that is already all zeros
> can't change the page. Because RelationCopyStorageUsingBuffer() and XLogRecordAssemble() now share PageFreeSpaceBoundsAreValid(), the range
> checked for zeros is exactly the range that gets omitted. I like that it needs no AM or fork knowledge.
>
> On your question about the page header, I don't think the whole page is guaranteed to match.
> pd_lsn should match. log_newpage() sets it on the primary, and XLogReadBufferForRedoExtended() sets it to the same record LSN after restoring the image.
>
> pd_checksum doesn't, though. The image is taken right after the memcpy, so it carries the template page's checksum. After that, each node's
> in-memory pd_checksum depends on whether that node has flushed the page (which, since 41d3d64e87a, updates the checksum in place) or re-read it
> from disk. The test checkpoints and restarts the primary, but does nothing comparable on the standby.

Thanks for the feedback and for doing that investigation. I changed
the test to just check the VM contents across primary and standby with
pg_visibilitymap_summary(). That should resolve this issue.

> A few things about the test:
> 1. The loop compares only block 0 of the vm and fsm forks. Your mail mentions comparing all forks, but the main fork isn't checked.
> If that was left out on purpose (hint bits, say), a comment explaining why would help. Comparing every block of each fork would also make
> the test more robust. The FSM is multi-level, and the old bug affected all of its pages.

In the new version of the test, I only check the VM page contents are
the same on primary and standby. I'm not too worried about checking
the FSM because it is never a correctness issue for the FSM to be
wrong. We could add something to the test checking that the contents
of the main fork are the same on primary and standby, but that
wouldn't fail without the commit (since it wasn't broken before), so I
didn't do that so far.

> 2. In the unpatched build, every vm and fsm image the copy wrote had "hole: offset: 24, length: 8168", i.e. only the page header was
> logged. So a vm/fsm check on any non-empty block will catch a regression.

Right. The new test only compares the VM, so it is only examining one
page. So, that should be efficient enough.

> Regarding WAL volume, in my test, after deletes and VACUUM, almost no heap or btree page had an all-zero hole. Pruning and compaction leave the old
> tuple bytes behind. So for templates with any update/delete history, most main-fork pages are now logged in full.
<-- snip -->
> I think correctness has to come first, and the append-only case costs almost nothing extra. But for a template whose tables have had deletes
> or updates followed by VACUUM, CREATE DATABASE wrote about 50% more WAL in my test (79 MB vs 117 MB), so it might be worth mentioning in the commit message. Do you think it's worth keeping hole punching for pages we know are standard (e.g. heap and btree), or is the simpler rule the better trade-off?

After trying to implement it, excepting heap tables and btree indexes
was a relatively small diff, so I went ahead and did that in v2.

> Regarding back-patching, pg_memory_is_all_zeros() was added in v18 (commit 07e9e28b56d), so v15-v17 will need it back-patched or an open-coded
> loop.

Good point. I haven't tried yet, but I think it shouldn't be too hard
to do this ourselves in backbranches. I will check.

> The XLogRecordAssemble() refactor isn't needed for the fix and could stay master-only, if you'd prefer the back branches to change as
> little as possible.

I think it's probably best to ensure that their criteria are the same,
ensuring that the bytes proven safe to discard are exactly those
removed from the full-page image. It's less of a code cleanup and more
of a code drift protection. But I might be able to be convinced
otherwise.

- Melanie






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-10 22:25                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-11-11 23:14                             ` surya poondla <suryapoondla4@gmail.com>
  2026-11-16 17:37                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: surya poondla @ 2026-11-11 23:14 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

Hi Melanie,

> Thanks for the feedback and for doing that investigation. I changed
> the test to just check the VM contents across primary and standby with
> pg_visibilitymap_summary().


Thanks for v2 and for addressing all the points. Both patches apply cleanly
on master (8d87367b39e) and build without warnings.

I reran the same WAL measurements as before, with CREATE DATABASE ...
STRATEGY WAL_LOG on master vs v2 (the two test templates each have a
1M-row table with a btree index):

    template
 master       v2
    template1 (stock)                                                 4549
kB   5733 kB
    1M rows inserted, VACUUM FREEZE                 113 MB    114 MB
    same, after deleting 1/3 and updating 10%,
      then VACUUM                                                    79 MB
    80 MB

With v1 the last case was 117 MB, so the regression is gone. The remaining
increase, mostly visible for template1, is the VM and FSM pages now being
logged in full, which is the fix itself.

For 0002, ALTER TABLE/INDEX ... SET TABLESPACE on the delete/update
  table:

                        master       v2
    heap              89 MB     61 MB
    btree index    22 MB     13 MB

The test passes fine on v2 and for master it fails as expected on "copied
table visibility map matches standby".
The standby shows f|f for all 45 blocks, where the primary shows t|t. It
compares per-block pg_visibility_map() output, which also covers the
check-every-block point from before.

A minor optional suggestion, isnt($primary_visibility_map, '', ...) only
checks that rows come back. If VACUUM (FREEZE) ever left the bits
unset, both nodes would show f|f and the test would pass without testing
anything. Checking that the primary has at least one
all-frozen page would guard against that.

Overall v2 looks good to me.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-10 22:25                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-11 23:14                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
@ 2026-11-16 17:37                               ` Melanie Plageman <melanieplageman@gmail.com>
  2026-11-16 21:52                                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-11-16 17:37 UTC (permalink / raw)
  To: surya poondla <suryapoondla4@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Wed, Nov 11, 2026 at 6:14 PM surya poondla <suryapoondla4@gmail.com> wrote:
>
> A minor optional suggestion, isnt($primary_visibility_map, '', ...) only checks that rows come back. If VACUUM (FREEZE) ever left the bits
> unset, both nodes would show f|f and the test would pass without testing anything. Checking that the primary has at least one
> all-frozen page would guard against that.

Okay, I did this in v3.

- Melanie






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-10 22:25                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-11 23:14                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-16 17:37                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-11-16 21:52                                 ` surya poondla <suryapoondla4@gmail.com>
  2026-11-17 23:19                                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: surya poondla @ 2026-11-16 21:52 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>; Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>

Hi Melanie,

Thanks for v3 and for adding the all-frozen check.

v3 looks good.

A few small comments:
1. In 0002, tablecmds.c already includes catalog/pg_am.h, which exports
catalog/pg_am_d.h, so the added #include "catalog/pg_am_d.h" is redundant.

2. For the back-branch wrapper of CreateAndCopyRelationData(), I'd
suggest it pass false for the main fork. For an unknown caller that is
always correct, the all-zeroes check still keeps the saving
whenever the hole is actually zero, and the non-main forks are handled
correctly either way.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-14 00:38                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-10 22:25                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-11 23:14                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
  2026-11-16 17:37                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-16 21:52                                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts surya poondla <suryapoondla4@gmail.com>
@ 2026-11-17 23:19                                   ` surya poondla <suryapoondla4@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: surya poondla @ 2026-11-17 23:19 UTC (permalink / raw)
  To: houzhijie22@gmail.com <houzhijie22@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>; Melanie Plageman <melanieplageman@gmail.com>

Hi Zhijie,

Nice find on the verifyBackupPageConsistency(). I tried your draft patch
with a primary and a streaming
standby, both with wal_consistency_checking = 'heap,heap2'.

With your patch on master, the standby stops after a plain CREATE DATABASE
from template1, on a catalog insert (from CREATE EXTENSION) in the new
database:

    FATAL:  inconsistent page found, rel 1663/16384/2608, forknum 2, blkno 0

With v3 applied as well, the same workload runs clean.

The fix looks good to me. One small comment, special-casing
VISIBILITYMAP_FORKNUM in verifyBackupPageConsistency() makes sense to
avoid changing rm_mask(). On master it might be cleaner to pass the fork
number to rm_mask and let heap_mask() skip mask_unused_space() for VM
pages, keeping your approach for the back branches.

Separately, since VM pages are now actually compared, any VM change that
bypasses WAL will show up as an inconsistency.
heap_page_fix_vm_corruption() in pruneheap.c clears corrupted VM bits
without WAL ("This VM clear is not WAL-logged ..."), would it make sense to
WAL-log that clear?
Today the repair only fixes the primary, a standby keeps the corrupted bit,
so its index-only scans can still trust it, and it comes back after a
failover.
WAL-Logging it would fix the standby too and keep the new check from
tripping over it. That's probably a separate patch, though.

Regards,
Surya Poondla

^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-11-06 15:13                         ` Robert Haas <robertmhaas@gmail.com>
  2026-11-10 22:10                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  1 sibling, 1 reply; 30+ messages in thread

From: Robert Haas @ 2026-11-06 15:13 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Mon, Oct 12, 2026 at 4:07 PM Melanie Plageman
<melanieplageman@gmail.com> wrote:
> Upon further off-list discussion, instead of checking the AM, we can
> just check if the space between pd_upper and pd_lower is all zeroes.
> If it is, there is no harm in omitting it. It should always work,
> allows the optimization in more cases, and requires less code.
> Attached patch does that.

The problem with this approach is that it also includes the hole data
when the page is standard but the space between pd_upper and pd_lower
contains leftover garbage rather than zero bytes. That case is common:
when we prune-and-defragment heap pages, we don't zero the hole. I
think it would be worth trying to pass down the AM OID and always use
page_std = true for main-fork heap and btree pages (and maybe other
in-core AMs with no known legacy problems). That would mitigate the
practical downsides of the patch considerably. Whether you do that or
not, it's probably worth mentioning the performance concern in the
commit message. With that change, the downside would only exist for
out-of-core AMs, which do exist, and they could consider vigorous
hole-zeroing as a workaround.

-- 
Robert Haas
Databricks






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
@ 2026-11-10 22:10                           ` Melanie Plageman <melanieplageman@gmail.com>
  2026-11-14 13:08                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-11-10 22:10 UTC (permalink / raw)
  To: Robert Haas <robertmhaas@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Fri, Nov 6, 2026 at 10:13 AM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Oct 12, 2026 at 4:07 PM Melanie Plageman
> <melanieplageman@gmail.com> wrote:
> > Upon further off-list discussion, instead of checking the AM, we can
> > just check if the space between pd_upper and pd_lower is all zeroes.
> > If it is, there is no harm in omitting it. It should always work,
> > allows the optimization in more cases, and requires less code.
> > Attached patch does that.
>
> The problem with this approach is that it also includes the hole data
> when the page is standard but the space between pd_upper and pd_lower
> contains leftover garbage rather than zero bytes. That case is common:
> when we prune-and-defragment heap pages, we don't zero the hole. I
> think it would be worth trying to pass down the AM OID and always use
> page_std = true for main-fork heap and btree pages (and maybe other
> in-core AMs with no known legacy problems). That would mitigate the
> practical downsides of the patch considerably. Whether you do that or
> not, it's probably worth mentioning the performance concern in the
> commit message. With that change, the downside would only exist for
> out-of-core AMs, which do exist, and they could consider vigorous
> hole-zeroing as a workaround.

I was aware that prune/defrag doesn't zero the data and so that would
not be included. I was mostly concerned that singling out heap tables
and btree felt a little inexact. But I suppose since it's a pretty
trivial diff to thread that information up from
ScanSourceDatabasePgClassTuple() to RelationCopyStorageUsingBuffer(),
it's worth doing.

Attached v2 uses a hybrid approach that passes page_std for heap
tables and btree indexes as well as when the data between pd_upper and
pd_lower is all zeroes. That allows other AMs to zero out that area if
they want to preserve the old behavior and avoid a regression (which I
think was what you were suggesting).

I also updated the test to use pg_visibilitymap_summary() instead of
comparing the raw page, which should fix test stability concerns.

The second commit in the patch set is targeting master only and would
extend this approach to RelationCopyStorage(), which would reduce the
WAL volume produced and works even more naturally there because they
already know the relation AM and page layout.

- Melanie

Attachments: download all (2 files, .tar.gz)

v2-0001-Fix-WAL-logged-CREATE-DATABASE-with-nonstandard-p.patch (text/x-patch, 13.6K) download From 51e932eae4582c88783b0e66bc950599fb35ea9c Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Tue, 10 Nov 2026 14:47:58 -0500 Subject: [PATCH v2 1/2] Fix WAL-logged CREATE DATABASE with nonstandard pages RelationCopyStorageUsingBuffer() passed page_std=true when WAL-logging every copied relation fork. This allowed WAL insertion to omit the region between pd_lower and pd_upper from full-page images and restore it as zeroes during replay. That is safe only for pages using the standard page layout. On nonstandard pages, the apparent hole can contain meaningful data, causing the primary and standby to diverge. Known affected cases include free space maps, visibility maps, and old GIN indexes, though any access method using nonstandard pages could be affected. Losing VM contents can lead to data corruption after promotion. For example, a tuple lock on a promoted standby may find the all-frozen bit already clear and therefore emit no WAL to clear it. The old primary then replays the xmax change without clearing the all-frozen bit, retaining the stale bit. This can cause vacuum failures and make invisible data visible again. Only remove the apparent free-space region from WAL when copying a heap table or B-tree index, whose pages are known to use the standard layout. For other relation types, remove the region only when it contains all zeroes, ensuring that replay reconstructs the page without data loss. Use a shared bounds helper so that RelationCopyStorageUsingBuffer() checks the same region that XLogRecordAssemble() omits. This may increase WAL volume for access methods other than heap and B-tree when the space between pd_lower and pd_upper contains stale data that was previously omitted. Such access methods can preserve hole removal by zeroing that space. Reported-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reported-by: Rogers Wang <rogers.ww@qq.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: surya poondla <suryapoondla4@gmail.com> Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/flat/CAEze2WgNjODiAiTU18E06_BrRK0nqwJ1kdS_bN61c2_DpFDSnQ%40mail.gmail.com Backpatch-through: 15 --- src/backend/access/transam/xloginsert.c | 13 +++---- src/backend/commands/dbcommands.c | 18 ++++++++- src/backend/storage/buffer/bufmgr.c | 37 +++++++++++++++---- src/include/storage/bufmgr.h | 3 +- src/include/storage/bufpage.h | 14 +++++++ src/test/recovery/Makefile | 1 + src/test/recovery/t/034_create_database.pl | 43 +++++++++++++++++++--- 7 files changed, 106 insertions(+), 23 deletions(-) diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 341deb7b80d..251688e37fd 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -740,16 +740,13 @@ XLogRecordAssemble(RmgrId rmid, uint8 info, */ if (regbuf->flags & REGBUF_STANDARD) { - /* Assume we can omit data between pd_lower and pd_upper */ - uint16 lower = ((const PageHeaderData *) page)->pd_lower; - uint16 upper = ((const PageHeaderData *) page)->pd_upper; + const PageHeaderData *phdr = (const PageHeaderData *) page; - if (lower >= SizeOfPageHeaderData && - upper > lower && - upper <= BLCKSZ) + /* Assume we can omit data between pd_lower and pd_upper */ + if (PageFreeSpaceBoundsAreValid(page)) { - bimg.hole_offset = lower; - cbimg.hole_length = upper - lower; + bimg.hole_offset = phdr->pd_lower; + cbimg.hole_length = phdr->pd_upper - phdr->pd_lower; } else { diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 7e3fc59eafd..f514579e8fd 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -36,6 +36,7 @@ #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/objectaccess.h" +#include "catalog/pg_am_d.h" #include "catalog/pg_authid.h" #include "catalog/pg_collation.h" #include "catalog/pg_database.h" @@ -110,6 +111,7 @@ typedef struct CreateDBRelInfo RelFileLocator rlocator; /* physical relation identifier */ Oid reloid; /* relation oid */ bool permanent; /* relation is permanent or unlogged */ + bool mainfork_page_std; /* main fork has standard page layout */ } CreateDBRelInfo; @@ -217,7 +219,8 @@ CreateDatabaseUsingWalLog(Oid src_dboid, Oid dst_dboid, LockRelationId(&dstrelid, AccessShareLock); /* Copy relation storage from source to the destination. */ - CreateAndCopyRelationData(srcrlocator, dstrlocator, relinfo->permanent); + CreateAndCopyRelationData(srcrlocator, dstrlocator, relinfo->permanent, + relinfo->mainfork_page_std); /* Release the relation locks. */ UnlockRelationId(&srcrelid, AccessShareLock); @@ -449,6 +452,19 @@ ScanSourceDatabasePgClassTuple(HeapTupleData *tuple, Oid tbid, Oid dbid, relinfo->permanent = (classForm->relpersistence == RELPERSISTENCE_PERMANENT) ? true : false; + /* + * Heap tables and B-tree indexes are common cases whose main forks are + * known to use the standard page layout. Other access methods may do so + * as well, but some, such as GIN, have legacy page formats that differ + * from their current format. Keep the classification conservative by + * limiting it to heap and B-tree. + */ + relinfo->mainfork_page_std = + (RELKIND_HAS_TABLE_AM(classForm->relkind) && + classForm->relam == HEAP_TABLE_AM_OID) || + (classForm->relkind == RELKIND_INDEX && + classForm->relam == BTREE_AM_OID); + return relinfo; } diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index c17fc3590f0..4dbc25c6c50 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -65,6 +65,7 @@ #include "storage/smgr.h" #include "storage/standby.h" #include "utils/memdebug.h" +#include "utils/memutils.h" #include "utils/ps_status.h" #include "utils/rel.h" #include "utils/resowner.h" @@ -666,7 +667,8 @@ static void FindAndDropRelationBuffers(RelFileLocator rlocator, BlockNumber firstDelBlock); static void RelationCopyStorageUsingBuffer(RelFileLocator srclocator, RelFileLocator dstlocator, - ForkNumber forkNum, bool permanent); + ForkNumber forkNum, bool permanent, + bool page_std); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); #ifdef USE_ASSERT_CHECKING @@ -5376,7 +5378,8 @@ FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels) static void RelationCopyStorageUsingBuffer(RelFileLocator srclocator, RelFileLocator dstlocator, - ForkNumber forkNum, bool permanent) + ForkNumber forkNum, bool permanent, + bool page_std) { Buffer srcBuf; Buffer dstBuf; @@ -5462,7 +5465,24 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, /* WAL-log the copied page. */ if (use_wal) - log_newpage_buffer(dstBuf, true); + { + bool allow_page_std = page_std; + + /* + * For page layouts we don't recognize, hole removal is still safe + * if the exact range that WAL would omit contains only zeroes. + */ + if (!allow_page_std && PageFreeSpaceBoundsAreValid(dstPage)) + { + PageHeader phdr = (PageHeader) dstPage; + + allow_page_std = + pg_memory_is_all_zeros(dstPage + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + + log_newpage_buffer(dstBuf, allow_page_std); + } END_CRIT_SECTION(); @@ -5484,12 +5504,15 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, * * Pass permanent as true for permanent relations and false for * unlogged relations. Currently this API is not supported for - * temporary relations. + * temporary relations. mainfork_page_std indicates whether the main + * fork uses the standard page layout; all other forks are treated as + * non-standard. * -------------------------------------------------------------------- */ void CreateAndCopyRelationData(RelFileLocator src_rlocator, - RelFileLocator dst_rlocator, bool permanent) + RelFileLocator dst_rlocator, bool permanent, + bool mainfork_page_std) { char relpersistence; SMgrRelation src_rel; @@ -5512,7 +5535,7 @@ CreateAndCopyRelationData(RelFileLocator src_rlocator, /* copy main fork. */ RelationCopyStorageUsingBuffer(src_rlocator, dst_rlocator, MAIN_FORKNUM, - permanent); + permanent, mainfork_page_std); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -5531,7 +5554,7 @@ CreateAndCopyRelationData(RelFileLocator src_rlocator, /* Copy a fork's data, block by block. */ RelationCopyStorageUsingBuffer(src_rlocator, dst_rlocator, forkNum, - permanent); + permanent, false); } } } diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 6837b35fc6d..b8e9b7f1093 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -297,7 +297,8 @@ extern void FlushRelationBuffers(Relation rel); extern void FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels); extern void CreateAndCopyRelationData(RelFileLocator src_rlocator, RelFileLocator dst_rlocator, - bool permanent); + bool permanent, + bool mainfork_page_std); extern void FlushDatabaseBuffers(Oid dbid); extern void DropRelationBuffers(SMgrRelation smgr_reln, ForkNumber *forkNum, diff --git a/src/include/storage/bufpage.h b/src/include/storage/bufpage.h index 713b6222286..2fe9085f881 100644 --- a/src/include/storage/bufpage.h +++ b/src/include/storage/bufpage.h @@ -241,6 +241,20 @@ typedef PageHeaderData *PageHeader; */ #define SizeOfPageHeaderData (offsetof(PageHeaderData, pd_linp)) +/* + * Returns true if the page header describes a non-empty, in-bounds free space + * range. This does not prove that the page uses the standard page layout. + */ +static inline bool +PageFreeSpaceBoundsAreValid(const PageData *page) +{ + const PageHeaderData *phdr = (const PageHeaderData *) page; + + return phdr->pd_lower >= SizeOfPageHeaderData && + phdr->pd_upper > phdr->pd_lower && + phdr->pd_upper <= BLCKSZ; +} + /* * PageIsEmpty * returns true iff no itemid has been allocated on the page diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile index 9c4102b6b2c..e90e0b1ad4c 100644 --- a/src/test/recovery/Makefile +++ b/src/test/recovery/Makefile @@ -11,6 +11,7 @@ EXTRA_INSTALL=contrib/pg_prewarm \ contrib/pg_stat_statements \ + contrib/pg_visibility \ contrib/test_decoding \ src/test/modules/injection_points \ src/test/modules/test_wait_lsn diff --git a/src/test/recovery/t/034_create_database.pl b/src/test/recovery/t/034_create_database.pl index 194d1837e23..a5c1ff228ba 100644 --- a/src/test/recovery/t/034_create_database.pl +++ b/src/test/recovery/t/034_create_database.pl @@ -10,19 +10,34 @@ use PostgreSQL::Test::Utils; use Test::More; my $node = PostgreSQL::Test::Cluster->new('node'); -$node->init; +$node->init(allows_streaming => 1); $node->start; +$node->backup('backup'); +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($node, 'backup', has_streaming => 1); +$standby->start; + # This checks that any DDLs run on the template database that modify pg_class # are persisted after creating a database from it using the WAL_LOG strategy, # as a direct copy of the template database's pg_class is used in this case. my $db_template = "template1"; my $db_new = "test_db_1"; -# Create table. It should persist on the template database. +# Create a table with a populated visibility map in the template database. +$node->safe_psql($db_template, q{ + CREATE TABLE tab_db_before_create_1 AS + SELECT generate_series(1, 10000) AS a; + VACUUM (FREEZE) tab_db_before_create_1; +}); + +# Copy the template database. $node->safe_psql("postgres", "CREATE DATABASE $db_new STRATEGY WAL_LOG TEMPLATE $db_template;"); +$node->safe_psql($db_new, "CREATE EXTENSION pg_visibility;"); +# This table should persist in the template database but not be present in the +# copy made above. $node->safe_psql($db_template, "CREATE TABLE tab_db_after_create_1 (a INT);"); # Flush the changes affecting the template database, then replay them. @@ -31,15 +46,31 @@ $node->safe_psql("postgres", "CHECKPOINT;"); $node->stop('immediate'); $node->start; my $result = $node->safe_psql($db_template, - "SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';"); + "SELECT count(*) FROM pg_class WHERE relname = 'tab_db_after_create_1';"); is($result, "1", "check that table exists on template after crash, with checkpoint"); -# The new database should have no tables. +# The new database should not have the table created after it was copied. $result = $node->safe_psql($db_new, - "SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';"); + "SELECT count(*) FROM pg_class WHERE relname = 'tab_db_after_create_1';"); is($result, "0", - "check that there are no tables from template on new database after crash" + "check that table created after database copy is absent after crash" ); +$node->wait_for_replay_catchup($standby); + +my $visibility_map_query = q{ + SELECT blkno, all_visible, all_frozen + FROM pg_visibility_map('tab_db_before_create_1') + ORDER BY blkno; +}; +my $primary_visibility_map = + $node->safe_psql($db_new, $visibility_map_query); +my $standby_visibility_map = + $standby->safe_psql($db_new, $visibility_map_query); + +isnt($primary_visibility_map, '', 'copied table has visibility map entries'); +is($standby_visibility_map, $primary_visibility_map, + "copied table visibility map matches standby"); + done_testing(); -- 2.43.0
v2-0002-Reduce-WAL-generated-when-copying-relation-storag.patch (text/x-patch, 5.6K) download From 264803abd3aa616edb90b418d98280373aa3c71d Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Tue, 10 Nov 2026 14:47:58 -0500 Subject: [PATCH v2 2/2] Reduce WAL generated when copying relation storage RelationCopyStorage() included the region between pd_lower and pd_upper in full-page images, even when that region was known to be free space, because it had no information about the page layout. It conservatively treated every page as nonstandard, so this was not a correctness bug, but it could generate unnecessary WAL. A recent commit fixed RelationCopyStorageUsingBuffer(), which previously treated every page as standard and could therefore omit meaningful data between pd_lower and pd_upper. It now omits that region only for heap and B-tree main-fork pages, which are known to use the standard page layout, or when the region contains only zeroes. Apply the same policy to RelationCopyStorage(). This reduces WAL volume for known standard page layouts while preserving the full contents of unknown layouts unless omission is demonstrably lossless. --- src/backend/access/heap/heapam_handler.c | 4 ++-- src/backend/catalog/storage.c | 19 ++++++++++++++----- src/backend/commands/tablecmds.c | 6 ++++-- src/include/catalog/storage.h | 3 ++- 4 files changed, 22 insertions(+), 10 deletions(-) diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 05a81e4557a..ff2345b9233 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -555,7 +555,7 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) /* copy main fork */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, true); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -574,7 +574,7 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index e443a4993c5..1a4397d15da 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -476,7 +476,7 @@ RelationPreTruncate(Relation rel) */ void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence) + ForkNumber forkNum, char relpersistence, bool page_std) { bool use_wal; bool copying_initfork; @@ -508,6 +508,7 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, for (blkno = 0; blkno < nblocks; blkno++) { BulkWriteBuffer buf; + bool allow_page_std = page_std; int piv_flags; bool checksum_failure; bool verified; @@ -551,11 +552,19 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, } /* - * Queue the page for WAL-logging and writing out. Unfortunately we - * don't know what kind of a page this is, so we have to log the full - * page including any unused space. + * For page layouts we don't recognize, hole removal is still safe if + * the exact range that WAL would omit contains only zeroes. */ - smgr_bulk_write(bulkstate, blkno, buf, false); + if (!allow_page_std && PageFreeSpaceBoundsAreValid((Page) buf)) + { + PageHeader phdr = (PageHeader) buf; + + allow_page_std = + pg_memory_is_all_zeros((char *) buf + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + + smgr_bulk_write(bulkstate, blkno, buf, allow_page_std); } smgr_bulk_finish(bulkstate); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index aa89c9a4637..cbd2bf0b2b7 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -36,6 +36,7 @@ #include "catalog/objectaccess.h" #include "catalog/partition.h" #include "catalog/pg_am.h" +#include "catalog/pg_am_d.h" #include "catalog/pg_attrdef.h" #include "catalog/pg_cast.h" #include "catalog/pg_collation.h" @@ -18527,7 +18528,8 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) /* copy main fork */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, + rel->rd_rel->relam == BTREE_AM_OID); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -18546,7 +18548,7 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(&newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 70f619a6d6f..68211e51c1e 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -30,7 +30,8 @@ extern void RelationPreserveStorage(RelFileLocator rlocator, bool atCommit); extern void RelationPreTruncate(Relation rel); extern void RelationTruncate(Relation rel, BlockNumber nblocks); extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence); + ForkNumber forkNum, char relpersistence, + bool page_std); extern bool RelFileLocatorSkippingWAL(RelFileLocator rlocator); extern Size EstimatePendingSyncsSpace(void); extern void SerializePendingSyncs(Size maxSize, char *startAddress); -- 2.43.0
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-11-10 22:10                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-11-14 13:08                             ` Zhijie Hou <houzhijie22@gmail.com>
  2026-11-16 17:35                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-11-14 13:08 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; Robert Haas <robertmhaas@gmail.com>; +Cc: Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

Hi,

On Wed, Nov11, 2026 at 06:10 AM Melanie Plageman wrote:
>
> Attached v2 uses a hybrid approach that passes page_std for heap
> tables and btree indexes as well as when the data between pd_upper and
> pd_lower is all zeroes. That allows other AMs to zero out that area if
> they want to preserve the old behavior and avoid a regression (which I
> think was what you were suggesting).
While analyzing an issue related to the visibility map, I reviewed the 
0001 patch a bit. The fix looks good to me.

I just have one minor comment on the TAP test in 0001. IIUC, the 
pre-existing test in 034_create_database.pl was added by commit 
8a8661828ae to verify that when CREATE DATABASE loads the template's 
pg_class into shared_buffers, it marks those buffers as permanent so 
that a subsequent checkpoint flushes them to disk.

But with 0001, the modified test vacuuming tab_db_before_create_1 in 
template1 beforehand loads template1's pg_class into shared_buffers with 
BM_PERMANENT already set before CREATE DATABASE runs. If we want to keep 
the original regression coverage, maybe we could run the new test steps 
after the pre-existing test.

-- 
Best Regards,
Zhijie Hou






^ permalink  raw  reply  [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-11-10 22:10                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-14 13:08                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
@ 2026-11-16 17:35                               ` Melanie Plageman <melanieplageman@gmail.com>
  2026-11-17 15:20                                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Melanie Plageman @ 2026-11-16 17:35 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Sat, Nov 14, 2026 at 8:08 AM Zhijie Hou <houzhijie22@gmail.com> wrote:
>
> I just have one minor comment on the TAP test in 0001. IIUC, the
> pre-existing test in 034_create_database.pl was added by commit
> 8a8661828ae to verify that when CREATE DATABASE loads the template's
> pg_class into shared_buffers, it marks those buffers as permanent so
> that a subsequent checkpoint flushes them to disk.
>
> But with 0001, the modified test vacuuming tab_db_before_create_1 in
> template1 beforehand loads template1's pg_class into shared_buffers with
> BM_PERMANENT already set before CREATE DATABASE runs. If we want to keep
> the original regression coverage, maybe we could run the new test steps
> after the pre-existing test.

Ah, thanks so much for catching this. I read the test and not the
commit message adding it and thought it was just trying to make sure
that tables added after the copy didn't end up in the copied database
-- which, if I think about it, would have been a silly test. I've
changed it in attached v3 to retain the coverage in the initial test.

I also realized while editing this that I'll have to make a wrapper
version of CreateAndCopyRelationData() for the backports since it is
public. A bit annoying but I think better than the alternatives.

- Melanie

Attachments: download all (2 files, .tar.gz)

v3-0001-Fix-WAL-logged-CREATE-DATABASE-with-nonstandard-p.patch (text/x-patch, 12.7K) download From 982989fa702981729cc3b9a3bafd30453216a25f Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Tue, 10 Nov 2026 14:47:58 -0500 Subject: [PATCH v3 1/2] Fix WAL-logged CREATE DATABASE with nonstandard pages RelationCopyStorageUsingBuffer() passed page_std=true when WAL-logging every copied relation fork. This allowed WAL insertion to omit the region between pd_lower and pd_upper from full-page images and restore it as zeroes during replay. That is safe only for pages using the standard page layout. On nonstandard pages, the apparent hole can contain meaningful data, causing the primary and standby to diverge. Known affected cases include free space maps, visibility maps, and old GIN indexes, though any access method using nonstandard pages could be affected. Losing VM contents can lead to data corruption after promotion. For example, a tuple lock on a promoted standby may find the all-frozen bit already clear and therefore emit no WAL to clear it. The old primary then replays the xmax change without clearing the all-frozen bit, retaining the stale bit. This can cause vacuum failures and make invisible data visible again. Only remove the apparent free-space region from WAL when copying a heap table or B-tree index, whose pages are known to use the standard layout. For other relation types, remove the region only when it contains all zeroes, ensuring that replay reconstructs the page without data loss. Use a shared bounds helper so that RelationCopyStorageUsingBuffer() checks the same region that XLogRecordAssemble() omits. This may increase WAL volume for access methods other than heap and B-tree when the space between pd_lower and pd_upper contains stale data that was previously omitted. Such access methods can preserve hole removal by zeroing that space. Reported-by: Matthias van de Meent <boekewurm+postgres@gmail.com> Reported-by: Rogers Wang <rogers.ww@qq.com> Reviewed-by: surya poondla <suryapoondla4@gmail.com> Reviewed-by: Robert Haas <robertmhaas@gmail.com> Reviewed-by: Zhijie Hou <houzhijie22@gmail.com> Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/flat/CAEze2WgNjODiAiTU18E06_BrRK0nqwJ1kdS_bN61c2_DpFDSnQ%40mail.gmail.com Backpatch-through: 15 --- src/backend/access/transam/xloginsert.c | 13 +++----- src/backend/commands/dbcommands.c | 18 +++++++++- src/backend/storage/buffer/bufmgr.c | 37 ++++++++++++++++---- src/include/storage/bufmgr.h | 3 +- src/include/storage/bufpage.h | 14 ++++++++ src/test/recovery/Makefile | 1 + src/test/recovery/t/034_create_database.pl | 39 +++++++++++++++++++++- 7 files changed, 107 insertions(+), 18 deletions(-) diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index 341deb7b80d..251688e37fd 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -740,16 +740,13 @@ XLogRecordAssemble(RmgrId rmid, uint8 info, */ if (regbuf->flags & REGBUF_STANDARD) { - /* Assume we can omit data between pd_lower and pd_upper */ - uint16 lower = ((const PageHeaderData *) page)->pd_lower; - uint16 upper = ((const PageHeaderData *) page)->pd_upper; + const PageHeaderData *phdr = (const PageHeaderData *) page; - if (lower >= SizeOfPageHeaderData && - upper > lower && - upper <= BLCKSZ) + /* Assume we can omit data between pd_lower and pd_upper */ + if (PageFreeSpaceBoundsAreValid(page)) { - bimg.hole_offset = lower; - cbimg.hole_length = upper - lower; + bimg.hole_offset = phdr->pd_lower; + cbimg.hole_length = phdr->pd_upper - phdr->pd_lower; } else { diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 7e3fc59eafd..f514579e8fd 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -36,6 +36,7 @@ #include "catalog/dependency.h" #include "catalog/indexing.h" #include "catalog/objectaccess.h" +#include "catalog/pg_am_d.h" #include "catalog/pg_authid.h" #include "catalog/pg_collation.h" #include "catalog/pg_database.h" @@ -110,6 +111,7 @@ typedef struct CreateDBRelInfo RelFileLocator rlocator; /* physical relation identifier */ Oid reloid; /* relation oid */ bool permanent; /* relation is permanent or unlogged */ + bool mainfork_page_std; /* main fork has standard page layout */ } CreateDBRelInfo; @@ -217,7 +219,8 @@ CreateDatabaseUsingWalLog(Oid src_dboid, Oid dst_dboid, LockRelationId(&dstrelid, AccessShareLock); /* Copy relation storage from source to the destination. */ - CreateAndCopyRelationData(srcrlocator, dstrlocator, relinfo->permanent); + CreateAndCopyRelationData(srcrlocator, dstrlocator, relinfo->permanent, + relinfo->mainfork_page_std); /* Release the relation locks. */ UnlockRelationId(&srcrelid, AccessShareLock); @@ -449,6 +452,19 @@ ScanSourceDatabasePgClassTuple(HeapTupleData *tuple, Oid tbid, Oid dbid, relinfo->permanent = (classForm->relpersistence == RELPERSISTENCE_PERMANENT) ? true : false; + /* + * Heap tables and B-tree indexes are common cases whose main forks are + * known to use the standard page layout. Other access methods may do so + * as well, but some, such as GIN, have legacy page formats that differ + * from their current format. Keep the classification conservative by + * limiting it to heap and B-tree. + */ + relinfo->mainfork_page_std = + (RELKIND_HAS_TABLE_AM(classForm->relkind) && + classForm->relam == HEAP_TABLE_AM_OID) || + (classForm->relkind == RELKIND_INDEX && + classForm->relam == BTREE_AM_OID); + return relinfo; } diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c index c17fc3590f0..4dbc25c6c50 100644 --- a/src/backend/storage/buffer/bufmgr.c +++ b/src/backend/storage/buffer/bufmgr.c @@ -65,6 +65,7 @@ #include "storage/smgr.h" #include "storage/standby.h" #include "utils/memdebug.h" +#include "utils/memutils.h" #include "utils/ps_status.h" #include "utils/rel.h" #include "utils/resowner.h" @@ -666,7 +667,8 @@ static void FindAndDropRelationBuffers(RelFileLocator rlocator, BlockNumber firstDelBlock); static void RelationCopyStorageUsingBuffer(RelFileLocator srclocator, RelFileLocator dstlocator, - ForkNumber forkNum, bool permanent); + ForkNumber forkNum, bool permanent, + bool page_std); static void AtProcExit_Buffers(int code, Datum arg); static void CheckForBufferLeaks(void); #ifdef USE_ASSERT_CHECKING @@ -5376,7 +5378,8 @@ FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels) static void RelationCopyStorageUsingBuffer(RelFileLocator srclocator, RelFileLocator dstlocator, - ForkNumber forkNum, bool permanent) + ForkNumber forkNum, bool permanent, + bool page_std) { Buffer srcBuf; Buffer dstBuf; @@ -5462,7 +5465,24 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, /* WAL-log the copied page. */ if (use_wal) - log_newpage_buffer(dstBuf, true); + { + bool allow_page_std = page_std; + + /* + * For page layouts we don't recognize, hole removal is still safe + * if the exact range that WAL would omit contains only zeroes. + */ + if (!allow_page_std && PageFreeSpaceBoundsAreValid(dstPage)) + { + PageHeader phdr = (PageHeader) dstPage; + + allow_page_std = + pg_memory_is_all_zeros(dstPage + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + + log_newpage_buffer(dstBuf, allow_page_std); + } END_CRIT_SECTION(); @@ -5484,12 +5504,15 @@ RelationCopyStorageUsingBuffer(RelFileLocator srclocator, * * Pass permanent as true for permanent relations and false for * unlogged relations. Currently this API is not supported for - * temporary relations. + * temporary relations. mainfork_page_std indicates whether the main + * fork uses the standard page layout; all other forks are treated as + * non-standard. * -------------------------------------------------------------------- */ void CreateAndCopyRelationData(RelFileLocator src_rlocator, - RelFileLocator dst_rlocator, bool permanent) + RelFileLocator dst_rlocator, bool permanent, + bool mainfork_page_std) { char relpersistence; SMgrRelation src_rel; @@ -5512,7 +5535,7 @@ CreateAndCopyRelationData(RelFileLocator src_rlocator, /* copy main fork. */ RelationCopyStorageUsingBuffer(src_rlocator, dst_rlocator, MAIN_FORKNUM, - permanent); + permanent, mainfork_page_std); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -5531,7 +5554,7 @@ CreateAndCopyRelationData(RelFileLocator src_rlocator, /* Copy a fork's data, block by block. */ RelationCopyStorageUsingBuffer(src_rlocator, dst_rlocator, forkNum, - permanent); + permanent, false); } } } diff --git a/src/include/storage/bufmgr.h b/src/include/storage/bufmgr.h index 6837b35fc6d..b8e9b7f1093 100644 --- a/src/include/storage/bufmgr.h +++ b/src/include/storage/bufmgr.h @@ -297,7 +297,8 @@ extern void FlushRelationBuffers(Relation rel); extern void FlushRelationsAllBuffers(SMgrRelation *smgrs, int nrels); extern void CreateAndCopyRelationData(RelFileLocator src_rlocator, RelFileLocator dst_rlocator, - bool permanent); + bool permanent, + bool mainfork_page_std); extern void FlushDatabaseBuffers(Oid dbid); extern void DropRelationBuffers(SMgrRelation smgr_reln, ForkNumber *forkNum, diff --git a/src/include/storage/bufpage.h b/src/include/storage/bufpage.h index 713b6222286..2fe9085f881 100644 --- a/src/include/storage/bufpage.h +++ b/src/include/storage/bufpage.h @@ -241,6 +241,20 @@ typedef PageHeaderData *PageHeader; */ #define SizeOfPageHeaderData (offsetof(PageHeaderData, pd_linp)) +/* + * Returns true if the page header describes a non-empty, in-bounds free space + * range. This does not prove that the page uses the standard page layout. + */ +static inline bool +PageFreeSpaceBoundsAreValid(const PageData *page) +{ + const PageHeaderData *phdr = (const PageHeaderData *) page; + + return phdr->pd_lower >= SizeOfPageHeaderData && + phdr->pd_upper > phdr->pd_lower && + phdr->pd_upper <= BLCKSZ; +} + /* * PageIsEmpty * returns true iff no itemid has been allocated on the page diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile index 9c4102b6b2c..e90e0b1ad4c 100644 --- a/src/test/recovery/Makefile +++ b/src/test/recovery/Makefile @@ -11,6 +11,7 @@ EXTRA_INSTALL=contrib/pg_prewarm \ contrib/pg_stat_statements \ + contrib/pg_visibility \ contrib/test_decoding \ src/test/modules/injection_points \ src/test/modules/test_wait_lsn diff --git a/src/test/recovery/t/034_create_database.pl b/src/test/recovery/t/034_create_database.pl index 194d1837e23..e35cb1eb2d5 100644 --- a/src/test/recovery/t/034_create_database.pl +++ b/src/test/recovery/t/034_create_database.pl @@ -10,9 +10,14 @@ use PostgreSQL::Test::Utils; use Test::More; my $node = PostgreSQL::Test::Cluster->new('node'); -$node->init; +$node->init(allows_streaming => 1); $node->start; +$node->backup('backup'); +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($node, 'backup', has_streaming => 1); +$standby->start; + # This checks that any DDLs run on the template database that modify pg_class # are persisted after creating a database from it using the WAL_LOG strategy, # as a direct copy of the template database's pg_class is used in this case. @@ -42,4 +47,36 @@ is($result, "0", "check that there are no tables from template on new database after crash" ); +# Create a table with a populated visibility map in the template database and +# copy it into a second database. +$node->safe_psql($db_template, q{ + CREATE TABLE tab_db_before_create_2 AS + SELECT generate_series(1, 10000) AS a; + VACUUM (FREEZE) tab_db_before_create_2; +}); +my $db_vm = "test_db_2"; +$node->safe_psql("postgres", + "CREATE DATABASE $db_vm STRATEGY WAL_LOG TEMPLATE $db_template;"); +$node->safe_psql($db_vm, "CREATE EXTENSION pg_visibility;"); + +$node->wait_for_replay_catchup($standby); + +my $visibility_map_query = q{ + SELECT blkno, all_visible, all_frozen + FROM pg_visibility_map('tab_db_before_create_2') + ORDER BY blkno; +}; +my $primary_visibility_map = + $node->safe_psql($db_vm, $visibility_map_query); +my $standby_visibility_map = + $standby->safe_psql($db_vm, $visibility_map_query); +my $primary_has_frozen = $node->safe_psql( + $db_vm, + q{SELECT bool_or(all_frozen) + FROM pg_visibility_map('tab_db_before_create_2');}); + +is($primary_has_frozen, 't', 'copied table has an all-frozen page'); +is($standby_visibility_map, $primary_visibility_map, + "copied table visibility map matches standby"); + done_testing(); -- 2.43.0
v3-0002-Reduce-WAL-generated-when-copying-relation-storag.patch (text/x-patch, 5.6K) download From 662e3e00a108ae9e455f55b9a82978614a5a6a89 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <melanieplageman@gmail.com> Date: Tue, 10 Nov 2026 14:47:58 -0500 Subject: [PATCH v3 2/2] Reduce WAL generated when copying relation storage RelationCopyStorage() included the region between pd_lower and pd_upper in full-page images, even when that region was known to be free space. It conservatively treated every page as nonstandard, which, while correct, could generate unnecessary WAL. A recent commit fixed RelationCopyStorageUsingBuffer(), which previously treated every page as standard and could therefore omit meaningful data. RelationCopyStorageUsingBuffer() now omits that region only for heap and B-tree main-fork pages, which are known to use the standard page layout, or when the region contains only zeroes. Apply the same policy to RelationCopyStorage(). This reduces WAL volume for known standard page layouts while preserving the full contents of unknown layouts unless omission is demonstrably lossless. Reviewed-by: surya poondla <suryapoondla4@gmail.com> Author: Melanie Plageman <melanieplageman@gmail.com> --- src/backend/access/heap/heapam_handler.c | 4 ++-- src/backend/catalog/storage.c | 19 ++++++++++++++----- src/backend/commands/tablecmds.c | 6 ++++-- src/include/catalog/storage.h | 3 ++- 4 files changed, 22 insertions(+), 10 deletions(-) diff --git a/src/backend/access/heap/heapam_handler.c b/src/backend/access/heap/heapam_handler.c index 05a81e4557a..ff2345b9233 100644 --- a/src/backend/access/heap/heapam_handler.c +++ b/src/backend/access/heap/heapam_handler.c @@ -555,7 +555,7 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) /* copy main fork */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, true); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -574,7 +574,7 @@ heapam_relation_copy_data(Relation rel, const RelFileLocator *newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/backend/catalog/storage.c b/src/backend/catalog/storage.c index e443a4993c5..1a4397d15da 100644 --- a/src/backend/catalog/storage.c +++ b/src/backend/catalog/storage.c @@ -476,7 +476,7 @@ RelationPreTruncate(Relation rel) */ void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence) + ForkNumber forkNum, char relpersistence, bool page_std) { bool use_wal; bool copying_initfork; @@ -508,6 +508,7 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, for (blkno = 0; blkno < nblocks; blkno++) { BulkWriteBuffer buf; + bool allow_page_std = page_std; int piv_flags; bool checksum_failure; bool verified; @@ -551,11 +552,19 @@ RelationCopyStorage(SMgrRelation src, SMgrRelation dst, } /* - * Queue the page for WAL-logging and writing out. Unfortunately we - * don't know what kind of a page this is, so we have to log the full - * page including any unused space. + * For page layouts we don't recognize, hole removal is still safe if + * the exact range that WAL would omit contains only zeroes. */ - smgr_bulk_write(bulkstate, blkno, buf, false); + if (!allow_page_std && PageFreeSpaceBoundsAreValid((Page) buf)) + { + PageHeader phdr = (PageHeader) buf; + + allow_page_std = + pg_memory_is_all_zeros((char *) buf + phdr->pd_lower, + phdr->pd_upper - phdr->pd_lower); + } + + smgr_bulk_write(bulkstate, blkno, buf, allow_page_std); } smgr_bulk_finish(bulkstate); } diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b6700929459..ec48b694e88 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -36,6 +36,7 @@ #include "catalog/objectaccess.h" #include "catalog/partition.h" #include "catalog/pg_am.h" +#include "catalog/pg_am_d.h" #include "catalog/pg_attrdef.h" #include "catalog/pg_cast.h" #include "catalog/pg_collation.h" @@ -18531,7 +18532,8 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) /* copy main fork */ RelationCopyStorage(RelationGetSmgr(rel), dstrel, MAIN_FORKNUM, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, + rel->rd_rel->relam == BTREE_AM_OID); /* copy those extra forks that exist */ for (ForkNumber forkNum = MAIN_FORKNUM + 1; @@ -18550,7 +18552,7 @@ index_copy_data(Relation rel, RelFileLocator newrlocator) forkNum == INIT_FORKNUM)) log_smgrcreate(&newrlocator, forkNum); RelationCopyStorage(RelationGetSmgr(rel), dstrel, forkNum, - rel->rd_rel->relpersistence); + rel->rd_rel->relpersistence, false); } } diff --git a/src/include/catalog/storage.h b/src/include/catalog/storage.h index 70f619a6d6f..68211e51c1e 100644 --- a/src/include/catalog/storage.h +++ b/src/include/catalog/storage.h @@ -30,7 +30,8 @@ extern void RelationPreserveStorage(RelFileLocator rlocator, bool atCommit); extern void RelationPreTruncate(Relation rel); extern void RelationTruncate(Relation rel, BlockNumber nblocks); extern void RelationCopyStorage(SMgrRelation src, SMgrRelation dst, - ForkNumber forkNum, char relpersistence); + ForkNumber forkNum, char relpersistence, + bool page_std); extern bool RelFileLocatorSkippingWAL(RelFileLocator rlocator); extern Size EstimatePendingSyncsSpace(void); extern void SerializePendingSyncs(Size maxSize, char *startAddress); -- 2.43.0
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-11-10 22:10                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-14 13:08                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
  2026-11-16 17:35                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
@ 2026-11-17 15:20                                 ` Zhijie Hou <houzhijie22@gmail.com>
  2026-11-17 22:47                                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 1 reply; 30+ messages in thread

From: Zhijie Hou @ 2026-11-17 15:20 UTC (permalink / raw)
  To: Melanie Plageman <melanieplageman@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

Hi,

On Tue, Nov17, 2026 at 01:35 AM Melanie Plageman wrote:
> On Sat, Nov 14, 2026 at 8:08 AM Zhijie Hou <houzhijie22@gmail.com> wrote:
> >
> > I just have one minor comment on the TAP test in 0001. IIUC, the
> > pre-existing test in 034_create_database.pl was added by commit
> > 8a8661828ae to verify that when CREATE DATABASE loads the template's
> > pg_class into shared_buffers, it marks those buffers as permanent so
> > that a subsequent checkpoint flushes them to disk.
> >
> > But with 0001, the modified test vacuuming tab_db_before_create_1 in
> > template1 beforehand loads template1's pg_class into shared_buffers
> > with BM_PERMANENT already set before CREATE DATABASE runs. If we
> > want to keep the original regression coverage, maybe we could run
> > the new test steps after the pre-existing test.
>
> Ah, thanks so much for catching this. I read the test and not the
> commit message adding it and thought it was just trying to make sure
> that tables added after the copy didn't end up in the copied database
> -- which, if I think about it, would have been a silly test. I've
> changed it in attached v3 to retain the coverage in the initial test.
>
> I also realized while editing this that I'll have to make a wrapper
> version of CreateAndCopyRelationData() for the backports since it is
> public. A bit annoying but I think better than the alternatives.
Thanks for updating the patch.

I think we also need to increase shared_buffers (e.g., to 16MB), because 
$node->init(allows_streaming => 1) sets shared_buffers to 1MB 
internally, causing the pg_class pages to be evicted from shared_buffers 
before CREATE TABLE runs, so the test could not reach the old bug path.

BTW, while looking into why the wal_consistency_checking test didn't 
catch this bug, I noticed that verifyBackupPageConsistency() also masks 
out all the bits in VM pages via heap_mask() (somewhat similar to this 
bug, since it treats pd_lower..pd_upper as unused space), so the 
consistency check for VM pages always "passes".  Once we avoid masking 
the VM bitmap there (see the attached draft patch for reference), the 
existing test catches this CREATE DB WAL_LOG bug right away:

   PG_TEST_EXTRA=wal_consistency_checking meson test -C build 
recovery/027_stream_regress

So if it's worth increasing the possibility of catching similar VM bugs, 
maybe we could improve that as well (in a separate patch or thread).

-- 
Best Regards,
Zhijie Hou

Attachments:

nocfbot-0001-Fix-WAL-consistency-checking-for-visibility-map-p.patch (text/plain, 2.6K) download From 4ede72e8ee6d79cda8209a725b1b6b04e07438a4 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Tue, 17 Nov 2026 22:06:38 +0800 Subject: [PATCH v1 1/2] Fix WAL consistency checking for visibility map pages. Both RM_HEAP_ID and RM_HEAP2_ID WAL records register VISIBILITYMAP_FORKNUM buffers (since a507b86900f for VM-setting records, and since ed62d26caca for VM-clearing records). When wal_consistency_checking is enabled for heap or heap2, verifyBackupPageConsistency() previously passed every registered block to rmgr.rm_mask (heap_mask()) without checking forknum. Because a visibility map page is initialized with PageInit(page, BLCKSZ, 0) and stores its bitmap between pd_lower (SizeOfPageHeaderData) and pd_upper (BLCKSZ), mask_unused_space() in heap_mask() zeroed out the entire bitmap in both the WAL backup image and the replay image before comparing them. Consequently, wal_consistency_checking silently missed visibility map page divergences during WAL replay. Fix verifyBackupPageConsistency() to mask only the page LSN and checksum when forknum is VISIBILITYMAP_FORKNUM, avoiding an ABI break to RmgrData.rm_mask. --- src/backend/access/transam/xlogrecovery.c | 18 +++++++++++++++--- 1 file changed, 15 insertions(+), 3 deletions(-) diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 52ac6d58423..490e51f26e5 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -30,6 +30,7 @@ #include <sys/time.h> #include <unistd.h> +#include "access/bufmask.h" #include "access/timeline.h" #include "access/transam.h" #include "access/xact.h" @@ -2547,10 +2548,21 @@ verifyBackupPageConsistency(XLogReaderState *record) errmsg_internal("%s", record->errormsg_buf))); /* - * If masking function is defined, mask both the primary and replay - * images + * Mask both the primary and replay images before comparing them. + * + * Visibility map pages are modified by RM_HEAP_ID and RM_HEAP2_ID + * records, but do not follow the standard heap page layout: their + * bitmap occupies the bytes between pd_lower and pd_upper (see + * visibilitymap.c), which heap_mask() would wipe via + * mask_unused_space(). Only the LSN and checksum can legitimately + * differ on a visibility map page. */ - if (rmgr.rm_mask != NULL) + if (forknum == VISIBILITYMAP_FORKNUM) + { + mask_page_lsn_and_checksum(replay_image_masked); + mask_page_lsn_and_checksum(primary_image_masked); + } + else if (rmgr.rm_mask != NULL) { rmgr.rm_mask(replay_image_masked, blkno); rmgr.rm_mask(primary_image_masked, blkno); -- 2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 30+ messages in thread

* Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts
  2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 14:13 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Tom Lane <tgl@sss.pgh.pa.us>
  2024-05-13 14:52   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
  2024-05-13 19:43     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2024-09-14 18:57       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Akshat Jaimini <destrex271@gmail.com>
  2024-12-10 06:25         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-02-02 15:26           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Álvaro Herrera <alvherre@kurilemu.de>
  2026-08-23 17:04             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Rogers Wang <rogers.ww@qq.com>
  2026-08-31 05:37               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Michael Paquier <michael@paquier.xyz>
  2026-08-31 15:46                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-09-16 21:59                   ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-09-22 23:12                     ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-10-12 20:06                       ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-06 15:13                         ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Robert Haas <robertmhaas@gmail.com>
  2026-11-10 22:10                           ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-14 13:08                             ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
  2026-11-16 17:35                               ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Melanie Plageman <melanieplageman@gmail.com>
  2026-11-17 15:20                                 ` Re: WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Zhijie Hou <houzhijie22@gmail.com>
@ 2026-11-17 22:47                                   ` Melanie Plageman <melanieplageman@gmail.com>
  0 siblings, 0 replies; 30+ messages in thread

From: Melanie Plageman @ 2026-11-17 22:47 UTC (permalink / raw)
  To: Zhijie Hou <houzhijie22@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Michael Paquier <michael@paquier.xyz>; Rogers Wang <rogers.ww@qq.com>; pgsql-hackers@lists.postgresql.org, Matthias van de Meent <boekewurm+postgres@gmail.com>; Álvaro Herrera <alvherre@kurilemu.de>; Andres Freund <andres@anarazel.de>

On Tue, Nov 17, 2026 at 10:21 AM Zhijie Hou <houzhijie22@gmail.com> wrote:
>
> I think we also need to increase shared_buffers (e.g., to 16MB), because
> $node->init(allows_streaming => 1) sets shared_buffers to 1MB
> internally, causing the pg_class pages to be evicted from shared_buffers
> before CREATE TABLE runs, so the test could not reach the old bug path.

Ah, okay. I'll do that. The original test feels awfully fragile. Is
there something we could do to strengthen it? Like check that one of
the buffers is marked BM_PERMANENT somehow? It seems very trivial to
accidentally eliminate the coverage. And there are of course lots of
ways for those buffers to get evicted.

> BTW, while looking into why the wal_consistency_checking test didn't
> catch this bug, I noticed that verifyBackupPageConsistency() also masks
> out all the bits in VM pages via heap_mask() (somewhat similar to this
> bug, since it treats pd_lower..pd_upper as unused space), so the
> consistency check for VM pages always "passes".  Once we avoid masking
> the VM bitmap there (see the attached draft patch for reference), the
> existing test catches this CREATE DB WAL_LOG bug right away:
>
>    PG_TEST_EXTRA=wal_consistency_checking meson test -C build
> recovery/027_stream_regress
>
> So if it's worth increasing the possibility of catching similar VM bugs,
> maybe we could improve that as well (in a separate patch or thread).

Ha, I didn't realize that wal_consistency_checking wasn't validating
the VM pages at all basically. I have a patch in this thread [1] to
stop masking PD_ALL_VISIBLE on heap pages, which I planned to commit
along with some other VM hardening in the next few weeks. Seems like I
should also do something about masking the whole VM pages themselves
too.

- Melanie

[1] https://www.postgresql.org/message-id/CAAKRu_aRdQ6RjneKeQh9%2BRVMFgMrtjTYFX%3DzDgBdSiQ8JDs6Jg%40mail...






^ permalink  raw  reply  [nested|flat] 30+ messages in thread


end of thread, other threads:[~2026-11-17 23:19 UTC | newest]

Thread overview: 30+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-13 12:31 WAL_LOG CREATE DATABASE strategy broken for non-standard page layouts Matthias van de Meent <boekewurm+postgres@gmail.com>
2024-05-13 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-05-13 14:52   ` Matthias van de Meent <boekewurm+postgres@gmail.com>
2024-05-13 19:43     ` Robert Haas <robertmhaas@gmail.com>
2024-09-14 18:57       ` Akshat Jaimini <destrex271@gmail.com>
2024-12-10 06:25         ` Michael Paquier <michael@paquier.xyz>
2026-02-02 15:26           ` Álvaro Herrera <alvherre@kurilemu.de>
2026-08-23 17:04             ` Rogers Wang <rogers.ww@qq.com>
2026-08-31 05:37               ` Michael Paquier <michael@paquier.xyz>
2026-08-31 15:46                 ` Melanie Plageman <melanieplageman@gmail.com>
2026-08-31 16:08                   ` Matthias van de Meent <boekewurm+postgres@gmail.com>
2026-09-16 14:26                     ` Melanie Plageman <melanieplageman@gmail.com>
2026-09-16 18:32                       ` Melanie Plageman <melanieplageman@gmail.com>
2026-09-11 20:44                   ` Melanie Plageman <melanieplageman@gmail.com>
2026-09-14 15:49                     ` Melanie Plageman <melanieplageman@gmail.com>
2026-09-16 21:59                   ` Robert Haas <robertmhaas@gmail.com>
2026-09-22 23:12                     ` Melanie Plageman <melanieplageman@gmail.com>
2026-10-12 20:06                       ` Melanie Plageman <melanieplageman@gmail.com>
2026-10-14 00:38                         ` surya poondla <suryapoondla4@gmail.com>
2026-11-10 22:25                           ` Melanie Plageman <melanieplageman@gmail.com>
2026-11-11 23:14                             ` surya poondla <suryapoondla4@gmail.com>
2026-11-16 17:37                               ` Melanie Plageman <melanieplageman@gmail.com>
2026-11-16 21:52                                 ` surya poondla <suryapoondla4@gmail.com>
2026-11-17 23:19                                   ` surya poondla <suryapoondla4@gmail.com>
2026-11-06 15:13                         ` Robert Haas <robertmhaas@gmail.com>
2026-11-10 22:10                           ` Melanie Plageman <melanieplageman@gmail.com>
2026-11-14 13:08                             ` Zhijie Hou <houzhijie22@gmail.com>
2026-11-16 17:35                               ` Melanie Plageman <melanieplageman@gmail.com>
2026-11-17 15:20                                 ` Zhijie Hou <houzhijie22@gmail.com>
2026-11-17 22:47                                   ` Melanie Plageman <melanieplageman@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox