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
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');
