PostgreSQL在何处处理 sql查询之三十五
接前面
深入考察 PortalRun: 初步判断，其核心在于
bool PortalRun(Portal portal, long count, bool isTopLevel, DestReceiver *dest, DestReceiver *altdest, char *completionTag) { ... portal->status = PORTAL_ACTIVE; ... PG_TRY(); { ActivePortal = portal; CurrentResourceOwner = portal->resowner; PortalContext = PortalGetHeapMemory(portal); MemoryContextSwitchTo(PortalContext); switch (portal->strategy) { case PORTAL_ONE_SELECT: case PORTAL_ONE_RETURNING: case PORTAL_ONE_MOD_WITH: case PORTAL_UTIL_SELECT: ... /* * Now fetch desired portion of results. */ nprocessed = PortalRunSelect(portal, true, count, dest); ... break; case PORTAL_MULTI_QUERY: ... break; default: ... break; } } PG_CATCH(); { ... PG_RE_THROW(); } PG_END_TRY(); ... return result; }
接下来，再接着分析(pgquery.c) PortalRunSelect ：
/* * PortalRunSelect * Execute a portal's query in PORTAL_ONE_SELECT mode, and also * when fetching from a completed holdStore in PORTAL_ONE_RETURNING, * PORTAL_ONE_MOD_WITH, and PORTAL_UTIL_SELECT cases. * * This handles simple N-rows-forward-or-backward cases. For more complex * nonsequential access to a portal, see PortalRunFetch. * * count <= 0 is interpreted as a no-op: the destination gets started up * and shut down, but nothing else happens. Also, count == FETCH_ALL is * interpreted as "all rows". * * Caller must already have validated the Portal and done appropriate * setup (cf. PortalRun). * * Returns number of rows processed (suitable for use in result tag) */ static long PortalRunSelect(Portal portal, bool forward, long count, DestReceiver *dest) { QueryDesc *queryDesc; ScanDirection direction; uint32 nprocessed; /* * NB: queryDesc will be NULL if we are fetching from a held cursor or a * completed utility query; can't use it in that path. */ queryDesc = PortalGetQueryDesc(portal); /* Caller messed up if we have neither a ready query nor held data. */ Assert(queryDesc || portal->holdStore); /* * Force the queryDesc destination to the right thing. This supports * MOVE, for example, which will pass in dest = DestNone. This is okay to * change as long as we do it on every fetch. (The Executor must not * assume that dest never changes.) */ if (queryDesc) queryDesc->dest = dest; /* * Determine which direction to go in, and check to see if we're already * at the end of the available tuples in that direction. If so, set the * direction to NoMovement to avoid trying to fetch any tuples. (This * check exists because not all plan node types are robust about being * called again if they've already returned NULL once.) Then call the * executor (we must not skip this, because the destination needs to see a * setup and shutdown even if no tuples are available). Finally, update * the portal position state depending on the number of tuples that were * retrieved. */ if (forward) { if (portal->atEnd || count <= 0) direction = NoMovementScanDirection; else direction = ForwardScanDirection; /* In the executor, zero count processes all rows */ if (count == FETCH_ALL) count = 0; if (portal->holdStore) nprocessed = RunFromStore(portal, direction, count, dest); else { PushActiveSnapshot(queryDesc->snapshot); ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; PopActiveSnapshot(); } if (!ScanDirectionIsNoMovement(direction)) { long oldPos; if (nprocessed > 0) portal->atStart = false; /* OK to go backward now */ if (count == 0 || (unsigned long) nprocessed < (unsigned long) count) portal->atEnd = true; /* we retrieved 'em all */ oldPos = portal->portalPos; portal->portalPos += nprocessed; /* portalPos doesn't advance when we fall off the end */ if (portal->portalPos < oldPos) portal->posOverflow = true; } } else { if (portal->cursorOptions & CURSOR_OPT_NO_SCROLL) ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("cursor can only scan forward"), errhint("Declare it with SCROLL option to enable backward scan."))); if (portal->atStart || count <= 0) direction = NoMovementScanDirection; else direction = BackwardScanDirection; /* In the executor, zero count processes all rows */ if (count == FETCH_ALL) count = 0; if (portal->holdStore) nprocessed = RunFromStore(portal, direction, count, dest); else { PushActiveSnapshot(queryDesc->snapshot); ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; PopActiveSnapshot(); } if (!ScanDirectionIsNoMovement(direction)) { if (nprocessed > 0 && portal->atEnd) { portal->atEnd = false; /* OK to go forward now */ portal->portalPos++; /* adjust for endpoint case */ } if (count == 0 || (unsigned long) nprocessed < (unsigned long) count) { portal->atStart = true; /* we retrieved 'em all */ portal->portalPos = 0; portal->posOverflow = false; } else { long oldPos; oldPos = portal->portalPos; portal->portalPos -= nprocessed; if (portal->portalPos > oldPos || portal->portalPos <= 0) portal->posOverflow = true; } } } return nprocessed; }
对 PortalRunSelect，需要进一步进行分析：实际执行的时候，可以知道 forward 是 true。
所以把上面的程序简化成为这样：
/* * PortalRunSelect * Execute a portal's query in PORTAL_ONE_SELECT mode, and also * when fetching from a completed holdStore in PORTAL_ONE_RETURNING, * PORTAL_ONE_MOD_WITH, and PORTAL_UTIL_SELECT cases. * * This handles simple N-rows-forward-or-backward cases. For more complex * nonsequential access to a portal, see PortalRunFetch. * * count <= 0 is interpreted as a no-op: the destination gets started up * and shut down, but nothing else happens. Also, count == FETCH_ALL is * interpreted as "all rows". * * Caller must already have validated the Portal and done appropriate * setup (cf. PortalRun). * * Returns number of rows processed (suitable for use in result tag) */ static long PortalRunSelect(Portal portal, bool forward, long count, DestReceiver *dest) { ... /* * Determine which direction to go in, and check to see if we're already * at the end of the available tuples in that direction. If so, set the * direction to NoMovement to avoid trying to fetch any tuples. (This * check exists because not all plan node types are robust about being * called again if they've already returned NULL once.) Then call the * executor (we must not skip this, because the destination needs to see a * setup and shutdown even if no tuples are available). Finally, update * the portal position state depending on the number of tuples that were * retrieved. */ if (forward) { if (portal->atEnd || count <= 0) direction = NoMovementScanDirection; else direction = ForwardScanDirection; /* In the executor, zero count processes all rows */ if (count == FETCH_ALL) count = 0; if (portal->holdStore) nprocessed = RunFromStore(portal, direction, count, dest); else { PushActiveSnapshot(queryDesc->snapshot); ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; PopActiveSnapshot(); } if (!ScanDirectionIsNoMovement(direction)) { long oldPos; if (nprocessed > 0) portal->atStart = false; /* OK to go backward now */ if (count == 0 || (unsigned long) nprocessed < (unsigned long) count) portal->atEnd = true; /* we retrieved 'em all */ oldPos = portal->portalPos; portal->portalPos += nprocessed; /* portalPos doesn't advance when we fall off the end */ if (portal->portalPos < oldPos) portal->posOverflow = true; } } else { ... } return nprocessed; }
对于上面的这一段：
if (portal->atEnd || count <= 0) direction = NoMovementScanDirection; else direction = ForwardScanDirection;
实际测试，select id from ts t04 获得的是 direction = ForwardScanDirection
而且，无论是否带有 where 条件，上述代码中都是 (count == FETCH_ALL)，也就是最后 count 被赋值为0
postgres=# select id from tst04; id ---- 1 2 3 (3 rows) postgres=# select id from tst04 where id<3; id ---- 1 2 (2 rows) postgres=#
下面这段：
if (portal->holdStore) nprocessed = RunFromStore(portal, direction, count, dest); else { PushActiveSnapshot(queryDesc->snapshot); ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; PopActiveSnapshot(); }
由于 portal->holdStore 不成立，所以 执行的是
{ PushActiveSnapshot(queryDesc->snapshot); ExecutorRun(queryDesc, direction, count); nprocessed = queryDesc->estate->es_processed; PopActiveSnapshot(); }
并且，经过运行发现，下面这段里面的内容，也会执行：
if (!ScanDirectionIsNoMovement(direction)) { long oldPos; if (nprocessed > 0) portal->atStart = false; /* OK to go backward now */ if (count == 0 || (unsigned long) nprocessed < (unsigned long) count) portal->atEnd = true; /* we retrieved 'em all */ oldPos = portal->portalPos; portal->portalPos += nprocessed; /* portalPos doesn't advance when we fall off the end */ if (portal->portalPos < oldPos) portal->posOverflow = true; }
进一步，将要分析 ExecutorRun(queryDesc, direction, count)
