PostgreSQL源码分析——物化视图

作者：正经夜光杯 | 2024-06-26 06:48:35

踩

我们前面分析完视图后，这里再继续分析一下物化视图，其实现原理是不相同的，需要注意，物化视图等于是将返回的结果集缓存起来，而视图是查询重写，结果需要重新进行计算。

create materialized view matvt1 as select * from t1
1

语法解析部分

主流程如下：

exec_simple_query
--> pg_parse_query
    --> raw_parser
        --> base_yyparse
--> pg_analyze_and_rewrite
    --> parse_analyze
        --> transformStmt
            --> transformCreateTableAsStmt
                --> transformStmt   // 对查询语句进行语义分析,将其转换为查询树Query
    --> pg_rewrite_query
--> pg_plan_queries
1
2
3
4
5
6
7
8
9
10
11

定义物化视图的语法如下：

/*****************************************************************************
 *
 *		QUERY :
 *				CREATE MATERIALIZED VIEW relname AS SelectStmt
 *
 *****************************************************************************/
CreateMatViewStmt:
		CREATE OptNoLog MATERIALIZED VIEW create_mv_target AS SelectStmt opt_with_data
				{
					CreateTableAsStmt *ctas = makeNode(CreateTableAsStmt);  
					ctas->query = $7;
					ctas->into = $5;
					ctas->relkind = OBJECT_MATVIEW;
					ctas->is_select_into = false;
					ctas->if_not_exists = false;
					/* cram additional flags into the IntoClause */
					$5->rel->relpersistence = $2;
					$5->skipData = !($8);
					$$ = (Node *) ctas;
				}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

创建物化视图的语法，抽象语法树表示CreateTableAsStmt，创建物化视图的流程与CREATE TABLE AS 相同，等于新创建一个表（UNLOGGED TABLE），保存查询到的结果集。可以看到SELECT INTO，与CREATE TABLE AS也是用此进行表示。

/* ----------------------
 *		CREATE TABLE AS Statement (a/k/a SELECT INTO)
 *
 * A query written as CREATE TABLE AS will produce this node type natively.
 * A query written as SELECT ... INTO will be transformed to this form during
 * parse analysis.
 * A query written as CREATE MATERIALIZED view will produce this node type,
 * during parse analysis, since it needs all the same data.
 *
 * The "query" field is handled similarly to EXPLAIN, though note that it
 * can be a SELECT or an EXECUTE, but not other DML statements.
 * ----------------------
 */
typedef struct CreateTableAsStmt
{
	NodeTag		type;
	Node	   *query;			/* the query (see comments above) */
	IntoClause *into;			/* destination table */
	ObjectType	relkind;		/* OBJECT_TABLE or OBJECT_MATVIEW */
	bool		is_select_into; /* it was written as SELECT INTO */
	bool		if_not_exists;	/* just do nothing if it already exists? */
} CreateTableAsStmt;

/*
 * IntoClause - target information for SELECT INTO, CREATE TABLE AS, and
 * CREATE MATERIALIZED VIEW
 *
 * For CREATE MATERIALIZED VIEW, viewQuery is the parsed-but-not-rewritten
 * SELECT Query for the view; otherwise it's NULL.  (Although it's actually
 * Query*, we declare it as Node* to avoid a forward reference.)
 */
typedef struct IntoClause
{
	NodeTag		type;

	RangeVar   *rel;			/* target relation name */
	List	   *colNames;		/* column names to assign, or NIL */
	char	   *accessMethod;	/* table access method */
	List	   *options;		/* options from WITH clause */
	OnCommitAction onCommit;	/* what do we do at COMMIT? */
	char	   *tableSpaceName; /* table space to use, or NULL */
	Node	   *viewQuery;		/* materialized view's SELECT query */
	bool		skipData;		/* true for WITH NO DATA */
} IntoClause;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

执行部分——创建物化视图

主流程如下：

exec_simple_query
--> pg_parse_query
--> pg_analyze_and_rewrite
--> pg_plan_queries
--> PortalStart
--> PortalRun
    --> PortalRunUtility    // Execute a utility statement inside a portal.
        --> ProcessUtility
            --> standard_ProcessUtility
                --> ProcessUtilitySlow
                    /*
                     * 执行步骤:
                     *         1. 创建表，准备存储结果集
                     *         2. 查询重写(物化视图中的查询语句)
                     *         3. 生成查询的执行计划
                     *         4. 执行获取查询语句的结果集
                     */
                    --> ExecCreateTableAs  
                        // Create the tuple receiver object and insert info it will need
                        -->  CreateIntoRelDestReceiver  // 结果集输入到IntoRel中，新建的表中
                        --> QueryRewrite
                        --> pg_plan_query
                            --> standard_planner
                                --> subquery_planner
                                    --> grouping_planner
                                        --> query_planner
                                            --> make_one_rel
                                --> create_plan
                                    --> create_scan_plan
                        --> CreateQueryDesc  /* Create a QueryDesc, redirecting output to our tuple receiver */
                        --> ExecutorStart
                        --> ExecutorRun
                            --> standard_ExecutorRun
                                // 1. 建表
                                --> intorel_startup
                                    --> create_ctas_internal    //Actually create the target table
                                        --> DefineRelation // 建表
                                            --> heap_create_with_catalog
                                                --> heap_create
                                        --> StoreViewQuery  // Use the rules system to store the query for the view.
                                            --> UpdateRangeTableOfViewParse 
                                            --> DefineViewRules
                                                --> DefineQueryRewrite  // Set up the ON SELECT rule.
                                                    --> InsertRule // 插入的规则，重写为新的物化表，并不是源表
                                    --> SetMatViewPopulatedState
                                // 2. 执行查询语句，结果集存入物化的表中
                                --> ExecutePlan
                                    --> ExecScan    // 扫描获取tuple
                                        --> ExecScanFetch
                                            --> SeqNext
                                                --> table_beginscan
                                    --> intorel_receive  // receive one tuple
                                        --> table_tuple_insert  // 将查询到的tuple slot插入到创建的表中
                                            --> heapam_tuple_insert
                                                --> ExecFetchSlotHeapTuple
                                                    --> tts_buffer_heap_materialize
                                                        --> heap_copytuple
                                                    --> tts_buffer_heap_get_heap_tuple
                                                --> heap_insert // 插入到表中，找到指定的page，插入tuple。
                                                    --> heap_prepare_insert
                                                    --> RelationPutHeapTuple    
                        --> ExecutorEnd
             
--> PortalDrop
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

对于结果集中如何存入物化的新表中，可查看dest.c、createas.c等源码，查询到的结果可以按照需求发送到不同的地方，可查看下面的枚举，可以看到有个DestIntoRel的值，即使将结果send to relation。

/* ----------------
 *		CommandDest is a simplistic means of identifying the desired
 *		destination.  Someday this will probably need to be improved.
 *
 * Note: only the values DestNone, DestDebug, DestRemote are legal for the
 * global variable whereToSendOutput.   The other values may be used
 * as the destination for individual commands.
 * ----------------
 */
typedef enum
{
	DestNone,					/* results are discarded */
	DestDebug,					/* results go to debugging output */
	DestRemote,					/* results sent to frontend process */
	DestRemoteExecute,			/* sent to frontend, in Execute command */
	DestRemoteSimple,			/* sent to frontend, w/no catalog access */
	DestSPI,					/* results sent to SPI manager */
	DestTuplestore,				/* results sent to Tuplestore */
	DestIntoRel,				/* results sent to relation (SELECT INTO) */
	DestCopyOut,				/* results sent to COPY TO code */
	DestSQLFunction,			/* results sent to SQL-language func mgr */
	DestTransientRel,			/* results sent to transient relation */
	DestTupleQueue				/* results sent to tuple queue */
} CommandDest;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

其中还有一个非常重要的函数需要列出来CreateIntoRelDestReceiver，查询返回的结果输入到IntoClause节点指定的表中。

/*
 * CreateIntoRelDestReceiver -- create a suitable DestReceiver object
 *
 * intoClause will be NULL if called from CreateDestReceiver(), in which
 * case it has to be provided later.  However, it is convenient to allow
 * self->into to be filled in immediately for other callers.
 */
DestReceiver *
CreateIntoRelDestReceiver(IntoClause *intoClause)
{
	DR_intorel *self = (DR_intorel *) palloc0(sizeof(DR_intorel));

	self->pub.receiveSlot = intorel_receive;
	self->pub.rStartup = intorel_startup;
	self->pub.rShutdown = intorel_shutdown;
	self->pub.rDestroy = intorel_destroy;
	self->pub.mydest = DestIntoRel;
	self->into = intoClause;
	/* other private fields will be set during intorel_startup */

	return (DestReceiver *) self;
}

typedef struct
{
	DestReceiver pub;			/* publicly-known function pointers */
	IntoClause *into;			/* target relation specification */
	/* These fields are filled by intorel_startup: */
	Relation	rel;			/* relation to write to */
	ObjectAddress reladdr;		/* address of rel, for ExecCreateTableAs */
	CommandId	output_cid;		/* cmin to insert in output tuples */
	int			ti_options;		/* table_tuple_insert performance options */
	BulkInsertState bistate;	/* bulk insert state */
} DR_intorel;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

最后我们看一下系统表pg_class、pg_rewrite中的相关信息：

-- 物化视图matvt1
postgres@postgres=# select oid,relname,relkind,relhasrules from pg_class where relname='matvt1';
-[ RECORD 1 ]-------
oid         | 16391
relname     | matvt1
relkind     | m
relhasrules | t
-- 表t1
postgres@postgres=# select oid,relname,relkind,relhasrules,relrewrite from pg_class where relname='t1';
-[ RECORD 1 ]------
oid         | 16384     -- 表OID
relname     | t1        -- 表名
relkind     | r         -- 表示是普通表
relhasrules | f         -- 表是否定义了规则
relrewrite  | 0
-- 查看系统表pg_rewrite，查看插入的规则
postgres@postgres=# select * from pg_rewrite order by oid desc limit 1;
-[ RECORD 1 ]
oid        | 16394
rulename   | _RETURN
ev_class   | 16391
ev_type    | 1
ev_enabled | O
is_instead | t
ev_qual    | <>
ev_action  | ({QUERY :commandType 1 :querySource 0 :canSetTag true :utilityStmt <> :resultRelation 0 :hasAggs false :hasWindowFuncs false :hasTargetSRFs false :hasSubLinks false :hasDistinctOn false :hasRecursive false :hasModifyingCTE false :hasForUpdate false :hasRowSecurity false :cteList <> :rtable ({RTE :alias {ALIAS :aliasname old :colnames <>} :eref {ALIAS :aliasname old :colnames ("a" "b")} :rtekind 0 :relid 16391 :relkind m :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias {ALIAS :aliasname new :colnames <>} :eref {ALIAS :aliasname new :colnames ("a" "b")} :rtekind 0 :relid 16391 :relkind m :rellockmode 1 :tablesample <> :lateral false :inh false :inFromCl false :requiredPerms 0 :checkAsUser 0 :selectedCols (b) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>} {RTE :alias <> :eref {ALIAS :aliasname t1 :colnames ("a" "b")} :rtekind 0 :relid 16384 :relkind r :rellockmode 1 :tablesample <> :lateral false :inh true :inFromCl true :requiredPerms 2 :checkAsUser 0 :selectedCols (b 8 9) :insertedCols (b) :updatedCols (b) :extraUpdatedCols (b) :securityQuals <>}) :jointree {FROMEXPR :fromlist ({RANGETBLREF :rtindex 3}) :quals <>} :targetList ({TARGETENTRY :expr {VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 1 :location 42} :resno 1 :resname a :ressortgroupref 0 :resorigtbl 16384 :resorigcol 1 :resjunk false} {TARGETENTRY :expr {VAR :varno 3 :varattno 2 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 3 :varattnosyn 2 :location 42} :resno 2 :resname b :ressortgroupref 0 :resorigtbl 16384 :resorigcol 2 :resjunk false}) :override 0 :onConflict <> :returningList <> :groupClause <> :groupingSets <> :havingQual <> :windowClause <> :distinctClause <> :sortClause <> :limitOffset <> :limitCount <> :limitOption 0 :rowMarks <> :setOperations <> :constraintDeps <> :withCheckOptions <>})
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

物化视图与普通视图不同的地方在于，创建物化视图时，要建立一张物理表存储查询语句的结果集。

声明：本文内容由网友自发贡献，转载请注明出处：【wpsshop】