CREATE TABLE table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] } [, ... ] ] )
where column_constraint is:
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ]
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
CREATE TABLE films ( code char(5), title varchar(40), did integer, date_prod date, kind varchar(10), len interval hour to minute);建表语句执行parse_analyze函数时进入传统的transform阶段时并没有执行任何trasform功能的函数，而是直接走transformStmt函数的default分支：创建Query节点，让原始语法树设置到其utilityStmt成员。执行pg_rewrite_query函数时不经过QueryRewrite函数，直接将Query节点作为querytree_list列表的元素返回。在各个点elog打印的语法树如下所示，可以说是完全一致的。
如下所示，对于create table基础建表语句，pg_plan_queries没有经过pg_plan_query函数，而是直接创建PlannedStmt节点，然后将query->utilityStmt设置到PlannedStmt节点的utilityStmt成员。
Portal流程
portal = CreatePortal("", true, true);函数创建PortalData结构体，并初始化其中的部分成员，比如portalContext、resowner等。PortalDefineQuery(portal, NULL, query_string, commandTag, plantree_list, NULL);函数主要工作是设置执行命令的commandTag、命令sourceText和执行计划PlannedStmts list到PortalData结构体相应成员中。PortalStart(portal, NULL, 0, InvalidSnapshot);首先暂存当前ActivePortal、CurrentResourceOwner、PortalContext，然后切换到新建portal中的资源中；调用ChoosePortalStrategy确定strategy，这里依旧为PORTAL_MULTI_QUERY，并设置portal->tupDesc成员为NULL；最后切换到暂存资源中。PortalSetResultFormat(portal, 1, &format)在这里运行时由于portal->tupDesc成员为NULL，直接return了，没有任何有效工作。PortalRun(portal, FETCH_ALL, true, /* always top level */ true, receiver, receiver, completionTag);函数首先调用MarkPortalActive(portal)设置portal->status为PORTAL_ACTIVE，更新portal->activeSubid；暂存当前TopTransactionResourceOwner、TopTransactionContext、ActivePortal、CurrentResourceOwner、PortalContext、CurrentMemoryContext，切换到新建portal中的资源中；由于strategy为PORTAL_MULTI_QUERY，所以执行PortalRunMulti函数；最终执行MarkPortalDone(portal)函数，设置portal->status为PORTAL_DONE，并执行cleanup回调函数；切换到暂存资源中。PortalDrop(portal, false)函数最后执行清理portal申请的相应资源。
typedef struct PortalData{
/* Bookkeeping data */
const char *name; /* portal's name */
MemoryContext portalContext;/* subsidiary memory for portal */ // CreatePortal时创建
ResourceOwner resowner;/* resources owned by portal */ // CreatePortal时创建
void (*cleanup) (Portal portal);/* cleanup hook */ // CreatePortal时设置为PortalCleanup
SubTransactionId createSubid;/* the creating subxact */ // CreatePortal时设置为GetCurrentSubTransactionId()
SubTransactionId activeSubid;/* the last subxact with activity */ // CreatePortal时设置为GetCurrentSubTransactionId()
const char *prepStmtName; /* source prepared statement (NULL if none) */ // PortalDefineQuery时设置
/* The query or queries the portal will execute */
const char *sourceText; /* text of query (as of 8.4, never NULL) */ // PortalDefineQuery时设置
const char *commandTag; /* command tag for original query */ // PortalDefineQuery时设置
List *stmts; /* list of PlannedStmts */ // PortalDefineQuery时设置
CachedPlan *cplan; /* CachedPlan, if stmts are from one */ // PortalDefineQuery时设置
ParamListInfo portalParams; /* params to pass to query */ // PortalStart时设置
QueryEnvironment *queryEnv; /* environment for query */
/* Features/options */
PortalStrategy strategy;// CreatePortal时设置为PORTAL_MULTI_QUERY; PortalStart时调用ChoosePortalStrategy设置
int cursorOptions;/* DECLARE CURSOR option bits */ // CreatePortal时设置为CURSOR_OPT_NO_SCROLL
bool run_once; /* portal will only be run once */ // PortalRun时设置
/* Status data */
PortalStatus status; // CreatePortal时设置为PORTAL_NEW；PortalDefineQuery时设置PORTAL_DEFINED；PortalStart时设置PORTAL_READY;PortalRun时设置为PORTAL_ACTIVE
bool portalPinned; /* a pinned portal can't be dropped */
bool autoHeld; /* was automatically converted from pinned to held (see HoldPinnedPortals()) */
/* If not NULL, Executor is active; call ExecutorEnd eventually: */
QueryDesc *queryDesc; /* info needed for executor invocation */
/* If portal returns tuples, this is their tupdesc: */
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 */
/* Where we store tuples for a held cursor or a PORTAL_ONE_RETURNING or PORTAL_UTIL_SELECT query. (A cursor held past the end of its transaction no longer has any active executor state.) */
Tuplestorestate *holdStore; /* store for holdable cursors */
MemoryContext holdContext; /* memory containing holdStore */
Snapshot holdSnapshot; /* registered snapshot, or NULL if none */
bool atStart;// CreatePortal时设置为true
bool atEnd;// CreatePortal时设置为true
uint64 portalPos;
/* Presentation data, primarily used by the pg_cursors system view */
TimestampTz creation_time;/* time at which this portal was defined */ // CreatePortal时设置为GetCurrentStatementStartTimestamp
bool visible;/* include this portal in pg_cursors? */ // CreatePortal时设置为true，结束CreatePortal后设置为false
} PortalData;
把PortalRunMulti函数的执行流程单抽出来看一下，foreach从portal->stmts列表中抽取出PlannedStmt元素，如果utilityStmt不为null,说明是处理的utility函数（比如create, destroy, etc…）,因此走else分支；由于canSetTag为true，故运行PortalRunUtility(portal, pstmt, isTopLevel, false, dest, completionTag)函数。
PortalRunUtility函数在运行ProcessUtility(standard_ProcessUtility)之前需要根据传入的utilityStmt判定一下是否需要获取快照，以及是否需要将快照设置到portal->holdSnapshot进行暂存，以备portal后续使用。从下图堆栈可以看出，需要获取快照，但无需暂存。
ProcessUtilitySlow流程
standard_ProcessUtility函数首先提取出PlannedStmt中的utilityStmt计划树，同时确定isTopLevel和isAtomicContext值，新建ParseState结构体，给ProcessUtilitySlow函数暂存中间结果。switch(nodeTag(parsetree))走的是default分支。
ProcessUtilitySlow函数的执行流程如下：首先从pstmt中取出utilityStmt解析树（由于没有经过transform、rewrite、planner流程，所以还是解析树），确定needCleanup为false、isTopLevel为true、isCompleteQuery为true；首先执行EventTriggerDDLCommandStart(parsetree)，然后走建表流程【transform解析树、针对CreateStmt进行处理(DefineRelation、EventTriggerCollectSimpleCommand、transformRelOptions、heap_reloptions、NewRelationCreateToastTable)】；最后执行EventTriggerSQLDrop和EventTriggerDDLCommandEnd流程。
ProcessUtilitySlow (pstate=0x236fd00, pstmt=0x2408578,
queryString=0x234add0 "CREATE TABLE films ( code char(5), title varchar(40), did integer, date_prod date, kind varchar(10), len interval hour t o minute);", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x2408658, completionTag=0x7ffcf8fb0e60 "") at utility.c:958
958 Node *parsetree = pstmt->utilityStmt;
(gdb) n
959 bool isTopLevel = (context == PROCESS_UTILITY_TOPLEVEL);
(gdb) n
960 bool isCompleteQuery = (context != PROCESS_UTILITY_SUBCOMMAND);
(gdb) n
962 bool commandCollected = false;
(gdb) n
964 ObjectAddress secondaryObject = InvalidObjectAddress;
(gdb) n
967 needCleanup = isCompleteQuery && EventTriggerBeginCompleteQuery();
(gdb) n
970 PG_TRY();
(gdb) n
972 if (isCompleteQuery)
(gdb) n
973 EventTriggerDDLCommandStart(parsetree);
(gdb) n
975 switch (nodeTag(parsetree))
(gdb) n
997 RangeVar *table_rv = NULL;
(gdb) n
1000 stmts = transformCreateStmt((CreateStmt *) parsetree,
(gdb) n
1008 while (stmts != NIL)
(gdb) n
1010 Node *stmt = (Node *) linitial(stmts);
(gdb) n
1012 stmts = list_delete_first(stmts);
(gdb) n
1014 if (IsA(stmt, CreateStmt))
(gdb) n
1016 CreateStmt *cstmt = (CreateStmt *) stmt;
(gdb) n
1021 table_rv = cstmt->relation;
(gdb) n
1024 address = DefineRelation(cstmt,
(gdb) n
1028 EventTriggerCollectSimpleCommand(address,
(gdb) n
1036 CommandCounterIncrement();
(gdb) n
1042 toast_options = transformRelOptions((Datum) 0,
(gdb) n
1048 (void) heap_reloptions(RELKIND_TOASTVALUE,
(gdb) n
1052 NewRelationCreateToastTable(address.objectId,
(gdb) n
1115 if (stmts != NIL)
(gdb) n
1008 while (stmts != NIL)
(gdb) n
1123 commandCollected = true;
(gdb) n
1125 break;
(gdb) n
1748 if (!commandCollected)
(gdb) n
1752 if (isCompleteQuery)
(gdb) n
1754 EventTriggerSQLDrop(parsetree);
(gdb) n
1755 EventTriggerDDLCommandEnd(parsetree);
(gdb) n
1764 PG_END_TRY();
(gdb) n
1766 if (needCleanup)
(gdb) n
1768 }
(gdb) n
CREATE TABLE films (
code char(5) CONSTRAINT firstkey PRIMARY KEY,
title varchar(40) NOT NULL,
did integer NOT NULL,
date_prod date,
kind varchar(10),
len interval hour to minute
);
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]
table_name ( [ { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint | LIKE source_table [ like_option ... ] } [, ... ] ] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ]
table_name OF type_name [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint } [, ... ] ) ]
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint } [, ... ] ) ] { FOR VALUES partition_bound_spec | DEFAULT }
[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ USING method ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] | UNIQUE ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and like_option is:
{ INCLUDING | EXCLUDING }
{ COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
and partition_bound_spec is:
IN ( partition_bound_expr [, ...] ) |
FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) }
[ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
1515
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
