public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v13 5/6] snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT.
7+ messages / 5 participants
[nested] [flat]
* [PATCH v13 5/6] snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT.
@ 2020-07-15 22:35 Andres Freund <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Andres Freund @ 2020-07-15 22:35 UTC (permalink / raw)
Similar to the previous changes this increases the chance that data
frequently needed by GetSnapshotData() stays in l2 cache. In many
workloads subtransactions are very rare, and this makes the check for
that considerably cheaper.
As this removes the last member of PGXACT, there is no need to keep it
around anymore.
Author: Andres Freund
Reviewed-By: Robert Haas, Thomas Munro, David Rowley
Discussion: https://postgr.es/m/[email protected]
---
src/include/storage/proc.h | 34 ++++---
src/backend/access/transam/clog.c | 7 +-
src/backend/access/transam/twophase.c | 17 ++--
src/backend/access/transam/varsup.c | 15 ++-
src/backend/storage/ipc/procarray.c | 128 ++++++++++++++------------
src/backend/storage/lmgr/proc.c | 24 +----
src/tools/pgindent/typedefs.list | 1 -
7 files changed, 113 insertions(+), 113 deletions(-)
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index ea95cf92402..43aa234709e 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -35,6 +35,14 @@
*/
#define PGPROC_MAX_CACHED_SUBXIDS 64 /* XXX guessed-at value */
+typedef struct XidCacheStatus
+{
+ /* number of cached subxids, never more than PGPROC_MAX_CACHED_SUBXIDS */
+ uint8 count;
+ /* has PGPROC->subxids overflowed */
+ bool overflowed;
+} XidCacheStatus;
+
struct XidCache
{
TransactionId xids[PGPROC_MAX_CACHED_SUBXIDS];
@@ -187,6 +195,8 @@ struct PGPROC
*/
SHM_QUEUE myProcLocks[NUM_LOCK_PARTITIONS];
+ XidCacheStatus subxidStatus; /* mirrored with
+ * ProcGlobal->subxidStates[i] */
struct XidCache subxids; /* cache for subtransaction XIDs */
/* Support for group XID clearing. */
@@ -235,22 +245,6 @@ struct PGPROC
extern PGDLLIMPORT PGPROC *MyProc;
-extern PGDLLIMPORT struct PGXACT *MyPgXact;
-
-/*
- * Prior to PostgreSQL 9.2, the fields below were stored as part of the
- * PGPROC. However, benchmarking revealed that packing these particular
- * members into a separate array as tightly as possible sped up GetSnapshotData
- * considerably on systems with many CPU cores, by reducing the number of
- * cache lines needing to be fetched. Thus, think very carefully before adding
- * anything else here.
- */
-typedef struct PGXACT
-{
- bool overflowed;
-
- uint8 nxids;
-} PGXACT;
/*
* There is one ProcGlobal struct for the whole database cluster.
@@ -310,12 +304,16 @@ typedef struct PROC_HDR
{
/* Array of PGPROC structures (not including dummies for prepared txns) */
PGPROC *allProcs;
- /* Array of PGXACT structures (not including dummies for prepared txns) */
- PGXACT *allPgXact;
/* Array mirroring PGPROC.xid for each PGPROC currently in the procarray */
TransactionId *xids;
+ /*
+ * Array mirroring PGPROC.subxidStatus for each PGPROC currently in the
+ * procarray.
+ */
+ XidCacheStatus *subxidStates;
+
/*
* Array mirroring PGPROC.vacuumFlags for each PGPROC currently in the
* procarray.
diff --git a/src/backend/access/transam/clog.c b/src/backend/access/transam/clog.c
index a4599e96610..65aa8841f7c 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -295,7 +295,7 @@ TransactionIdSetPageStatus(TransactionId xid, int nsubxids,
*/
if (all_xact_same_page && xid == MyProc->xid &&
nsubxids <= THRESHOLD_SUBTRANS_CLOG_OPT &&
- nsubxids == MyPgXact->nxids &&
+ nsubxids == MyProc->subxidStatus.count &&
memcmp(subxids, MyProc->subxids.xids,
nsubxids * sizeof(TransactionId)) == 0)
{
@@ -510,16 +510,15 @@ TransactionGroupUpdateXidStatus(TransactionId xid, XidStatus status,
while (nextidx != INVALID_PGPROCNO)
{
PGPROC *proc = &ProcGlobal->allProcs[nextidx];
- PGXACT *pgxact = &ProcGlobal->allPgXact[nextidx];
/*
* Transactions with more than THRESHOLD_SUBTRANS_CLOG_OPT sub-XIDs
* should not use group XID status update mechanism.
*/
- Assert(pgxact->nxids <= THRESHOLD_SUBTRANS_CLOG_OPT);
+ Assert(proc->subxidStatus.count <= THRESHOLD_SUBTRANS_CLOG_OPT);
TransactionIdSetPageStatusInternal(proc->clogGroupMemberXid,
- pgxact->nxids,
+ proc->subxidStatus.count,
proc->subxids.xids,
proc->clogGroupMemberXidStatus,
proc->clogGroupMemberLsn,
diff --git a/src/backend/access/transam/twophase.c b/src/backend/access/transam/twophase.c
index 744b8a7f393..76465ad2c8b 100644
--- a/src/backend/access/transam/twophase.c
+++ b/src/backend/access/transam/twophase.c
@@ -21,9 +21,9 @@
* GIDs and aborts the transaction if there already is a global
* transaction in prepared state with the same GID.
*
- * A global transaction (gxact) also has dummy PGXACT and PGPROC; this is
- * what keeps the XID considered running by TransactionIdIsInProgress.
- * It is also convenient as a PGPROC to hook the gxact's locks to.
+ * A global transaction (gxact) also has dummy PGPROC; this is what keeps
+ * the XID considered running by TransactionIdIsInProgress. It is also
+ * convenient as a PGPROC to hook the gxact's locks to.
*
* Information to recover prepared transactions in case of crash is
* now stored in WAL for the common case. In some cases there will be
@@ -447,14 +447,12 @@ MarkAsPreparingGuts(GlobalTransaction gxact, TransactionId xid, const char *gid,
TimestampTz prepared_at, Oid owner, Oid databaseid)
{
PGPROC *proc;
- PGXACT *pgxact;
int i;
Assert(LWLockHeldByMeInMode(TwoPhaseStateLock, LW_EXCLUSIVE));
Assert(gxact != NULL);
proc = &ProcGlobal->allProcs[gxact->pgprocno];
- pgxact = &ProcGlobal->allPgXact[gxact->pgprocno];
/* Initialize the PGPROC entry */
MemSet(proc, 0, sizeof(PGPROC));
@@ -480,8 +478,8 @@ MarkAsPreparingGuts(GlobalTransaction gxact, TransactionId xid, const char *gid,
for (i = 0; i < NUM_LOCK_PARTITIONS; i++)
SHMQueueInit(&(proc->myProcLocks[i]));
/* subxid data must be filled later by GXactLoadSubxactData */
- pgxact->overflowed = false;
- pgxact->nxids = 0;
+ proc->subxidStatus.count = 0;
+ proc->subxidStatus.overflowed = 0;
gxact->prepared_at = prepared_at;
gxact->xid = xid;
@@ -510,19 +508,18 @@ GXactLoadSubxactData(GlobalTransaction gxact, int nsubxacts,
TransactionId *children)
{
PGPROC *proc = &ProcGlobal->allProcs[gxact->pgprocno];
- PGXACT *pgxact = &ProcGlobal->allPgXact[gxact->pgprocno];
/* We need no extra lock since the GXACT isn't valid yet */
if (nsubxacts > PGPROC_MAX_CACHED_SUBXIDS)
{
- pgxact->overflowed = true;
+ proc->subxidStatus.overflowed = true;
nsubxacts = PGPROC_MAX_CACHED_SUBXIDS;
}
if (nsubxacts > 0)
{
memcpy(proc->subxids.xids, children,
nsubxacts * sizeof(TransactionId));
- pgxact->nxids = nsubxacts;
+ proc->subxidStatus.count = PGPROC_MAX_CACHED_SUBXIDS;
}
}
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 4c91b343ecd..2d2b05be36c 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -222,22 +222,31 @@ GetNewTransactionId(bool isSubXact)
*/
if (!isSubXact)
{
+ Assert(ProcGlobal->subxidStates[MyProc->pgxactoff].count == 0);
+ Assert(!ProcGlobal->subxidStates[MyProc->pgxactoff].overflowed);
+ Assert(MyProc->subxidStatus.count == 0);
+ Assert(!MyProc->subxidStatus.overflowed);
+
/* LWLockRelease acts as barrier */
MyProc->xid = xid;
ProcGlobal->xids[MyProc->pgxactoff] = xid;
}
else
{
- int nxids = MyPgXact->nxids;
+ XidCacheStatus *substat = &ProcGlobal->subxidStates[MyProc->pgxactoff];
+ int nxids = MyProc->subxidStatus.count;
+
+ Assert(substat->count == MyProc->subxidStatus.count);
+ Assert(substat->overflowed == MyProc->subxidStatus.overflowed);
if (nxids < PGPROC_MAX_CACHED_SUBXIDS)
{
MyProc->subxids.xids[nxids] = xid;
pg_write_barrier();
- MyPgXact->nxids = nxids + 1;
+ MyProc->subxidStatus.count = substat->count = nxids + 1;
}
else
- MyPgXact->overflowed = true;
+ MyProc->subxidStatus.overflowed = substat->overflowed = true;
}
LWLockRelease(XidGenLock);
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index e77d4e44fb8..8e8049d9715 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -4,9 +4,10 @@
* POSTGRES process array code.
*
*
- * This module maintains arrays of the PGPROC and PGXACT structures for all
- * active backends. Although there are several uses for this, the principal
- * one is as a means of determining the set of currently running transactions.
+ * This module maintains arrays of PGPROC substructures, as well as associated
+ * arrays in ProcGlobal, for all active backends. Although there are several
+ * uses for this, the principal one is as a means of determining the set of
+ * currently running transactions.
*
* Because of various subtle race conditions it is critical that a backend
* hold the correct locks while setting or clearing its xid (in
@@ -85,7 +86,7 @@ typedef struct ProcArrayStruct
/*
* Highest subxid that has been removed from KnownAssignedXids array to
* prevent overflow; or InvalidTransactionId if none. We track this for
- * similar reasons to tracking overflowing cached subxids in PGXACT
+ * similar reasons to tracking overflowing cached subxids in PGPROC
* entries. Must hold exclusive ProcArrayLock to change this, and shared
* lock to read it.
*/
@@ -96,7 +97,7 @@ typedef struct ProcArrayStruct
/* oldest catalog xmin of any replication slot */
TransactionId replication_slot_catalog_xmin;
- /* indexes into allPgXact[], has PROCARRAY_MAXPROCS entries */
+ /* indexes into allProcs[], has PROCARRAY_MAXPROCS entries */
int pgprocnos[FLEXIBLE_ARRAY_MEMBER];
} ProcArrayStruct;
@@ -239,7 +240,6 @@ typedef struct ComputeXidHorizonsResult
static ProcArrayStruct *procArray;
static PGPROC *allProcs;
-static PGXACT *allPgXact;
/*
* Bookkeeping for tracking emulated transactions in recovery
@@ -325,8 +325,7 @@ static int KnownAssignedXidsGetAndSetXmin(TransactionId *xarray,
static TransactionId KnownAssignedXidsGetOldestXmin(void);
static void KnownAssignedXidsDisplay(int trace_level);
static void KnownAssignedXidsReset(void);
-static inline void ProcArrayEndTransactionInternal(PGPROC *proc,
- PGXACT *pgxact, TransactionId latestXid);
+static inline void ProcArrayEndTransactionInternal(PGPROC *proc, TransactionId latestXid);
static void ProcArrayGroupClearXid(PGPROC *proc, TransactionId latestXid);
static void MaintainLatestCompletedXid(TransactionId latestXid);
static void MaintainLatestCompletedXidRecovery(TransactionId latestXid);
@@ -411,7 +410,6 @@ CreateSharedProcArray(void)
}
allProcs = ProcGlobal->allProcs;
- allPgXact = ProcGlobal->allPgXact;
/* Create or attach to the KnownAssignedXids arrays too, if needed */
if (EnableHotStandby)
@@ -476,11 +474,14 @@ ProcArrayAdd(PGPROC *proc)
(arrayP->numProcs - index) * sizeof(*arrayP->pgprocnos));
memmove(&ProcGlobal->xids[index + 1], &ProcGlobal->xids[index],
(arrayP->numProcs - index) * sizeof(*ProcGlobal->xids));
+ memmove(&ProcGlobal->subxidStates[index + 1], &ProcGlobal->subxidStates[index],
+ (arrayP->numProcs - index) * sizeof(*ProcGlobal->subxidStates));
memmove(&ProcGlobal->vacuumFlags[index + 1], &ProcGlobal->vacuumFlags[index],
(arrayP->numProcs - index) * sizeof(*ProcGlobal->vacuumFlags));
arrayP->pgprocnos[index] = proc->pgprocno;
ProcGlobal->xids[index] = proc->xid;
+ ProcGlobal->subxidStates[index] = proc->subxidStatus;
ProcGlobal->vacuumFlags[index] = proc->vacuumFlags;
arrayP->numProcs++;
@@ -534,6 +535,8 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
MaintainLatestCompletedXid(latestXid);
ProcGlobal->xids[proc->pgxactoff] = 0;
+ ProcGlobal->subxidStates[proc->pgxactoff].overflowed = false;
+ ProcGlobal->subxidStates[proc->pgxactoff].count = 0;
}
else
{
@@ -542,6 +545,8 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
}
Assert(TransactionIdIsValid(ProcGlobal->xids[proc->pgxactoff] == 0));
+ Assert(TransactionIdIsValid(ProcGlobal->subxidStates[proc->pgxactoff].count == 0));
+ Assert(TransactionIdIsValid(ProcGlobal->subxidStates[proc->pgxactoff].overflowed == false));
ProcGlobal->vacuumFlags[proc->pgxactoff] = 0;
for (index = 0; index < arrayP->numProcs; index++)
@@ -553,6 +558,8 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
(arrayP->numProcs - index - 1) * sizeof(*arrayP->pgprocnos));
memmove(&ProcGlobal->xids[index], &ProcGlobal->xids[index + 1],
(arrayP->numProcs - index - 1) * sizeof(*ProcGlobal->xids));
+ memmove(&ProcGlobal->subxidStates[index], &ProcGlobal->subxidStates[index + 1],
+ (arrayP->numProcs - index - 1) * sizeof(*ProcGlobal->subxidStates));
memmove(&ProcGlobal->vacuumFlags[index], &ProcGlobal->vacuumFlags[index + 1],
(arrayP->numProcs - index - 1) * sizeof(*ProcGlobal->vacuumFlags));
@@ -598,8 +605,6 @@ ProcArrayRemove(PGPROC *proc, TransactionId latestXid)
void
ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
{
- PGXACT *pgxact = &allPgXact[proc->pgprocno];
-
if (TransactionIdIsValid(latestXid))
{
/*
@@ -617,7 +622,7 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
*/
if (LWLockConditionalAcquire(ProcArrayLock, LW_EXCLUSIVE))
{
- ProcArrayEndTransactionInternal(proc, pgxact, latestXid);
+ ProcArrayEndTransactionInternal(proc, latestXid);
LWLockRelease(ProcArrayLock);
}
else
@@ -631,15 +636,14 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
* estimate of global xmin, but that's OK.
*/
Assert(!TransactionIdIsValid(proc->xid));
+ Assert(proc->subxidStatus.count == 0);
+ Assert(!proc->subxidStatus.overflowed);
proc->lxid = InvalidLocalTransactionId;
proc->xmin = InvalidTransactionId;
proc->delayChkpt = false; /* be sure this is cleared in abort */
proc->recoveryConflictPending = false;
- Assert(pgxact->nxids == 0);
- Assert(pgxact->overflowed == false);
-
/* must be cleared with xid/xmin: */
/* avoid unnecessarily dirtying shared cachelines */
if (proc->vacuumFlags & PROC_VACUUM_STATE_MASK)
@@ -660,8 +664,7 @@ ProcArrayEndTransaction(PGPROC *proc, TransactionId latestXid)
* We don't do any locking here; caller must handle that.
*/
static inline void
-ProcArrayEndTransactionInternal(PGPROC *proc, PGXACT *pgxact,
- TransactionId latestXid)
+ProcArrayEndTransactionInternal(PGPROC *proc, TransactionId latestXid)
{
size_t pgxactoff = proc->pgxactoff;
@@ -684,8 +687,15 @@ ProcArrayEndTransactionInternal(PGPROC *proc, PGXACT *pgxact,
}
/* Clear the subtransaction-XID cache too while holding the lock */
- pgxact->nxids = 0;
- pgxact->overflowed = false;
+ Assert(ProcGlobal->subxidStates[pgxactoff].count == proc->subxidStatus.count &&
+ ProcGlobal->subxidStates[pgxactoff].overflowed == proc->subxidStatus.overflowed);
+ if (proc->subxidStatus.count > 0 || proc->subxidStatus.overflowed)
+ {
+ ProcGlobal->subxidStates[pgxactoff].count = 0;
+ ProcGlobal->subxidStates[pgxactoff].overflowed = false;
+ proc->subxidStatus.count = 0;
+ proc->subxidStatus.overflowed = false;
+ }
/* Also advance global latestCompletedXid while holding the lock */
MaintainLatestCompletedXid(latestXid);
@@ -775,9 +785,8 @@ ProcArrayGroupClearXid(PGPROC *proc, TransactionId latestXid)
while (nextidx != INVALID_PGPROCNO)
{
PGPROC *proc = &allProcs[nextidx];
- PGXACT *pgxact = &allPgXact[nextidx];
- ProcArrayEndTransactionInternal(proc, pgxact, proc->procArrayGroupMemberXid);
+ ProcArrayEndTransactionInternal(proc, proc->procArrayGroupMemberXid);
/* Move to next proc in list. */
nextidx = pg_atomic_read_u32(&proc->procArrayGroupNext);
@@ -821,7 +830,6 @@ ProcArrayGroupClearXid(PGPROC *proc, TransactionId latestXid)
void
ProcArrayClearTransaction(PGPROC *proc)
{
- PGXACT *pgxact = &allPgXact[proc->pgprocno];
size_t pgxactoff;
/*
@@ -846,8 +854,15 @@ ProcArrayClearTransaction(PGPROC *proc)
Assert(!proc->delayChkpt);
/* Clear the subtransaction-XID cache too */
- pgxact->nxids = 0;
- pgxact->overflowed = false;
+ Assert(ProcGlobal->subxidStates[pgxactoff].count == proc->subxidStatus.count &&
+ ProcGlobal->subxidStates[pgxactoff].overflowed == proc->subxidStatus.overflowed);
+ if (proc->subxidStatus.count > 0 || proc->subxidStatus.overflowed)
+ {
+ ProcGlobal->subxidStates[pgxactoff].count = 0;
+ ProcGlobal->subxidStates[pgxactoff].overflowed = false;
+ proc->subxidStatus.count = 0;
+ proc->subxidStatus.overflowed = false;
+ }
LWLockRelease(ProcArrayLock);
}
@@ -1268,6 +1283,7 @@ TransactionIdIsInProgress(TransactionId xid)
{
static TransactionId *xids = NULL;
static TransactionId *other_xids;
+ XidCacheStatus *other_subxidstates;
int nxids = 0;
ProcArrayStruct *arrayP = procArray;
TransactionId topxid;
@@ -1330,6 +1346,7 @@ TransactionIdIsInProgress(TransactionId xid)
}
other_xids = ProcGlobal->xids;
+ other_subxidstates = ProcGlobal->subxidStates;
LWLockAcquire(ProcArrayLock, LW_SHARED);
@@ -1352,7 +1369,6 @@ TransactionIdIsInProgress(TransactionId xid)
for (size_t pgxactoff = 0; pgxactoff < numProcs; pgxactoff++)
{
int pgprocno;
- PGXACT *pgxact;
PGPROC *proc;
TransactionId pxid;
int pxids;
@@ -1387,9 +1403,7 @@ TransactionIdIsInProgress(TransactionId xid)
/*
* Step 2: check the cached child-Xids arrays
*/
- pgprocno = arrayP->pgprocnos[pgxactoff];
- pgxact = &allPgXact[pgprocno];
- pxids = pgxact->nxids;
+ pxids = other_subxidstates[pgxactoff].count;
pg_read_barrier(); /* pairs with barrier in GetNewTransactionId() */
pgprocno = arrayP->pgprocnos[pgxactoff];
proc = &allProcs[pgprocno];
@@ -1413,7 +1427,7 @@ TransactionIdIsInProgress(TransactionId xid)
* we hold ProcArrayLock. So we can't miss an Xid that we need to
* worry about.)
*/
- if (pgxact->overflowed)
+ if (other_subxidstates[pgxactoff].overflowed)
xids[nxids++] = pxid;
}
@@ -2017,6 +2031,7 @@ GetSnapshotData(Snapshot snapshot)
size_t numProcs = arrayP->numProcs;
TransactionId *xip = snapshot->xip;
int *pgprocnos = arrayP->pgprocnos;
+ XidCacheStatus *subxidStates = ProcGlobal->subxidStates;
uint8 *allVacuumFlags = ProcGlobal->vacuumFlags;
/*
@@ -2093,17 +2108,16 @@ GetSnapshotData(Snapshot snapshot)
*/
if (!suboverflowed)
{
- int pgprocno = pgprocnos[pgxactoff];
- PGXACT *pgxact = &allPgXact[pgprocno];
- if (pgxact->overflowed)
+ if (subxidStates[pgxactoff].overflowed)
suboverflowed = true;
else
{
- int nsubxids = pgxact->nxids;
+ int nsubxids = subxidStates[pgxactoff].count;
if (nsubxids > 0)
{
+ int pgprocno = pgprocnos[pgxactoff];
PGPROC *proc = &allProcs[pgprocno];
pg_read_barrier(); /* pairs with GetNewTransactionId */
@@ -2496,8 +2510,6 @@ GetRunningTransactionData(void)
*/
for (index = 0; index < arrayP->numProcs; index++)
{
- int pgprocno = arrayP->pgprocnos[index];
- PGXACT *pgxact = &allPgXact[pgprocno];
TransactionId xid;
/* Fetch xid just once - see GetNewTransactionId */
@@ -2518,7 +2530,7 @@ GetRunningTransactionData(void)
if (TransactionIdPrecedes(xid, oldestRunningXid))
oldestRunningXid = xid;
- if (pgxact->overflowed)
+ if (ProcGlobal->subxidStates[index].overflowed)
suboverflowed = true;
/*
@@ -2538,27 +2550,28 @@ GetRunningTransactionData(void)
*/
if (!suboverflowed)
{
+ XidCacheStatus *other_subxidstates = ProcGlobal->subxidStates;
+
for (index = 0; index < arrayP->numProcs; index++)
{
int pgprocno = arrayP->pgprocnos[index];
PGPROC *proc = &allProcs[pgprocno];
- PGXACT *pgxact = &allPgXact[pgprocno];
- int nxids;
+ int nsubxids;
/*
* Save subtransaction XIDs. Other backends can't add or remove
* entries while we're holding XidGenLock.
*/
- nxids = pgxact->nxids;
- if (nxids > 0)
+ nsubxids = other_subxidstates[index].count;
+ if (nsubxids > 0)
{
/* barrier not really required, as XidGenLock is held, but ... */
pg_read_barrier(); /* pairs with GetNewTransactionId */
memcpy(&xids[count], (void *) proc->subxids.xids,
- nxids * sizeof(TransactionId));
- count += nxids;
- subcount += nxids;
+ nsubxids * sizeof(TransactionId));
+ count += nsubxids;
+ subcount += nsubxids;
/*
* Top-level XID of a transaction is always less than any of
@@ -3625,14 +3638,6 @@ ProcArrayGetReplicationSlotXmin(TransactionId *xmin,
LWLockRelease(ProcArrayLock);
}
-
-#define XidCacheRemove(i) \
- do { \
- MyProc->subxids.xids[i] = MyProc->subxids.xids[MyPgXact->nxids - 1]; \
- pg_write_barrier(); \
- MyPgXact->nxids--; \
- } while (0)
-
/*
* XidCacheRemoveRunningXids
*
@@ -3648,6 +3653,7 @@ XidCacheRemoveRunningXids(TransactionId xid,
{
int i,
j;
+ XidCacheStatus *mysubxidstat;
Assert(TransactionIdIsValid(xid));
@@ -3665,6 +3671,8 @@ XidCacheRemoveRunningXids(TransactionId xid,
*/
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
+ mysubxidstat = &ProcGlobal->subxidStates[MyProc->pgxactoff];
+
/*
* Under normal circumstances xid and xids[] will be in increasing order,
* as will be the entries in subxids. Scan backwards to avoid O(N^2)
@@ -3674,11 +3682,14 @@ XidCacheRemoveRunningXids(TransactionId xid,
{
TransactionId anxid = xids[i];
- for (j = MyPgXact->nxids - 1; j >= 0; j--)
+ for (j = MyProc->subxidStatus.count - 1; j >= 0; j--)
{
if (TransactionIdEquals(MyProc->subxids.xids[j], anxid))
{
- XidCacheRemove(j);
+ MyProc->subxids.xids[j] = MyProc->subxids.xids[MyProc->subxidStatus.count - 1];
+ pg_write_barrier();
+ mysubxidstat->count--;
+ MyProc->subxidStatus.count--;
break;
}
}
@@ -3690,20 +3701,23 @@ XidCacheRemoveRunningXids(TransactionId xid,
* error during AbortSubTransaction. So instead of Assert, emit a
* debug warning.
*/
- if (j < 0 && !MyPgXact->overflowed)
+ if (j < 0 && !MyProc->subxidStatus.overflowed)
elog(WARNING, "did not find subXID %u in MyProc", anxid);
}
- for (j = MyPgXact->nxids - 1; j >= 0; j--)
+ for (j = MyProc->subxidStatus.count - 1; j >= 0; j--)
{
if (TransactionIdEquals(MyProc->subxids.xids[j], xid))
{
- XidCacheRemove(j);
+ MyProc->subxids.xids[j] = MyProc->subxids.xids[MyProc->subxidStatus.count - 1];
+ pg_write_barrier();
+ mysubxidstat->count--;
+ MyProc->subxidStatus.count--;
break;
}
}
/* Ordinarily we should have found it, unless the cache has overflowed */
- if (j < 0 && !MyPgXact->overflowed)
+ if (j < 0 && !MyProc->subxidStatus.overflowed)
elog(WARNING, "did not find subXID %u in MyProc", xid);
/* Also advance global latestCompletedXid while holding the lock */
diff --git a/src/backend/storage/lmgr/proc.c b/src/backend/storage/lmgr/proc.c
index f6113b2d243..aa9fbd80545 100644
--- a/src/backend/storage/lmgr/proc.c
+++ b/src/backend/storage/lmgr/proc.c
@@ -63,9 +63,8 @@ int LockTimeout = 0;
int IdleInTransactionSessionTimeout = 0;
bool log_lock_waits = false;
-/* Pointer to this process's PGPROC and PGXACT structs, if any */
+/* Pointer to this process's PGPROC struct, if any */
PGPROC *MyProc = NULL;
-PGXACT *MyPgXact = NULL;
/*
* This spinlock protects the freelist of recycled PGPROC structures.
@@ -110,10 +109,8 @@ ProcGlobalShmemSize(void)
size = add_size(size, mul_size(TotalProcs, sizeof(PGPROC)));
size = add_size(size, sizeof(slock_t));
- size = add_size(size, mul_size(MaxBackends, sizeof(PGXACT)));
- size = add_size(size, mul_size(NUM_AUXILIARY_PROCS, sizeof(PGXACT)));
- size = add_size(size, mul_size(max_prepared_xacts, sizeof(PGXACT)));
size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->xids)));
+ size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->subxidStates)));
size = add_size(size, mul_size(TotalProcs, sizeof(*ProcGlobal->vacuumFlags)));
return size;
@@ -161,7 +158,6 @@ void
InitProcGlobal(void)
{
PGPROC *procs;
- PGXACT *pgxacts;
int i,
j;
bool found;
@@ -202,18 +198,6 @@ InitProcGlobal(void)
/* XXX allProcCount isn't really all of them; it excludes prepared xacts */
ProcGlobal->allProcCount = MaxBackends + NUM_AUXILIARY_PROCS;
- /*
- * Also allocate a separate array of PGXACT structures. This is separate
- * from the main PGPROC array so that the most heavily accessed data is
- * stored contiguously in memory in as few cache lines as possible. This
- * provides significant performance benefits, especially on a
- * multiprocessor system. There is one PGXACT structure for every PGPROC
- * structure.
- */
- pgxacts = (PGXACT *) ShmemAlloc(TotalProcs * sizeof(PGXACT));
- MemSet(pgxacts, 0, TotalProcs * sizeof(PGXACT));
- ProcGlobal->allPgXact = pgxacts;
-
/*
* Allocate arrays mirroring PGPROC fields in a dense manner. See
* PROC_HDR.
@@ -224,6 +208,8 @@ InitProcGlobal(void)
ProcGlobal->xids =
(TransactionId *) ShmemAlloc(TotalProcs * sizeof(*ProcGlobal->xids));
MemSet(ProcGlobal->xids, 0, TotalProcs * sizeof(*ProcGlobal->xids));
+ ProcGlobal->subxidStates = (XidCacheStatus *) ShmemAlloc(TotalProcs * sizeof(*ProcGlobal->subxidStates));
+ MemSet(ProcGlobal->subxidStates, 0, TotalProcs * sizeof(*ProcGlobal->subxidStates));
ProcGlobal->vacuumFlags = (uint8 *) ShmemAlloc(TotalProcs * sizeof(*ProcGlobal->vacuumFlags));
MemSet(ProcGlobal->vacuumFlags, 0, TotalProcs * sizeof(*ProcGlobal->vacuumFlags));
@@ -372,7 +358,6 @@ InitProcess(void)
(errcode(ERRCODE_TOO_MANY_CONNECTIONS),
errmsg("sorry, too many clients already")));
}
- MyPgXact = &ProcGlobal->allPgXact[MyProc->pgprocno];
/*
* Cross-check that the PGPROC is of the type we expect; if this were not
@@ -569,7 +554,6 @@ InitAuxiliaryProcess(void)
((volatile PGPROC *) auxproc)->pid = MyProcPid;
MyProc = auxproc;
- MyPgXact = &ProcGlobal->allPgXact[auxproc->pgprocno];
SpinLockRelease(ProcStructLock);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4948ac675f..3d990463ce9 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1536,7 +1536,6 @@ PGSetenvStatusType
PGShmemHeader
PGTransactionStatusType
PGVerbosity
-PGXACT
PG_Locale_Strategy
PG_Lock_Status
PG_init_t
--
2.25.0.114.g5b0ca878e0
--4il4azyv6rmablac
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v13-0006-snapshot-scalability-cache-snapshots-using-a-xac.patch"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-03-05 19:12 Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2025-03-05 19:12 UTC (permalink / raw)
To: Jeff Davis <[email protected]>; +Cc: Sami Imseih <[email protected]>; dinesh salve <[email protected]>; pgsql-hackers
Jeff Davis <[email protected]> writes:
> Ideally, we'd have EXPLAIN ANALYZE return two result sets, kind of like
> how a query with a semicolon returns two result sets. That changes the
> expected message flow for EXPLAIN ANALYZE, though, so we'd need a new
> option so we are sure the client is expecting it (is this a sane
> idea?).
I'm afraid not. That pretty fundamentally breaks the wire protocol,
I think. Also (1) there could be more than two, no, if the query
touches more than one foreign table? How would the client know
how many to expect? (2) there would be no particularly compelling
ordering for the multiple resultsets.
> I wonder if Robert's extensible EXPLAIN work[1] could be useful
> here?
I'm wondering the same. You could certainly imagine cramming
all of the foreign EXPLAIN output into some new field attached
to the ForeignScan node. Readability and indentation would
require some thought, but the other approaches don't have any
mechanism for addressing that at all.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-03-05 23:24 Jeff Davis <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Jeff Davis @ 2025-03-05 23:24 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Sami Imseih <[email protected]>; dinesh salve <[email protected]>; pgsql-hackers
On Wed, 2025-03-05 at 14:12 -0500, Tom Lane wrote:
> I'm afraid not. That pretty fundamentally breaks the wire protocol,
> I think.
The extended protocol docs say: "The possible responses to Execute are
the same as those described above for queries issued via simple query
protocol, except that Execute doesn't cause ReadyForQuery or
RowDescription to be issued."
Each result set needs a RowDescription, so I think you're right that it
breaks the extended protocol. I missed that the first time.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-11-18 03:25 dinesh salve <[email protected]>
parent: Jeff Davis <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: dinesh salve @ 2025-11-18 03:25 UTC (permalink / raw)
To: Sami Imseih <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi Sami,
Thanks for the feedback. I have refactored the commit on the latest version
of PG and added a few more tests. To simplify the roll out of this feature,
I decided to work on analyze=false use case first. Please find the attached
patch for the same.
Regards,
Dinesh
On Thu, Mar 6, 2025 at 4:54 AM Jeff Davis <[email protected]> wrote:
> On Wed, 2025-03-05 at 14:12 -0500, Tom Lane wrote:
> > I'm afraid not. That pretty fundamentally breaks the wire protocol,
> > I think.
>
> The extended protocol docs say: "The possible responses to Execute are
> the same as those described above for queries issued via simple query
> protocol, except that Execute doesn't cause ReadyForQuery or
> RowDescription to be issued."
>
> Each result set needs a RowDescription, so I think you're right that it
> breaks the extended protocol. I missed that the first time.
>
> Regards,
> Jeff Davis
>
>
--
Regards,
Dinesh Salve
Cell:- 91 8125898845
Attachments:
[application/octet-stream] 0001-This-change-adds-capability-to-fetch-explain-plans-f.patch (59.1K, ../../CAP+B4TDxE3gbrixL9qR9F+CK_N_N0-HmXSL55QXVZNAorRHSHQ@mail.gmail.com/3-0001-This-change-adds-capability-to-fetch-explain-plans-f.patch)
download | inline diff:
From b5908a7ac4ea6f40493cb0d5638d6a254a4fb768 Mon Sep 17 00:00:00 2001
From: Dinesh Salve <[email protected]>
Date: Wed, 5 Nov 2025 05:49:11 +0000
Subject: [PATCH 1/1] This change adds capability to fetch explain plans for
foreign tables. We have introduced new option "remote_plans" to achieve the
same. This option does not work with ANALYZE option yet.
---
.../postgres_fdw/expected/postgres_fdw.out | 581 ++++++++++++++++++
contrib/postgres_fdw/option.c | 71 +++
contrib/postgres_fdw/postgres_fdw.c | 256 +++++++-
contrib/postgres_fdw/postgres_fdw.h | 26 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 39 ++
5 files changed, 964 insertions(+), 9 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index cd28126049d..5496a6ddea5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -441,6 +441,173 @@ SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
fixed |
(1 row)
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ - Plan: +
+ Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "ft1" +
+ Schema: "public" +
+ Alias: "t1" +
+ Disabled: false +
+ Output: +
+ - "c1" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Remote SQL: "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))"+
+ Plan Node ID: 0 +
+ Remote Plans: +
+ Plan Node ID 0: +
+ - Plan: +
+ Node Type: "Index Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Schema: "S 1" +
+ Alias: "T 1" +
+ Disabled: false +
+ Output: +
+ - "\"C 1\"" +
+ - "c2" +
+ - "c3" +
+ - "c4" +
+ - "c5" +
+ - "c6" +
+ - "c7" +
+ - "c8" +
+ Index Cond: "(\"T 1\".\"C 1\" = 101)"
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>ft1</Relation-Name> +
+ <Schema>public</Schema> +
+ <Alias>t1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>c1</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Remote-SQL>SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))</Remote-SQL>+
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-0> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Schema>S 1</Schema> +
+ <Alias>T 1</Alias> +
+ <Disabled>false</Disabled> +
+ <Output> +
+ <Item>"C 1"</Item> +
+ <Item>c2</Item> +
+ <Item>c3</Item> +
+ <Item>c4</Item> +
+ <Item>c5</Item> +
+ <Item>c6</Item> +
+ <Item>c7</Item> +
+ <Item>c8</Item> +
+ </Output> +
+ <Index-Cond>("T 1"."C 1" = 101)</Index-Cond> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-0> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "ft1", +
+ "Schema": "public", +
+ "Alias": "t1", +
+ "Disabled": false, +
+ "Output": ["c1", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Remote SQL": "SELECT \"C 1\", c2, c3, c4, c5, c6, c7, c8 FROM \"S 1\".\"T 1\" WHERE ((\"C 1\" = 101))",+
+ "Plan Node ID": 0 +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 0": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Schema": "S 1", +
+ "Alias": "T 1", +
+ "Disabled": false, +
+ "Output": ["\"C 1\"", "c2", "c3", "c4", "c5", "c6", "c7", "c8"], +
+ "Index Cond": "(\"T 1\".\"C 1\" = 101)" +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------
+ Foreign Scan on public.ft1 t1
+ Output: c1, c2, c3, c4, c5, c6, c7, c8
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 101))
+ Plan Node ID: 0
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Index Scan using t1_pkey on "S 1"."T 1"
+ Output: "C 1", c2, c3, c4, c5, c6, c7, c8
+ Index Cond: ("T 1"."C 1" = 101)
+(10 rows)
+
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -5086,6 +5253,373 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
Remote SQL: SELECT r5."C 1", r6.c1 FROM ("S 1"."T 1" r5 INNER JOIN "S 1"."T 3" r6 ON (((r5."C 1" = r6.c1)))) ORDER BY r5."C 1" ASC NULLS LAST
(13 rows)
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ERROR: EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ Limit
+ Plan Node ID: 0
+ -> Merge Semi Join
+ Merge Cond: (ft1.c1 = ft2_1.c1)
+ Plan Node ID: 1
+ -> Foreign Scan
+ Relations: (ft1) INNER JOIN (ft2)
+ Plan Node ID: 2
+ -> Foreign Scan
+ Relations: (ft2 ft2_1) INNER JOIN (ft4)
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 2:
+ Index Only Scan using t1_pkey on "T 1" r2
+ Plan Node ID 3:
+ Merge Join
+ Merge Cond: (r5."C 1" = r6.c1)
+ -> Index Only Scan using t1_pkey on "T 1" r5
+ -> Sort
+ Sort Key: r6.c1
+ -> Seq Scan on "T 3" r6
+(22 rows)
+
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------------------
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Limit</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Plan-Node-ID>0</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Semi</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>false</Inner-Unique> +
+ <Merge-Cond>(ft1.c1 = ft2_1.c1)</Merge-Cond> +
+ <Plan-Node-ID>1</Plan-Node-ID> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft1) INNER JOIN (ft2)</Relations> +
+ <Plan-Node-ID>2</Plan-Node-ID> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Foreign Scan</Node-Type> +
+ <Operation>Select</Operation> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Relations>(ft2 ft2_1) INNER JOIN (ft4)</Relations> +
+ <Plan-Node-ID>3</Plan-Node-ID> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ <Remote-Plans> +
+ <Plan-Node-ID-2> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r2</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-2> +
+ <Plan-Node-ID-3> +
+ <explain xmlns="http://www.postgresql.org/2009/explain"> +
+ <Query> +
+ <Plan> +
+ <Node-Type>Merge Join</Node-Type> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Join-Type>Inner</Join-Type> +
+ <Disabled>false</Disabled> +
+ <Inner-Unique>true</Inner-Unique> +
+ <Merge-Cond>(r5."C 1" = r6.c1)</Merge-Cond> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Index Only Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Scan-Direction>Forward</Scan-Direction> +
+ <Index-Name>t1_pkey</Index-Name> +
+ <Relation-Name>T 1</Relation-Name> +
+ <Alias>r5</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ <Plan> +
+ <Node-Type>Sort</Node-Type> +
+ <Parent-Relationship>Inner</Parent-Relationship> +
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Disabled>false</Disabled> +
+ <Sort-Key> +
+ <Item>r6.c1</Item> +
+ </Sort-Key> +
+ <Plans> +
+ <Plan> +
+ <Node-Type>Seq Scan</Node-Type> +
+ <Parent-Relationship>Outer</Parent-Relationship>+
+ <Parallel-Aware>false</Parallel-Aware> +
+ <Async-Capable>false</Async-Capable> +
+ <Relation-Name>T 3</Relation-Name> +
+ <Alias>r6</Alias> +
+ <Disabled>false</Disabled> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Plans> +
+ </Plan> +
+ </Query> +
+ </explain> +
+ </Plan-Node-ID-3> +
+ </Remote-Plans> +
+ </Query> +
+ </explain>
+(1 row)
+
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+------------------------------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Limit", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Plan Node ID": 0, +
+ "Plans": [ +
+ { +
+ "Node Type": "Merge Join", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Semi", +
+ "Disabled": false, +
+ "Inner Unique": false, +
+ "Merge Cond": "(ft1.c1 = ft2_1.c1)", +
+ "Plan Node ID": 1, +
+ "Plans": [ +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft1) INNER JOIN (ft2)", +
+ "Plan Node ID": 2 +
+ }, +
+ { +
+ "Node Type": "Foreign Scan", +
+ "Operation": "Select", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Relations": "(ft2 ft2_1) INNER JOIN (ft4)",+
+ "Plan Node ID": 3 +
+ } +
+ ] +
+ } +
+ ] +
+ }, +
+ "Remote Plans": { +
+ "Plan Node ID 2": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Index Only Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r2", +
+ "Disabled": false +
+ } +
+ } +
+ ] +
+ ], +
+ "Plan Node ID 3": [ +
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Merge Join", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Join Type": "Inner", +
+ "Disabled": false, +
+ "Inner Unique": true, +
+ "Merge Cond": "(r5.\"C 1\" = r6.c1)", +
+ "Plans": [ +
+ { +
+ "Node Type": "Index Only Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Scan Direction": "Forward", +
+ "Index Name": "t1_pkey", +
+ "Relation Name": "T 1", +
+ "Alias": "r5", +
+ "Disabled": false +
+ }, +
+ { +
+ "Node Type": "Sort", +
+ "Parent Relationship": "Inner", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Disabled": false, +
+ "Sort Key": ["r6.c1"], +
+ "Plans": [ +
+ { +
+ "Node Type": "Seq Scan", +
+ "Parent Relationship": "Outer", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "T 3", +
+ "Alias": "r6", +
+ "Disabled": false +
+ } +
+ ] +
+ } +
+ ] +
+ } +
+ } +
+ ] +
+ ] +
+ } +
+ } +
+ ]
+(1 row)
+
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+ QUERY PLAN
+-------------------------------------------------------
+ - Plan: +
+ Node Type: "Limit" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Plan Node ID: 0 +
+ Plans: +
+ - Node Type: "Merge Join" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Semi" +
+ Disabled: false +
+ Inner Unique: false +
+ Merge Cond: "(ft1.c1 = ft2_1.c1)" +
+ Plan Node ID: 1 +
+ Plans: +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft1) INNER JOIN (ft2)" +
+ Plan Node ID: 2 +
+ - Node Type: "Foreign Scan" +
+ Operation: "Select" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Relations: "(ft2 ft2_1) INNER JOIN (ft4)"+
+ Plan Node ID: 3 +
+ Remote Plans: +
+ Plan Node ID 2: +
+ - Plan: +
+ Node Type: "Index Only Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r2" +
+ Disabled: false +
+ Plan Node ID 3: +
+ - Plan: +
+ Node Type: "Merge Join" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Join Type: "Inner" +
+ Disabled: false +
+ Inner Unique: true +
+ Merge Cond: "(r5.\"C 1\" = r6.c1)" +
+ Plans: +
+ - Node Type: "Index Only Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Scan Direction: "Forward" +
+ Index Name: "t1_pkey" +
+ Relation Name: "T 1" +
+ Alias: "r5" +
+ Disabled: false +
+ - Node Type: "Sort" +
+ Parent Relationship: "Inner" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Disabled: false +
+ Sort Key: +
+ - "r6.c1" +
+ Plans: +
+ - Node Type: "Seq Scan" +
+ Parent Relationship: "Outer" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "T 3" +
+ Alias: "r6" +
+ Disabled: false
+(1 row)
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -6303,6 +6837,25 @@ DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
ft2
(1 row)
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Update on public.ft2
+ Plan Node ID: 0
+ -> Foreign Update on public.ft2
+ Remote SQL: UPDATE "S 1"."T 1" SET c2 = (c2 + 300) WHERE ((("C 1" % 10) = 3))
+ Plan Node ID: 1
+ Remote Plans:
+ -------------
+ Plan Node ID 1:
+ Update on "S 1"."T 1"
+ -> Seq Scan on "S 1"."T 1"
+ Output: (c2 + 300), ctid
+ Filter: (("T 1"."C 1" % 10) = 3)
+(12 rows)
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -12260,6 +12813,34 @@ SELECT * FROM insert_tbl ORDER BY a;
2505 | 505 | bar
(2 rows)
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+ QUERY PLAN
+-------------------------------------------------------------------------
+ Insert on public.insert_tbl
+ Remote SQL: INSERT INTO public.base_tbl4(a, b, c) VALUES ($1, $2, $3)
+ Batch Size: 1
+ Plan Node ID: 0
+ -> Append
+ Plan Node ID: 1
+ -> Seq Scan on public.local_tbl
+ Output: local_tbl.a, local_tbl.b, local_tbl.c
+ Plan Node ID: 2
+ -> Async Foreign Scan on public.remote_tbl
+ Output: remote_tbl.a, remote_tbl.b, remote_tbl.c
+ Remote SQL: SELECT a, b, c FROM public.base_tbl3
+ Plan Node ID: 3
+ Remote Plans:
+ -------------
+ Plan Node ID 0:
+ Insert on public.base_tbl4
+ -> Result
+ Output: $1, $2, $3
+ Plan Node ID 3:
+ Seq Scan on public.base_tbl3
+ Output: a, b, c
+(22 rows)
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
diff --git a/contrib/postgres_fdw/option.c b/contrib/postgres_fdw/option.c
index 04788b7e8b3..a3b87d2bbaf 100644
--- a/contrib/postgres_fdw/option.c
+++ b/contrib/postgres_fdw/option.c
@@ -17,6 +17,8 @@
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
+#include "commands/explain_state.h"
#include "commands/extension.h"
#include "libpq/libpq-be.h"
#include "postgres_fdw.h"
@@ -40,6 +42,13 @@ typedef struct PgFdwOption
*/
static PgFdwOption *postgres_fdw_options;
+/*
+ * EXPLAIN hooks
+ */
+static explain_per_node_hook_type prev_explain_per_node_hook;
+static explain_per_plan_hook_type prev_explain_per_plan_hook;
+static explain_validate_options_hook_type prev_explain_validate_options_hook;
+
/*
* GUC parameters
*/
@@ -561,6 +570,57 @@ process_pgfdw_appname(const char *appname)
return buf.data;
}
+/*
+ * Get the PgFdwExplainState structure from an ExplainState; if there is
+ * none, create one, attach it to the ExplainState, and return it.
+ */
+static PgFdwExplainState *
+pgfdw_ensure_options(ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"));
+
+ if (pgfdw_explain_state == NULL)
+ {
+ pgfdw_explain_state = palloc0(sizeof(PgFdwExplainState));
+ SetExplainExtensionState(es, GetExplainExtensionId("postgres_fdw"), pgfdw_explain_state);
+ pgfdw_explain_state->all_remote_plans = NIL;
+ }
+
+ return pgfdw_explain_state;
+}
+
+/*
+ * Parse handler for EXPLAIN (REMOTE_PLANS).
+ */
+static void
+pgfdw_remote_plans_apply(ExplainState *es, DefElem *opt, ParseState *pstate)
+{
+ PgFdwExplainState *options = pgfdw_ensure_options(es);
+
+ options->remote_plans = defGetBoolean(opt);
+}
+
+static void
+postgresExplainValidateOptions(ExplainState *es, List *options, ParseState *pstate)
+{
+ ListCell *lc;
+
+ foreach(lc, options)
+ {
+ DefElem *opt = (DefElem *) lfirst(lc);
+
+ if (strcmp(opt->defname, "remote_plans") == 0)
+ {
+ if (defGetBoolean(opt) && es->analyze)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN options REMOTE_PLANS and ANALYZE cannot be used together"));
+ }
+ }
+}
+
/*
* Module load callback
*/
@@ -587,4 +647,15 @@ _PG_init(void)
NULL);
MarkGUCPrefixReserved("postgres_fdw");
+
+ RegisterExtensionExplainOption("remote_plans", pgfdw_remote_plans_apply);
+
+ /* per node EXPLAIN hook */
+ prev_explain_per_node_hook = explain_per_node_hook;
+ explain_per_node_hook = postgresExplainPerNode;
+ prev_explain_per_plan_hook = explain_per_plan_hook;
+ explain_per_plan_hook = postgresExplainPerPlan;
+ prev_explain_validate_options_hook = explain_validate_options_hook;
+ explain_validate_options_hook = postgresExplainValidateOptions;
+
}
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 06b52c65300..9b99a2386b3 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -43,6 +43,7 @@
#include "utils/builtins.h"
#include "utils/float.h"
#include "utils/guc.h"
+#include "utils/json.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -133,6 +134,20 @@ enum FdwDirectModifyPrivateIndex
FdwDirectModifyPrivateSetProcessed,
};
+static const char *const explain_formats[] = {
+ [EXPLAIN_FORMAT_TEXT] = "TEXT",
+ [EXPLAIN_FORMAT_JSON] = "JSON",
+ [EXPLAIN_FORMAT_XML] = "XML",
+ [EXPLAIN_FORMAT_YAML] = "YAML",
+};
+
+/*
+ * Track the extension id in the backend.
+ */
+static int extension_id = -1;
+#define GET_EXTENSION_ID() ((extension_id == -1) ? \
+ GetExplainExtensionId("postgres_fdw"): extension_id)
+
/*
* Execution state of a foreign scan using postgres_fdw.
*/
@@ -2822,6 +2837,65 @@ postgresEndDirectModify(ForeignScanState *node)
/* MemoryContext will be deleted automatically. */
}
+static void
+postgresExplainStatement(int plan_node_id,
+ ExplainState *es,
+ PgFdwExplainState * pgfdw_explain_state,
+ PGconn *conn,
+ char *sql)
+{
+ PGresult *volatile res = NULL;
+ StringInfoData explain_sql;
+
+ PG_TRY();
+ {
+ int numrows,
+ i;
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) palloc(sizeof(PgFdwExplainRemotePlans));
+
+ initStringInfo(&explain_sql);
+ initStringInfo(&explain->explain_plan);
+
+ appendStringInfo(&explain_sql, "EXPLAIN (\
+ GENERIC_PLAN 1, \
+ FORMAT %s, \
+ VERBOSE %d, \
+ COSTS %d, \
+ SETTINGS %d) \
+ %s",
+ explain_formats[es->format],
+ (es->verbose) ? 1 : 0,
+ (es->costs) ? 1 : 0,
+ (es->settings) ? 1 : 0,
+ sql);
+
+ /* Run the query and collect the remote plan */
+ res = pgfdw_exec_query(conn, explain_sql.data, NULL);
+ if (PQresultStatus(res) != PGRES_TUPLES_OK)
+ pgfdw_report_error(res, conn, explain_sql.data);
+
+ numrows = PQntuples(res);
+
+ for (i = 0; i < numrows; i++)
+ appendStringInfo(&explain->explain_plan, "%s\n", pstrdup(PQgetvalue(res, i, 0)));
+
+ if (explain->explain_plan.len > 0 && explain->explain_plan.data[explain->explain_plan.len - 1] == '\n')
+ explain->explain_plan.data[--explain->explain_plan.len] = '\0';
+
+ explain->plan_node_id = plan_node_id;
+ pgfdw_explain_state->all_remote_plans = lappend(pgfdw_explain_state->all_remote_plans, explain);
+ }
+ PG_FINALLY();
+ {
+ if (res)
+ PQclear(res);
+
+ if (explain_sql.data)
+ pfree(explain_sql.data);
+ }
+ PG_END_TRY();
+}
+
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
@@ -2831,6 +2905,9 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
+ PgFdwExplainState *pgfdw_explain_state;
+ char *sql;
+ List *foreign_scan_table = NIL;
/*
* Identify foreign scans that are really joins or upper relations. The
@@ -2892,6 +2969,14 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
+
+ /*
+ * add one of the tables to foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table = lappend_oid(foreign_scan_table, rte->relid);
+
if (es->verbose)
{
char *namespace;
@@ -2917,15 +3002,38 @@ postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
ExplainPropertyText("Relations", relations.data, es);
}
+ sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
+
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
+ ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
{
- char *sql;
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
- sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
- ExplainPropertyText("Remote SQL", sql, es);
+ if (node && !node->ss.ss_currentRelation &&
+ foreign_scan_table == NIL)
+ return;
+
+ if (node && node->ss.ss_currentRelation)
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+ else
+ table = GetForeignTable(list_nth_oid(foreign_scan_table, 0));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -2940,11 +3048,12 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
int subplan_index,
ExplainState *es)
{
+ char *sql = strVal(list_nth(fdw_private,
+ FdwModifyPrivateUpdateSql));
+ PgFdwExplainState *pgfdw_explain_state;
+
if (es->verbose)
{
- char *sql = strVal(list_nth(fdw_private,
- FdwModifyPrivateUpdateSql));
-
ExplainPropertyText("Remote SQL", sql, es);
/*
@@ -2954,6 +3063,24 @@ postgresExplainForeignModify(ModifyTableState *mtstate,
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(rinfo->ri_RelationDesc->rd_rel->oid);
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(mtstate->ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
+ }
}
/*
@@ -2966,12 +3093,31 @@ postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
+ sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
- {
- fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
- sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
ExplainPropertyText("Remote SQL", sql, es);
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ {
+ UserMapping *user = NULL;
+ PGconn *conn = NULL;
+ ForeignTable *table;
+
+ table = GetForeignTable(RelationGetRelid(node->ss.ss_currentRelation));
+
+ Assert(table);
+
+ user = GetUserMapping(GetUserId(), table->serverid);
+ conn = GetConnection(user, false, NULL);
+
+ postgresExplainStatement(node->ss.ps.plan->plan_node_id, es, pgfdw_explain_state, conn, sql);
+ ReleaseConnection(conn);
}
}
@@ -7886,3 +8032,95 @@ get_batch_size_option(Relation rel)
return batch_size;
}
+
+void
+postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship, const char *plan_name,
+ ExplainState *es)
+{
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
+ ExplainPropertyInteger("Plan Node ID", NULL, planstate->plan->plan_node_id, es);
+}
+
+static void
+pgfdwFormatRemotePlan(PgFdwExplainRemotePlans * explain,
+ ExplainState *es,
+ int plan_node_id)
+{
+ char *token;
+ StringInfoData remote_plan_name;
+
+ initStringInfo(&remote_plan_name);
+ appendStringInfo(&remote_plan_name, "Plan Node ID %d", plan_node_id);
+
+ ExplainOpenGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Plan Node ID %d:", plan_node_id);
+ appendStringInfoString(es->str, "\n");
+ }
+
+ while ((token = strsep(&explain->explain_plan.data, "\n")) != NULL)
+ {
+ if (es->format == EXPLAIN_FORMAT_JSON ||
+ es->format == EXPLAIN_FORMAT_YAML)
+ appendStringInfoString(es->str, "\n");
+
+ appendStringInfoSpaces(es->str, (es->indent == 0) ? 2 : es->indent * 2);
+ appendStringInfoString(es->str, token);
+
+ if (es->format == EXPLAIN_FORMAT_XML ||
+ es->format == EXPLAIN_FORMAT_TEXT)
+ appendStringInfoString(es->str, "\n");
+ }
+
+ ExplainCloseGroup(remote_plan_name.data, remote_plan_name.data, false, es);
+ pfree(remote_plan_name.data);
+}
+
+void
+postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc;
+ PgFdwExplainState *pgfdw_explain_state;
+
+ pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
+
+ if (pgfdw_explain_state == NULL ||
+ pgfdw_explain_state->all_remote_plans == NIL ||
+ !pgfdw_explain_state->remote_plans)
+ return;
+
+ ExplainOpenGroup("Remote Plans", "Remote Plans", true, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfo(es->str, "Remote Plans:\n");
+ appendStringInfo(es->str, "-------------\n");
+ }
+
+ /* Process every remote plan captured */
+ foreach(lc, pgfdw_explain_state->all_remote_plans)
+ {
+ PgFdwExplainRemotePlans *explain = (PgFdwExplainRemotePlans *) lfirst(lc);
+
+ pgfdwFormatRemotePlan(explain,
+ es,
+ explain->plan_node_id);
+ }
+
+ ExplainCloseGroup("Remote Plans", "Remote Plans", true, es);
+}
diff --git a/contrib/postgres_fdw/postgres_fdw.h b/contrib/postgres_fdw/postgres_fdw.h
index e69735298d7..b135664b933 100644
--- a/contrib/postgres_fdw/postgres_fdw.h
+++ b/contrib/postgres_fdw/postgres_fdw.h
@@ -13,6 +13,7 @@
#ifndef POSTGRES_FDW_H
#define POSTGRES_FDW_H
+#include "commands/explain_state.h"
#include "foreign/foreign.h"
#include "lib/stringinfo.h"
#include "libpq/libpq-be-fe.h"
@@ -151,6 +152,21 @@ typedef enum PgFdwSamplingMethod
ANALYZE_SAMPLE_BERNOULLI, /* TABLESAMPLE bernoulli */
} PgFdwSamplingMethod;
+typedef struct PgFdwExplainRemotePlans
+{
+ int plan_node_id;
+ StringInfoData explain_plan;
+
+} PgFdwExplainRemotePlans;
+
+typedef struct PgFdwExplainState
+{
+ List *all_remote_plans;
+
+ /* EXPLAIN options */
+ bool remote_plans;
+} PgFdwExplainState;
+
/* in postgres_fdw.c */
extern int set_transmission_modes(void);
extern void reset_transmission_modes(int nestlevel);
@@ -178,6 +194,16 @@ extern int ExtractConnectionOptions(List *defelems,
extern List *ExtractExtensionList(const char *extensionsString,
bool warnOnMissing);
extern char *process_pgfdw_appname(const char *appname);
+extern void postgresExplainPerNode(PlanState *planstate, List *ancestors,
+ const char *relationship,
+ const char *plan_name,
+ ExplainState *es);
+extern void postgresExplainPerPlan(PlannedStmt *plannedstmt,
+ IntoClause *into,
+ ExplainState *es,
+ const char *queryString,
+ ParamListInfo params,
+ QueryEnvironment *queryEnv);
extern char *pgfdw_application_name;
/* in deparse.c */
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 9a8f9e28135..25823a7ebe7 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -281,6 +281,11 @@ SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
+-- with WHERE clause and remote_plans with different formats
+EXPLAIN (REMOTE_PLANS, FORMAT YAML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT XML, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS, FORMAT JSON, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
+EXPLAIN (REMOTE_PLANS TRUE, FORMAT TEXT, VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 101;
-- Test forcing the remote server to produce sorted data for a merge join.
SET enable_hashjoin TO false;
SET enable_nestloop TO false;
@@ -1489,6 +1494,33 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
ORDER BY ft1.c1 LIMIT 5;
+-- EXPLAIN remote_plans
+EXPLAIN (remote_plans, format text, costs off, analyze)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format text, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format xml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format json, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+EXPLAIN (remote_plans, format yaml, costs off)
+SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
+ ft1.c1 IN (
+ SELECT ft2.c1 FROM ft2 JOIN ft4 ON ft2.c1 = ft4.c1)
+ ORDER BY ft1.c1 LIMIT 5;
+
-- ===================================================================
-- test writable foreign table stuff
-- ===================================================================
@@ -1538,6 +1570,10 @@ EXPLAIN (verbose, costs off)
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass; -- can be pushed down
DELETE FROM ft2 WHERE c1 = 1200 RETURNING tableoid::regclass;
+-- test write on foreign tables with remote_plans
+EXPLAIN (remote_plans, verbose, costs off)
+UPDATE ft2 SET c2 = c2 + 300 WHERE c1 % 10 = 3;
+
-- Test UPDATE/DELETE with RETURNING on a three-table join
INSERT INTO ft2 (c1,c2,c3)
SELECT id, id - 1200, to_char(id, 'FM00000') FROM generate_series(1201, 1300) id;
@@ -4138,6 +4174,9 @@ INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_t
SELECT * FROM insert_tbl ORDER BY a;
+EXPLAIN (REMOTE_PLANS, VERBOSE, COSTS OFF)
+INSERT INTO insert_tbl (SELECT * FROM local_tbl UNION ALL SELECT * FROM remote_tbl);
+
-- Check with direct modify
EXPLAIN (VERBOSE, COSTS OFF)
WITH t AS (UPDATE remote_tbl SET c = c || c RETURNING *)
--
2.43.0
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-03 22:40 Sami Imseih <[email protected]>
parent: dinesh salve <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sami Imseih @ 2025-12-03 22:40 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
> I have refactored the commit on the latest version of PG and added a few more tests.
Thanks for the update!
> To simplify the roll out of this feature, I decided to work on analyze=false use case first.
I did not go through the entire patch yet, but a few things stood out
from my first pass.
1/
RegisterExtensionExplainOption is called during _PG_init, which is fine, but I
also wonder if we can call this during postgresExplainForeignScan as well?
The reason being is for _PG_init to be invoked, the user must load postgres_fdw
(LOAD, session_preload_libraries, shared_preload_libraries), which from my
experience is not very common in postgres_fdw. Users ordinarily just
"CREATE EXTENSION..."
So this needs to be documented [0]
2/
Does this behave sanely with multiple fdw connections? Can we add
tests for this?
+
+ /*
+ * add one of the tables to
foreign_scan_table to get the
+ * serverId for remote plans
+ */
+ if (list_length(foreign_scan_table) == 0)
+ foreign_scan_table =
lappend_oid(foreign_scan_table, rte->relid);
+
[0] https://www.postgresql.org/docs/current/postgres-fdw.html
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-04 00:34 Sami Imseih <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Sami Imseih @ 2025-12-04 00:34 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
I forgot to mention this point earlier.
I noticed GENERIC_PLAN is hard-coded to 1 (true).
Is that an oversight, or is it required?
```
+ GENERIC_PLAN 1, \
```
--
Sami Imseih
Amazon Web Services (AWS)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: explain plans for foreign servers
@ 2025-12-09 21:08 Sami Imseih <[email protected]>
parent: Sami Imseih <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Sami Imseih @ 2025-12-09 21:08 UTC (permalink / raw)
To: dinesh salve <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-hackers; Jeff Davis <[email protected]>
Hi,
I spent more time reviewing this patch. Here are additional comments.
1/ Remove unnecessary includes
#include "commands/explain_state.h" from option.c.
#include "utils/json.h" from postgres_fdw.c.
2/ Add new EXPLAIN options
MEMORY and SUMMARY flags added to the remote EXPLAIN query formatting.
These do not require ANALYZE to be used.
A larger question is how would we want to ensure that new core EXPLAIN
options can be automatically set?
This is not quite common, but perhaps it is a good thing to add a comment
near ExplainState explaining that if a new option is added, make sure
that postgre_fdw remote_plans are updated.
```
typedef struct ExplainState
{
StringInfo str; /* output buffer */
/* options */
bool verbose; /* be verbose */
bool analyze; /* print actual times */
bool costs; /* print estimated co
```
3/ Removed unnecessary pstrdup() when appending remote plan rows to
explain_plan.
Removed unnecessary pstrdup() when appending remote plan rows to explain_plan.
```
+ appendStringInfo(&explain->explain_plan,
"%s\n", pstrdup(PQgetvalue(res, i, 0)));
```
4/ Simplify foreign table OID handling in postgresExplainForeignScan
I am not sure why we need a list that can only hold a single value.
Can we just use an Oid variable to store this?
5/ Encapsulates getting the connection, executing the remote EXPLAIN,
and releasing the connection.
Replaces repeated code in postgresExplainForeignScan,
postgresExplainForeignModify, and postgresExplainDirectModify.
For #4 and #5, attached is my attempt to simplify these routines. What
do you think?
6/ Updated typedefs.list
... to include PgFdwExplainRemotePlans and PgFdwExplainState.
7/ Tests
I quickly skimmed the tests, but I did not see a join push-down test.
We should add
that.
--
Sami Imseih
Amazon Web Services (AWS)
/*
* explain_remote_query
* Helper function to get connection and execute remote EXPLAIN
*/
static void
explain_remote_query(int plan_node_id, ExplainState *es,
PgFdwExplainState *pgfdw_explain_state,
Oid foreign_table_oid, char *sql)
{
UserMapping *user;
PGconn *conn;
ForeignTable *table;
table = GetForeignTable(foreign_table_oid);
user = GetUserMapping(GetUserId(), table->serverid);
conn = GetConnection(user, false, NULL);
postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
ReleaseConnection(conn);
}
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
static void
postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
PgFdwExplainState *pgfdw_explain_state;
char *sql;
Oid foreign_table_oid = InvalidOid;
if (node->ss.ss_currentRelation)
foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
* input looks something like "(1) LEFT JOIN (2)", and we must replace the
* digit string(s), which are RT indexes, with the correct relation names.
* We do that here, not when the plan is created, because we can't know
* what aliases ruleutils.c will assign at plan creation time.
*/
if (list_length(fdw_private) > FdwScanPrivateRelations)
{
StringInfoData relations;
char *rawrelations;
char *ptr;
int minrti,
rtoffset;
rawrelations = strVal(list_nth(fdw_private, FdwScanPrivateRelations));
/*
* A difficulty with using a string representation of RT indexes is
* that setrefs.c won't update the string when flattening the
* rangetable. To find out what rtoffset was applied, identify the
* minimum RT index appearing in the string and compare it to the
* minimum member of plan->fs_base_relids. (We expect all the relids
* in the join will have been offset by the same amount; the Asserts
* below should catch it if that ever changes.)
*/
minrti = INT_MAX;
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
if (rti < minrti)
minrti = rti;
}
else
ptr++;
}
rtoffset = bms_next_member(plan->fs_base_relids, -1) - minrti;
/* Now we can translate the string */
initStringInfo(&relations);
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
RangeTblEntry *rte;
char *relname;
char *refname;
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
/*
* Save first table OID for getting server connection
*/
if (!OidIsValid(foreign_table_oid))
foreign_table_oid = rte->relid;
if (es->verbose)
{
char *namespace;
namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
appendStringInfo(&relations, "%s.%s",
quote_identifier(namespace),
quote_identifier(relname));
}
else
appendStringInfoString(&relations,
quote_identifier(relname));
refname = (char *) list_nth(es->rtable_names, rti - 1);
if (refname == NULL)
refname = rte->eref->aliasname;
if (strcmp(refname, relname) != 0)
appendStringInfo(&relations, " %s",
quote_identifier(refname));
}
else
appendStringInfoChar(&relations, *ptr++);
}
ExplainPropertyText("Relations", relations.data, es);
}
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
/* If we don't have a foreign table oid by now, something went wrong */
Assert(foreign_table_oid);
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
foreign_table_oid,
sql);
}
/*
* postgresExplainForeignModify
* Produce extra output for EXPLAIN of a ModifyTable on a foreign table
*/
static void
postgresExplainForeignModify(ModifyTableState *mtstate,
ResultRelInfo *rinfo,
List *fdw_private,
int subplan_index,
ExplainState *es)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
PgFdwExplainState *pgfdw_explain_state;
if (es->verbose)
{
ExplainPropertyText("Remote SQL", sql, es);
/*
* For INSERT we should always have batch size >= 1, but UPDATE and
* DELETE don't support batching so don't show the property.
*/
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(mtstate->ps.plan->plan_node_id,
es,
pgfdw_explain_state,
rinfo->ri_RelationDesc->rd_rel->oid,
sql);
}
/*
* postgresExplainDirectModify
* Produce extra output for EXPLAIN of a ForeignScan that modifies a
* foreign table directly
*/
static void
postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
PgFdwExplainState *pgfdw_explain_state;
fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
RelationGetRelid(node->ss.ss_currentRelation),
sql);
}
Attachments:
[text/plain] simplify_explain_routines.txt (6.1K, ../../CAA5RZ0spw7Fmd5pGvFdTaG2GqkSm6HeXJABbBeqzUDkuN4uevQ@mail.gmail.com/2-simplify_explain_routines.txt)
download | inline:
/*
* explain_remote_query
* Helper function to get connection and execute remote EXPLAIN
*/
static void
explain_remote_query(int plan_node_id, ExplainState *es,
PgFdwExplainState *pgfdw_explain_state,
Oid foreign_table_oid, char *sql)
{
UserMapping *user;
PGconn *conn;
ForeignTable *table;
table = GetForeignTable(foreign_table_oid);
user = GetUserMapping(GetUserId(), table->serverid);
conn = GetConnection(user, false, NULL);
postgresExplainStatement(plan_node_id, es, pgfdw_explain_state, conn, sql);
ReleaseConnection(conn);
}
/*
* postgresExplainForeignScan
* Produce extra output for EXPLAIN of a ForeignScan on a foreign table
*/
static void
postgresExplainForeignScan(ForeignScanState *node, ExplainState *es)
{
ForeignScan *plan = castNode(ForeignScan, node->ss.ps.plan);
List *fdw_private = plan->fdw_private;
PgFdwExplainState *pgfdw_explain_state;
char *sql;
Oid foreign_table_oid = InvalidOid;
if (node->ss.ss_currentRelation)
foreign_table_oid = RelationGetRelid(node->ss.ss_currentRelation);
/*
* Identify foreign scans that are really joins or upper relations. The
* input looks something like "(1) LEFT JOIN (2)", and we must replace the
* digit string(s), which are RT indexes, with the correct relation names.
* We do that here, not when the plan is created, because we can't know
* what aliases ruleutils.c will assign at plan creation time.
*/
if (list_length(fdw_private) > FdwScanPrivateRelations)
{
StringInfoData relations;
char *rawrelations;
char *ptr;
int minrti,
rtoffset;
rawrelations = strVal(list_nth(fdw_private, FdwScanPrivateRelations));
/*
* A difficulty with using a string representation of RT indexes is
* that setrefs.c won't update the string when flattening the
* rangetable. To find out what rtoffset was applied, identify the
* minimum RT index appearing in the string and compare it to the
* minimum member of plan->fs_base_relids. (We expect all the relids
* in the join will have been offset by the same amount; the Asserts
* below should catch it if that ever changes.)
*/
minrti = INT_MAX;
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
if (rti < minrti)
minrti = rti;
}
else
ptr++;
}
rtoffset = bms_next_member(plan->fs_base_relids, -1) - minrti;
/* Now we can translate the string */
initStringInfo(&relations);
ptr = rawrelations;
while (*ptr)
{
if (isdigit((unsigned char) *ptr))
{
int rti = strtol(ptr, &ptr, 10);
RangeTblEntry *rte;
char *relname;
char *refname;
rti += rtoffset;
Assert(bms_is_member(rti, plan->fs_base_relids));
rte = rt_fetch(rti, es->rtable);
Assert(rte->rtekind == RTE_RELATION);
/* This logic should agree with explain.c's ExplainTargetRel */
relname = get_rel_name(rte->relid);
/*
* Save first table OID for getting server connection
*/
if (!OidIsValid(foreign_table_oid))
foreign_table_oid = rte->relid;
if (es->verbose)
{
char *namespace;
namespace = get_namespace_name_or_temp(get_rel_namespace(rte->relid));
appendStringInfo(&relations, "%s.%s",
quote_identifier(namespace),
quote_identifier(relname));
}
else
appendStringInfoString(&relations,
quote_identifier(relname));
refname = (char *) list_nth(es->rtable_names, rti - 1);
if (refname == NULL)
refname = rte->eref->aliasname;
if (strcmp(refname, relname) != 0)
appendStringInfo(&relations, " %s",
quote_identifier(refname));
}
else
appendStringInfoChar(&relations, *ptr++);
}
ExplainPropertyText("Relations", relations.data, es);
}
sql = strVal(list_nth(fdw_private, FdwScanPrivateSelectSql));
/*
* Add remote query, when VERBOSE option is specified.
*/
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
/* If we don't have a foreign table oid by now, something went wrong */
Assert(foreign_table_oid);
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
foreign_table_oid,
sql);
}
/*
* postgresExplainForeignModify
* Produce extra output for EXPLAIN of a ModifyTable on a foreign table
*/
static void
postgresExplainForeignModify(ModifyTableState *mtstate,
ResultRelInfo *rinfo,
List *fdw_private,
int subplan_index,
ExplainState *es)
{
char *sql = strVal(list_nth(fdw_private,
FdwModifyPrivateUpdateSql));
PgFdwExplainState *pgfdw_explain_state;
if (es->verbose)
{
ExplainPropertyText("Remote SQL", sql, es);
/*
* For INSERT we should always have batch size >= 1, but UPDATE and
* DELETE don't support batching so don't show the property.
*/
if (rinfo->ri_BatchSize > 0)
ExplainPropertyInteger("Batch Size", NULL, rinfo->ri_BatchSize, es);
}
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(mtstate->ps.plan->plan_node_id,
es,
pgfdw_explain_state,
rinfo->ri_RelationDesc->rd_rel->oid,
sql);
}
/*
* postgresExplainDirectModify
* Produce extra output for EXPLAIN of a ForeignScan that modifies a
* foreign table directly
*/
static void
postgresExplainDirectModify(ForeignScanState *node, ExplainState *es)
{
List *fdw_private;
char *sql;
PgFdwExplainState *pgfdw_explain_state;
fdw_private = ((ForeignScan *) node->ss.ps.plan)->fdw_private;
sql = strVal(list_nth(fdw_private, FdwDirectModifyPrivateUpdateSql));
if (es->verbose)
ExplainPropertyText("Remote SQL", sql, es);
pgfdw_explain_state = GetExplainExtensionState(es, GET_EXTENSION_ID());
if (pgfdw_explain_state && pgfdw_explain_state->remote_plans)
explain_remote_query(node->ss.ps.plan->plan_node_id,
es,
pgfdw_explain_state,
RelationGetRelid(node->ss.ss_currentRelation),
sql);
}
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-12-09 21:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-07-15 22:35 [PATCH v13 5/6] snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT. Andres Freund <[email protected]>
2025-03-05 19:12 Re: explain plans for foreign servers Tom Lane <[email protected]>
2025-03-05 23:24 ` Re: explain plans for foreign servers Jeff Davis <[email protected]>
2025-11-18 03:25 ` Re: explain plans for foreign servers dinesh salve <[email protected]>
2025-12-03 22:40 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2025-12-04 00:34 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
2025-12-09 21:08 ` Re: explain plans for foreign servers Sami Imseih <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox