diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index cb0a36a170f..5d1c77e5365 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -15801,6 +15801,12 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
+ pg_waiting_for_safe_snapshot(int)
+ boolean
+ Whether the specified server process ID is waiting for a safe snapshot
+
+
+
session_user
name
session user name
@@ -16068,6 +16074,19 @@ SET search_path TO schema> , schema>, ..
+ pg_waiting_for_safe_snapshot
+
+
+
+ pg_waiting_for_safe_snapshot returns true only if the
+ specified process ID is currently waiting for a safe snapshot. This
+ applies to SERIALIZABLE READ ONLY DEFERRABLE
+ transactions blocked by concurrent SERIALIZABLE
+ transactions. See for more
+ information.
+
+
+
version
diff --git a/src/backend/storage/lmgr/predicate.c b/src/backend/storage/lmgr/predicate.c
index 10bac71e94b..89f4af1f793 100644
--- a/src/backend/storage/lmgr/predicate.c
+++ b/src/backend/storage/lmgr/predicate.c
@@ -1556,6 +1556,32 @@ GetSafeSnapshot(Snapshot origSnapshot)
}
/*
+ * Check if the given process is currently waiting in GetSafeSnapshot.
+ */
+bool
+GetSafeSnapshotIsWaiting(int blocked_pid)
+{
+ SERIALIZABLEXACT *sxact;
+ bool result;
+
+ LWLockAcquire(SerializableXactHashLock, LW_SHARED);
+
+ /* Find blocked_pid's SERIALIZABLEXACT by linear search. */
+ for (sxact = FirstPredXact(); sxact != NULL; sxact = NextPredXact(sxact))
+ {
+ if (sxact->pid == blocked_pid)
+ break;
+ }
+
+ /* Did we find it, and is it currently waiting in GetSafeSnapshot? */
+ result = (sxact != NULL && SxactIsDeferrableWaiting(sxact));
+
+ LWLockRelease(SerializableXactHashLock);
+
+ return result;
+}
+
+/*
* Acquire a snapshot that can be used for the current transaction.
*
* Make sure we have a SERIALIZABLEXACT reference in MySerializableXact.
diff --git a/src/backend/utils/adt/lockfuncs.c b/src/backend/utils/adt/lockfuncs.c
index 63f956e6708..035ce3f06f2 100644
--- a/src/backend/utils/adt/lockfuncs.c
+++ b/src/backend/utils/adt/lockfuncs.c
@@ -516,6 +516,16 @@ pg_blocking_pids(PG_FUNCTION_ARGS)
sizeof(int32), true, 'i'));
}
+/*
+ * pg_waiting_for_safe_snapshot - is a given pid waiting in GetSafeSnapshot?
+ */
+Datum
+pg_waiting_for_safe_snapshot(PG_FUNCTION_ARGS)
+{
+ int blocked_pid = PG_GETARG_INT32(0);
+
+ PG_RETURN_BOOL(GetSafeSnapshotIsWaiting(blocked_pid));
+}
/*
* Functions for manipulating advisory locks
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 643838bb054..fdf34914a2c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3140,6 +3140,8 @@ DATA(insert OID = 1371 ( pg_lock_status PGNSP PGUID 12 1 1000 0 0 f f f f t t
DESCR("view system lock information");
DATA(insert OID = 2561 ( pg_blocking_pids PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 1007 "23" _null_ _null_ _null_ _null_ _null_ pg_blocking_pids _null_ _null_ _null_ ));
DESCR("get array of PIDs of sessions blocking specified backend PID");
+DATA(insert OID = 3376 ( pg_waiting_for_safe_snapshot PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 16 "23" _null_ _null_ _null_ _null_ _null_ pg_waiting_for_safe_snapshot _null_ _null_ _null_ ));
+DESCR("get array of PIDs of sessions blocking specified backend PID while waiting for a safe snapshot");
DATA(insert OID = 1065 ( pg_prepared_xact PGNSP PGUID 12 1 1000 0 0 f f f f t t v s 0 0 2249 "" "{28,25,1184,26,26}" "{o,o,o,o,o}" "{transaction,gid,prepared,ownerid,dbid}" _null_ _null_ pg_prepared_xact _null_ _null_ _null_ ));
DESCR("view two-phase transactions");
DATA(insert OID = 3819 ( pg_get_multixact_members PGNSP PGUID 12 1 1000 0 0 f f f f t t v s 1 0 2249 "28" "{28,28,25}" "{i,o,o}" "{multixid,xid,mode}" _null_ _null_ pg_get_multixact_members _null_ _null_ _null_ ));
diff --git a/src/include/storage/predicate_internals.h b/src/include/storage/predicate_internals.h
index 408d94cc7a5..726010eafc4 100644
--- a/src/include/storage/predicate_internals.h
+++ b/src/include/storage/predicate_internals.h
@@ -474,5 +474,6 @@ typedef struct TwoPhasePredicateRecord
* locking internals.
*/
extern PredicateLockData *GetPredicateLockStatusData(void);
+extern bool GetSafeSnapshotIsWaiting(int blocked_pid);
#endif /* PREDICATE_INTERNALS_H */
diff --git a/src/test/isolation/isolationtester.c b/src/test/isolation/isolationtester.c
index 4d18710bdfd..2b18899039a 100644
--- a/src/test/isolation/isolationtester.c
+++ b/src/test/isolation/isolationtester.c
@@ -231,13 +231,12 @@ main(int argc, char **argv)
appendPQExpBuffer(&wait_query, ",%s", backend_pids[i]);
appendPQExpBufferStr(&wait_query, "}'::integer[]");
- /* Also detect certain wait events. */
+ /*
+ * Also detect waits caused by SERIALIZABLE DEFERRABLE transactions
+ * waiting for all possible conflicting transactions to finish.
+ */
appendPQExpBufferStr(&wait_query,
- " OR EXISTS ("
- " SELECT * "
- " FROM pg_catalog.pg_stat_activity "
- " WHERE pid = $1 "
- " AND wait_event IN ('SafeSnapshot'))");
+ " OR pg_catalog.pg_waiting_for_safe_snapshot($1)");
res = PQprepare(conns[0], PREP_WAITING, wait_query.data, 0, NULL);
if (PQresultStatus(res) != PGRES_COMMAND_OK)