Date: Tue, 28 Jul 2026 06:07:34 +0000
From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Andres Freund <andres@anarazel.de>, scott@scottray.io
Subject: Re: Snapshot export on a standby corrupts hint bits on subxact
 overflow
Message-ID: <amhHJiZyFA9s70Uq@bdtpg>
References: 
 <CAH2-WzmHVeYY=pjz9x8DhhxVjXHX0pvoQ-MdiB1Tt6=o2GTiKg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="G5mbVXPGyfR4obnE"
Content-Disposition: inline
In-Reply-To: 
 <CAH2-WzmHVeYY=pjz9x8DhhxVjXHX0pvoQ-MdiB1Tt6=o2GTiKg@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/amhHJiZyFA9s70Uq%40bdtpg>
Precedence: bulk


--G5mbVXPGyfR4obnE
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hi,

On Sun, Jul 26, 2026 at 10:17:21PM -0400, Peter Geoghegan wrote:
> I decided to reinvestigate the problem today, with help from Claude
> code. I found a bug that exactly matches the known symptoms. Attached
> patch 0001 has a reproducer + draft bug fix. This is likely a bug in
> 2017 commit 6c2003f8. There's also a second patch 0002 that fixes
> another bug found along the way (though that's much less serious than
> the one that 0001 deals with).

Thanks for having looked at it! The first problem is also something I worked
on in the past without success.

> The test case in 0001 shows a scenario where pg_export_snapshot on a
> standby hands out a snapshot that claims no transaction is running,
> which is wrong. A backend that imports it writes wrong hint bits,
> which are then seen by every other session on that standby --
> including sessions that never touched the exported snapshot.
> User-visible symptoms include rows reappearing after deletion, rows
> vanishing after insertion, and duplicate entries in unique indexes
> (all symptoms that I've personally seen in the wild).

The fix in 0001 makes sense to me. 

Some comments:

=== 1

+  if (snapshot->subxcnt + nchildren > GetMaxSnapshotSubxidCount())
+      ereport(ERROR,
+             (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),

Yes, this check is needed here.

+ for (int32 i = 0; i < snapshot->subxcnt; i++)
+      appendStringInfo(&buf, "sxp:%u\n", snapshot->subxip[i]);
+ for (int32 i = 0; i < nchildren; i++)
+      appendStringInfo(&buf, "sxp:%u\n", children[i]);

We count every existing subxip entry and every committed child without filtering
against snapshot->xmax. A committed child created after the imported snapshot
was taken can have an XID >= snapshot->xmax. I think that such an entry is
unnecessary for visibility, since XidInMVCCSnapshot() classifies every XID >= xmax
as still in progress before looking at subxip.

It can be observed by applying the attached post-xmax.txt on top of 0001, which
produces:

# BDT snapshot saved for post-promotion re-export 00000007-00000002-1: sof=1
# BDT re-exported snapshot 00000000-00000004-1: sof=1; xmax=781; sxp=[698, 763, 764, 765, 766, 767, 768, 769, 770, 771, 772, 773, 774, 775, 776, 777, 778, 782]; sxp >= xmax=[782]

We can see that xmax=781, and that 782 has been exported.

That's not a visibility issue, however, those entries count toward GetMaxSnapshotSubxidCount()
and could therefore cause an avoidable PROGRAM_LIMIT_EXCEEDED.

=== 2

> 0002 is a separate bug of the same general nature.

+       if (cur->takenDuringRecovery)
+       {
+               nxip = cur->subxcnt;
+               xip = cur->subxip;
+       }
+       else

SnapshotData.subxip permits entries at or above xmax, whereas pg_snapshot.xip
requires every entry to satisfy xmin <= xip[i] < xmax.

So, I think filtering is appropriate in both places:

1/ In ExportSnapshot(), do not include recovery subxip entries and committed
child XIDs at or above xmax when counting and serializing them, so unnecessary
entries do not consume the limited recovery subxip capacity.

2/ In pg_current_snapshot(), do not include source XIDs outside [xmin, xmax),
so that it enforces the rule regardless of how the source snapshot was produced.

=== 3

0002 can now expose subtransaction IDs, so I think some comments:

"Note that only top-level transaction IDs are exposed to user sessions"
"Note that only top-transaction XIDs are included in the snapshot"

and the docs for pg_current_snapshot() and xip_list need updates to mention the
recovery specific exception.

Regards,

-- 
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

--G5mbVXPGyfR4obnE
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="post-xmax.txt"

diff --git a/src/test/recovery/t/055_standby_snapshot_export.pl b/src/test/recovery/t/055_standby_snapshot_export.pl
index 139190cf231..4cf3e78c766 100644
--- a/src/test/recovery/t/055_standby_snapshot_export.pl
+++ b/src/test/recovery/t/055_standby_snapshot_export.pl
@@ -122,7 +122,6 @@ $s1->query_safe('COMMIT');
 $u->query_safe('COMMIT');
 $primary->safe_psql('postgres',
 	q[INSERT INTO victim VALUES (7, repeat('y', 1200))]);
-$o->query_safe('COMMIT');
 $primary->wait_for_replay_catchup($standby);
 
 # Sessions that never touched the exported snapshot must agree with the
@@ -151,6 +150,13 @@ $s3->query_safe('BEGIN ISOLATION LEVEL REPEATABLE READ');
 my $snap2 = $s3->query_safe('SELECT pg_export_snapshot()');
 my $before = $s3->query_safe('SELECT count(*) FROM victim');
 
+my $file2 = slurp_file($standby->data_dir . "/pg_snapshots/$snap2");
+my ($sof2) = $file2 =~ /^sof:(\d+)$/m;
+note("BDT snapshot saved for post-promotion re-export $snap2: sof=$sof2");
+
+$o->query_safe('COMMIT');
+$primary->wait_for_replay_catchup($standby);
+
 $standby->promote;
 $standby->poll_query_until('postgres', 'SELECT NOT pg_is_in_recovery()')
   or die "standby never finished promotion";
@@ -172,6 +178,16 @@ $s4->query_safe('RELEASE sp');
 my $snap3 = $s4->query_safe('SELECT pg_export_snapshot()');
 
 my $file3 = slurp_file($standby->data_dir . "/pg_snapshots/$snap3");
+my ($sof3) = $file3 =~ /^sof:(\d+)$/m;
+my ($xmax3) = $file3 =~ /^xmax:(\d+)$/m;
+my @subxids3 = $file3 =~ /^sxp:(\d+)$/mg;
+my @post_xmax_subxids3 = grep { $_ >= $xmax3 } @subxids3;
+note(
+	"BDT re-exported snapshot $snap3: sof=$sof3; xmax=$xmax3; sxp=["
+	  . join(', ', @subxids3)
+	  . "]; sxp >= xmax=["
+	  . join(', ', @post_xmax_subxids3)
+	  . "]");
 like($file3, qr/^sxcnt:[1-9]/m,
 	'a recovery-taken snapshot re-exports its subxip array after a write');
 

--G5mbVXPGyfR4obnE--