Hi,
Commit 2817525f0 ("Fix rare assertion failure in standby, if primary
is restarted", discussed in "Snapshot related assert failure on
skink") made ExpireOldKnownAssignedTransactionIds() advance
latestCompletedXid and xactCompletionCount.
However, I can still trigger the assertion on standby through a
different path using only ordinary operations on any supported
postgres version, including v19rc2.
TRAP: failed Assert("TransactionIdPrecedesOrEquals(TransactionXmin,
RecentXmin)"), File: "procarray.c", Line: 2446, PID: 52757
LOG: client backend (PID 52757) was terminated by signal 6: Abort trap: 6
Mechanism
ProcArrayApplyRecoveryInfo() starts by calling
ExpireOldKnownAssignedTransactionIds(oldestRunningXid), which now
lifts latestCompletedXid to oldestRunningXid - 1.
If the standby is already STANDBY_SNAPSHOT_READY, the function then
returns early, without touching latestObservedXid. So
latestObservedXid can be left behind latestCompletedXid.
Then:
1. A standby backend takes a snapshot. Its xmin = xmax = the lifted
value (e.g., 931), and so TransactionXmin = 931.
2. The next WAL record with a real xid (931) reaches
RecordKnownAssignedTransactionIds(), which gap-fills KnownAssignedXids
from the old latestObservedXid + 1 (say 731) up to that xid.
3. The next snapshot taken in the same transaction (e.g., a catalog
snapshot) gets xmin 731 < TransactionXmin, and the assertion fires.
Reproduction
The attached pg_standby_xmin_repro.py needs only an install built with
--enable-cassert (no patches, no extensions). It sets up a primary
and a streaming standby, runs the steps above, then opens a REPEATABLE
READ transaction on the standby, creates a table on the primary (first
real xid), and queries it in the open standby transaction.
INST=/path/to/install python3 -I pg_standby_xmin_repro.py
Proposed fix
In the STANDBY_SNAPSHOT_READY early-return path of
ProcArrayApplyRecoveryInfo(), bring latestObservedXid up to
oldestRunningXid - 1 (the value just stored in latestCompletedXid) if
it is behind: extend SUBTRANS over the skipped xids and advance
nextXid past latestObservedXid, as the non-early path of that function
does. KnownAssignedXids is not touched.
Thanks,
Alexey Masterov
#!/usr/bin/env python3
"""Reproduce a hot-standby crash on UNMODIFIED upstream PostgreSQL, using only ordinary operations:
TRAP: failed Assert("TransactionIdPrecedesOrEquals(TransactionXmin, RecentXmin)")
File: "procarray.c", in GetSnapshotData()
No source patch, no extensions. (In builds without asserts there is no crash: the snapshot xmin
silently moves backwards within a transaction.)
Mechanism
---------
ProcArrayApplyRecoveryInfo() calls ExpireOldKnownAssignedTransactionIds(oldestRunningXid), which
lifts latestCompletedXid to oldestRunningXid - 1. If the standby is already STANDBY_SNAPSHOT_READY
it then returns early WITHOUT advancing latestObservedXid. If oldestRunningXid is ahead of
latestObservedXid + 1, an open standby transaction takes a snapshot at the lifted xmax, and the
next WAL record with a real xid gap-fills KnownAssignedXids from the old latestObservedXid + 1.
The next snapshot in that transaction then has xmin < TransactionXmin -> assertion.
How the xid gap is created
--------------------------
1. NSESS primary sessions each take an xid and hold it open: pg_current_xact_id() assigns an
xid but writes no WAL. The (already SNAPSHOT_READY) standby never sees these xids.
2. CHECKPOINT: the checkpoint record's nextXid is now past the held xids.
3. Primary crash (pg_ctl -m immediate stop) + restart: the held transactions vanish without
abort records; crash recovery restarts from that checkpoint with nextXid unchanged, and the
end-of-recovery XLOG_RUNNING_XACTS has oldestRunningXid = nextXid (e.g. 931) while the
standby's latestObservedXid is still ~730. Standby snapshot jumps 731:731 -> 931:931.
4. Open a REPEATABLE READ transaction on the standby (snapshot 931:931).
5. Primary: create table + insert (first real xid, 931) -> standby gap-fills 731..930.
6. In the open standby transaction run a query that needs a fresh catalog snapshot
(select * from t2): xmin 731 < TransactionXmin 931 -> assertion fires.
Requirements
------------
* A PostgreSQL install built with --enable-cassert (pgbench from the same install is used to
hold the open transactions).
Build (example, upstream release candidate 19rc2):
mkdir /tmp/pg19rc2_dl && cd /tmp/pg19rc2_dl
curl -fsSLO https://ftp.postgresql.org/pub/source/v19rc2/postgresql-19rc2.tar.bz2
mkdir /tmp/pg19rc2_src && tar -xjf postgresql-19rc2.tar.bz2 -C /tmp/pg19rc2_src --strip-components=1
mkdir /tmp/pg19rc2_build && cd /tmp/pg19rc2_build
/tmp/pg19rc2_src/configure --prefix=/tmp/pg19rc2_inst --enable-cassert --enable-debug \\
--without-icu --without-readline --without-zlib CFLAGS="-O0 -g"
make -j16 && make install
Run:
INST=/tmp/pg19rc2_inst python3 -I pg_standby_xmin_repro.py
Env knobs: INST (install prefix, required), NSESS (held xids, default 200; must be > 0),
ROOT (work dir, default /tmp/pgrepro_nohack), PP/SP (ports 5450/5451).
Exit code: 2 = bug reproduced (assertion fired), 0 = no crash, 1 = script/setup failure.
Typical output when the bug is present:
standby snapshot after end-of-recovery running-xacts: 931:931:
fresh standby snapshot: 731:932: <- gap-fill
... TRAP: failed Assert("TransactionIdPrecedesOrEquals(TransactionXmin, RecentXmin)") ...
A fix that keeps latestObservedXid in sync gives a fresh standby snapshot of 932:932 and the
query returns normally.
"""
import os
import shutil
import subprocess
import sys
import time
INST = os.environ.get("INST")
if not INST:
sys.exit("set INST=<postgres install prefix> (built with --enable-cassert); see docstring")
BIN = f"{INST}/bin"
ROOT = os.environ.get("ROOT", "/tmp/pgrepro_nohack")
PRIMARY, STANDBY = f"{ROOT}/primary", f"{ROOT}/standby"
PP, SP = int(os.environ.get("PP", "5450")), int(os.environ.get("SP", "5451"))
NSESS = int(os.environ.get("NSESS", "200"))
def sh(*a, check=True):
r = subprocess.run(a, capture_output=True, text=True)
if check and r.returncode:
sys.exit(f"FAILED {a}\n{r.stdout}\n{r.stderr}")
return r
def q(port, sql):
return sh(f"{BIN}/psql", "-h", "127.0.0.1", "-p", str(port), "-U", "postgres", "-d", "postgres",
"-qAtX", "-c", sql).stdout.strip()
def wait_replay(timeout=30):
target = q(PP, "select pg_current_wal_insert_lsn()")
for _ in range(int(timeout / 0.1)):
try:
if q(SP, f"select pg_last_wal_replay_lsn() >= '{target}'::pg_lsn") == "t":
return
except SystemExit:
pass
time.sleep(0.1)
sys.exit("standby did not catch up")
def stop_all():
for d in (STANDBY, PRIMARY):
if os.path.exists(f"{d}/postmaster.pid"):
sh(f"{BIN}/pg_ctl", "-D", d, "-m", "immediate", "stop", check=False)
stop_all() # leftovers from an earlier run
shutil.rmtree(ROOT, ignore_errors=True)
os.makedirs(ROOT)
pgb = None
try:
sh(f"{BIN}/initdb", "-D", PRIMARY, "-U", "postgres", "-A", "trust")
with open(f"{PRIMARY}/postgresql.conf", "a") as f:
f.write(f"port={PP}\nlisten_addresses='127.0.0.1'\nunix_socket_directories='{ROOT}'\n"
"wal_level=replica\nmax_wal_senders=5\nhot_standby=on\nfsync=off\n"
f"max_connections={NSESS + 100}\n")
with open(f"{PRIMARY}/pg_hba.conf", "a") as f:
f.write("host replication all 127.0.0.1/32 trust\n")
sh(f"{BIN}/pg_ctl", "-D", PRIMARY, "-l", f"{ROOT}/primary.log", "-w", "start")
q(PP, "create table t(a int); insert into t select generate_series(1,100)")
sh(f"{BIN}/pg_basebackup", "-D", STANDBY, "-h", "127.0.0.1", "-p", str(PP), "-U", "postgres",
"-R", "-X", "stream")
with open(f"{STANDBY}/postgresql.conf", "a") as f:
f.write(f"port={SP}\nunix_socket_directories='{ROOT}'\n")
sh(f"{BIN}/pg_ctl", "-D", STANDBY, "-l", f"{ROOT}/standby.log", "-w", "start")
wait_replay()
print("standby up; snapshot:", q(SP, "select pg_current_snapshot()"))
# 1. NSESS sessions each take an xid and hold it open; no WAL is written for those xids
with open(f"{ROOT}/hold.sql", "w") as f:
f.write("begin;\nselect pg_current_xact_id();\nselect pg_sleep(600);\n")
pgb = subprocess.Popen([f"{BIN}/pgbench", "-h", "127.0.0.1", "-p", str(PP), "-U", "postgres", "-n",
"-c", str(NSESS), "-j", "4", "-T", "600", "-f", f"{ROOT}/hold.sql", "postgres"],
stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
for _ in range(150):
time.sleep(0.2)
if int(q(PP, "select count(*) from pg_stat_activity where query like '%pg_sleep%' "
"and pid <> pg_backend_pid()")) >= NSESS:
break
time.sleep(1)
# 2. checkpoint: nextXid in the checkpoint record is now past the held xids
q(PP, "checkpoint")
wait_replay()
time.sleep(1)
print("standby snapshot after checkpoint:", q(SP, "select pg_current_snapshot()")[:80])
# 3. crash the primary: the held transactions vanish without abort records
pgb.kill()
pgb = None
sh(f"{BIN}/pg_ctl", "-D", PRIMARY, "-m", "immediate", "-w", "stop")
sh(f"{BIN}/pg_ctl", "-D", PRIMARY, "-l", f"{ROOT}/primary.log", "-w", "start")
print("primary snapshot after crash recovery:", q(PP, "select pg_current_snapshot()"))
for _ in range(20): # the standby reconnects by itself
try:
wait_replay(5)
break
except SystemExit:
time.sleep(0.5)
time.sleep(1)
print("standby snapshot after end-of-recovery running-xacts:", q(SP, "select pg_current_snapshot()")[:80])
# 4. REPEATABLE READ transaction on the standby holds a snapshot at the lifted xmax
vc = subprocess.Popen([f"{BIN}/psql", "-h", "127.0.0.1", "-p", str(SP), "-U", "postgres", "-d", "postgres",
"-qAtX"], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
text=True)
vc.stdin.write("begin isolation level repeatable read;\nselect 'rr snapshot', pg_current_snapshot();\n"
"select count(*) from t;\n")
vc.stdin.flush()
time.sleep(1)
# 5. first real xid on the primary -> gap-fill of KnownAssignedXids on the standby
q(PP, "create table t2(a int); insert into t2 values (1)")
wait_replay()
print("fresh standby snapshot:", q(SP, "select pg_current_snapshot()")[:100])
print("primary snapshot:", q(PP, "select pg_current_snapshot()"))
# 6. the same transaction needs a fresh catalog snapshot -> assertion on the buggy code
vc.stdin.write("select * from t2;\n")
vc.stdin.close()
try:
vc.wait(timeout=20)
except subprocess.TimeoutExpired:
vc.kill()
print("--- RR session output ---\n" + vc.stdout.read())
time.sleep(1)
hits = subprocess.run(["grep", "-nE", "TRAP|Assert|terminated by signal|PANIC", f"{ROOT}/standby.log"],
capture_output=True, text=True).stdout
print("--- standby log (assert lines) ---\n" + hits)
reproduced = "TransactionIdPrecedesOrEquals(TransactionXmin, RecentXmin)" in hits
print("VERDICT:", "BUG REPRODUCED (assertion fired)" if reproduced else "no crash")
finally:
if pgb:
pgb.kill()
stop_all()
sys.exit(2 if reproduced else 0)
procarray_upstream_master.patch
Description: Binary data
