Revive "snapshot too old" with wal_level=minimal and SET TABLESPACE.

nmisch · nmisch · commit 7da83415e5bc · 2021-01-30T00:12:18.000-08:00
Given a permanent relation rewritten in the current transaction, the old_snapshot_threshold mechanism assumed the relation had never been subject to early pruning. Hence, a query could fail to report "snapshot too old" when the rewrite followed an early truncation. ALTER TABLE SET TABLESPACE is probably the only rewrite mechanism capable of exposing this bug. REINDEX sets indcheckxmin, avoiding the problem. CLUSTER has zeroed page LSNs since before old_snapshot_threshold existed, so old_snapshot_threshold has never cooperated with it. ALTER TABLE ... SET DATA TYPE makes the table look empty to every past snapshot, which is strictly worse. Back-patch to v13, where commit c6b9204 broke this. Kyotaro Horiguchi and Noah Misch Discussion: https://postgr.es/m/20210113.160705.2225256954956139776.horikyota.ntt@gmail.com
diff --git a/src/backend/utils/time/snapmgr.c b/src/backend/utils/time/snapmgr.c
@@ -1764,7 +1764,11 @@ TransactionIdLimitedForOldSnapshots(TransactionId recentXmin,
 	Assert(OldSnapshotThresholdActive());
 	Assert(limit_ts != NULL && limit_xid != NULL);
 
-	if (!RelationAllowsEarlyPruning(relation))
+	/*
+	 * TestForOldSnapshot() assumes early pruning advances the page LSN, so we
+	 * can't prune early when skipping WAL.
+	 */
+	if (!RelationAllowsEarlyPruning(relation) || !RelationNeedsWAL(relation))
 		return false;
 
 	ts = GetSnapshotCurrentTimestamp();
diff --git a/src/include/utils/snapmgr.h b/src/include/utils/snapmgr.h
@@ -37,7 +37,7 @@
  */
 #define RelationAllowsEarlyPruning(rel) \
 ( \
-	 RelationNeedsWAL(rel) \
+	 (rel)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT	\
   && !IsCatalogRelation(rel) \
   && !RelationIsAccessibleInLogicalDecoding(rel) \
 )

Original file line number	Diff line number	Diff line change
`@@ -37,7 +37,7 @@`
`37`	`37`	`*/`
`38`	`38`	`#define RelationAllowsEarlyPruning(rel) \`
`39`	`39`	`( \`
`40`		`- RelationNeedsWAL(rel) \`
	`40`	`+ (rel)->rd_rel->relpersistence == RELPERSISTENCE_PERMANENT \`
`41`	`41`	`&& !IsCatalogRelation(rel) \`
`42`	`42`	`&& !RelationIsAccessibleInLogicalDecoding(rel) \`
`43`	`43`	`)`