相关 《Postgresql源码(61)查询执行——最外层Portal模块》 《Postgresql源码(62)查询执行——子模块ProcessUtility》
PG14中截取部分SQL语法:https://www.postgresql.org/docs/14/sql-commands.html
ABORT — abort the current transaction
...
ALTER INDEX — change the definition of an index
...
ALTER TABLE — change the definition of a table
ALTER TABLESPACE — change the definition of a tablespace
...
BEGIN — start a transaction block
CALL — invoke a procedure
CHECKPOINT — force a write-ahead log checkpoint
CLOSE — close a cursor
...
CREATE FOREIGN DATA WRAPPER — define a new foreign-data wrapper
CREATE FOREIGN TABLE — define a new foreign table
CREATE FUNCTION — define a new function
...
CREATE TABLE — define a new table
CREATE TABLE AS — define a new table from the results of a query
CREATE TABLESPACE — define a new tablespace
...
DELETE — delete rows of a table
...
EXPLAIN — show the execution plan of a statement
...
REVOKE — remove access privileges
...
VACUUM — garbage-collect and optionally analyze a database
...
大致看过可以发现:
如果我们自己来设计这个系统,应该也会把SQL执行分成两类,带执行计划的DML(增删改查)不带执行计划的DDL。
PG中的SQL在经过语法解析、查询编译后,进入执行模块,整形模块的分三个子模块:
SQL会在查询编译阶段得到plantree_list,在portal模块启动时(函数PortalStart),根据plantree_list中具体情况(函数ChoosePortalStrategy),来决定PortalStrategy的值,后面执行根据PortalStrategy来决定进入Executor还是ProcessUtility。
一、portal状态
typedef enum PortalStatus
{
PORTAL_NEW, /* freshly created */
PORTAL_DEFINED, /* PortalDefineQuery done */
PORTAL_READY, /* PortalStart complete, can run it */
PORTAL_ACTIVE, /* portal is running (can't delete it) */
PORTAL_DONE, /* portal is finished (don't re-run it) */
PORTAL_FAILED /* portal got error (can't re-run it) */
} PortalStatus;
二、portal结构
关键变量:
typedef struct PortalData
{
/* Bookkeeping data */
const char *name; /* portal's name */
const char *prepStmtName; /* source prepared statement (NULL if none) */
MemoryContext portalContext; /* subsidiary memory for portal */
...
// 【0】SQL语句
const char *sourceText; /* text of query (as of 8.4, never NULL) */
CommandTag commandTag; /* command tag for original query */
QueryCompletion qc; /* command completion data for executed query */
// 【1】注意:执行计划树链表
List *stmts; /* list of PlannedStmts */
CachedPlan *cplan; /* CachedPlan, if stmts are from one */
ParamListInfo portalParams; /* params to pass to query */
QueryEnvironment *queryEnv; /* environment for query */
// 【2】根绝策略决定走Executor还是ProcessUtility
PortalStrategy strategy; /* see above */
int cursorOptions; /* DECLARE CURSOR option bits */
bool run_once; /* portal will only be run once */
// 【3】当前portal状态
PortalStatus status; /* see above */
...
// 【4】Executor执行需要的查询描述符
QueryDesc *queryDesc; /* info needed for executor invocation */
// 【5】描述返回的元组结构
TupleDesc tupDesc; /* descriptor for result tuples */
/* and these are the format codes to use for the columns: */
int16 *formats; /* a format code for each column */
...
} PortalData;
from 《PostgreSQL数据库内核分析》
简版:
exec_simple_query
|
PortalStart
|
PortalRun
|
PortalDrop
执行一些初始化工作,比如
PortalStart
portal->strategy = ChoosePortalStrategy(portal->stmts)
switch (portal->strategy)
{
case PORTAL_ONE_SELECT:
// 拿快照
PushActiveSnapshot
// 创建查询描述符
CreateQueryDesc
// 初始化Executor
ExecutorStart
case PORTAL_ONE_RETURNING:
case PORTAL_ONE_MOD_WITH:
PortalGetPrimaryStmt
ExecCleanTypeFromTL
case PORTAL_UTIL_SELECT:
PortalGetPrimaryStmt
UtilityTupleDescriptor
case PORTAL_MULTI_QUERY:
portal->tupDesc = NULL
}
portal->status = PORTAL_READY;
其中执行策略的选择ChoosePortalStrategy:
PortalRun是一级portal执行函数,负责分发给二级portal执行函数
PortalRun
switch (portal->strategy)
case PORTAL_ONE_SELECT:
case PORTAL_ONE_RETURNING:
case PORTAL_ONE_MOD_WITH:
case PORTAL_UTIL_SELECT:
PortalRunSelect
case PORTAL_MULTI_QUERY:
PortalRunMulti
PortalRun二级执行函数有四个,其中两个从PortalRun调入
PortalRunSelect <-- PortalRun
PortalRunMulti <-- PortalRun
PortalRun三级执行函数有两个,从PortalRunMulti调入
PortalRunUtility <-- PortalRunMulti
PortalRunFetch <-- (游标专用)从SPI系统_SPI_cursor_operation
<-- 或 standard_ProcessUtility的PerformPortalFetch 调入
执行过程:
清理
PortalDrop
PortalHashTableDelete
PortalReleaseCachedPlan
ResourceOwnerRelease
...
MemoryContextDelete(portal->portalContext)
pfree(portal)