PostgreSQL——查询重写——删除重写规则以及对查询树进行重写

2021SC@SDUSC

概述

我负责的PostgreSQL代码部分：查询的编译与执行
此篇博客的分析内容：查询重写的处理操作——删除重写规则和查询树的重写
在上篇博客中，我详细的介绍了查询重写的处理操作中的一部分——定义规则。既然知道了如何定义规则之后，那如果规则不需要了或者错误了，就需要了解如何删除重写规则。所以我再详细的介绍一下删除重写规则

查询重写——删除重写规则

经过网上查询相关资料发现，在原来的PostgreSQL版本里删除重写规则有两种方式可选分别是：根据规则的名字删除和根据规则的oid删除。使用drop rule命令删除规则时，默认是根据规则名删除的。但是，在PostgreSQL12.0版本中，搜索不到根据规则名称删除的函数——RemoveRewriteRule而只有根据规则oid删除的函数——RemoveRewriteRuleByld。我个人猜测，可能根据名字删除会不会导致重名规则等其他规则被删除，这样可能没有根据唯一标识规则的oid删除更准确。（纯属个人推测，不认同的朋友或者有其他想法的朋友，欢迎留言交流）

删除重写规则的主要函数——RemoveRewriteRuleById函数

void
RemoveRewriteRuleById(Oid ruleOid)
{
	Relation	RewriteRelation;
	ScanKeyData skey[1];
	SysScanDesc rcscan;
	Relation	event_relation;
	HeapTuple	tuple;
	Oid			eventRelationOid;

//打开系统表pg_rewrite，并对pg_rewrite系统表加RowExclusiveLock锁，从而防止在该表上执行insert，update，delete操作
	RewriteRelation = table_open(RewriteRelationId, RowExclusiveLock);

//利用制定规则的oid，在系统表pg_rewrite中找到该规则所对应的元组
	//初始化scankey
	ScanKeyInit(&skey[0],
				Anum_pg_rewrite_oid,
				BTEqualStrategyNumber, F_OIDEQ,
				ObjectIdGetDatum(ruleOid));
//ObjectIdGetDatum(ruleOid)该函数根据规则oid取到数据

	//扫描系统表pg_rewrite，找到给定的规则oid对应的元组
	rcscan = systable_beginscan(RewriteRelation, RewriteOidIndexId, true,
								NULL, 1, skey);
    //根据rcscan获得元组所对应的数据
	tuple = systable_getnext(rcscan);
    //如果给定的规则oid在系统表中找不到则报错
	if (!HeapTupleIsValid(tuple))
		elog(ERROR, "could not find tuple for rule %u", ruleOid);

	//根据元组中的ev_class字段获取该规则所作用的关联表，并对该表添加AccessExclusiveLock锁，防止对它进行修改和删除等操作
	eventRelationOid = ((Form_pg_rewrite) GETSTRUCT(tuple))->ev_class;//根据元组中的ev_class字段获取该规则所作用的关联表的oid
	
	event_relation = table_open(eventRelationOid, AccessExclusiveLock);//对表添加AccessExclusiveLock锁，防止对它进行修改和删除等操作
	//删除系统表pg_rewrite中该规则所对应的元组，并关闭该系统表
	CatalogTupleDelete(RewriteRelation, &tuple->t_self);
	
    //结束刚才的扫描
	systable_endscan(rcscan);
	
   //关闭系统表pg_rewrite，并释放锁
	table_close(RewriteRelation, RowExclusiveLock);
	
	//更新该规则所作用的表的RelationData结构
	CacheInvalidateRelcache(event_relation);
	//关闭关联表，并释放锁
	table_close(event_relation, NoLock);
}

查询重写—— 对查询树进行重写

PostgreSQL会调用函数QueryRewrite来完成查询树的重写。

QueryRewrite函数

QueryRewrite(Query *parsetree)
{
	uint64		input_query_id = parsetree->queryId;//查询id
	List	   *querylist;//查询树链表
	List	   *results;//结果集
	ListCell   *l;//链表节点
	CmdType		origCmdType;//原始命令类型
	bool		foundOriginalQuery;//是否找到原始查询树
	Query	   *lastInstead;//重写的查询树

	//判断是否为原始的查询树
	Assert(parsetree->querySource == QSRC_ORIGINAL);
	//canSetTag：查询重写时用到，如果该Query是由原始查询转换而来则此字段为假，如果Query是由查询重写或查询规划时新增加的则此字段为真
	Assert(parsetree->canSetTag);

	//调用RewriteQuery函数，利用非select规则，将一个查询重写为0个或多个查询
	querylist = RewriteQuery(parsetree, NIL);

	//通过调用函数fireRIRrules，对上一步得到的每个查询分别用RIR规则（无条件instead规则，并且只能有一个select规则动作）重写
	//result置空
	results = NIL;
	foreach(l, querylist)
	{
		Query	   *query = (Query *) lfirst(l);
		//上一步得到的每个查询分别用RIR规则（无条件instead规则，并且只能有一个select规则动作）重写

		query = fireRIRrules(query, NIL);
		//把重写结果加入到results结果集中

		results = lappend(results, query);
	}

QueryRewrite函数，将fireRIRrules函数返回的查询树组织成一个链表，对他们的canSetTag字段进行设置之后，返回给pg_rewrite_query。到这里整个查询重写工作，就结束了。最终生成的是一个有多棵查询树组成的查询树链表，该链表将会传递给查询规划模块，形成执行计划。

	//将查询树作为查询重写的结果返回
	origCmdType = parsetree->commandType;//将原始查询树的命令类型记录下来
	foundOriginalQuery = false;//foundOriginalQuery变量记录是否找到原始查询树
	lastInstead = NULL;

	foreach(l, results)//遍历查询树
	{
		Query	   *query = (Query *) lfirst(l);//取左子树
        //判断结果集中是否为原始的查询树，即未被fireRIRrules函数重写
		if (query->querySource == QSRC_ORIGINAL)//如果找到原始查询树
		{
			Assert(query->canSetTag);
			Assert(!foundOriginalQuery);
			foundOriginalQuery = true;//找到原始查询树则将foundOriginalQuery 变量设为true
#ifndef USE_ASSERT_CHECKING
			break;
#endif
		}
		else//如果没找到原始查询树即已经被fireRIRrules函数重写
		{
			Assert(!query->canSetTag);
			//判断是否符合重写后的条件
			if (query->commandType == origCmdType &&
				(query->querySource == QSRC_INSTEAD_RULE ||
				 query->querySource == QSRC_QUAL_INSTEAD_RULE))
				lastInstead = query;
		}
	}
//如果已经进行重写并且符合条件的则将canSetTag设置为true进行标记
	if (!foundOriginalQuery && lastInstead != NULL)
	//canSetTag：查询重写时用到，如果该Query是由原始查询转换而来则此字段为假，如果Query是由查询重写或查询规划时新增加的则此字段为真
		lastInstead->canSetTag = true;
//返回查询树
	return results;
}

QueryRewrite函数调用的RewriteQuery函数——处理非select规则的重写

RewriteQuery函数的作用是执行非SELECT规则(也就是处理SELECT以外的语句)

static List *
RewriteQuery(Query *parsetree, List *rewrite_events)
{
	CmdType		event = parsetree->commandType;
	bool		instead = false;
	bool		returning = false;
	bool		updatableview = false;
	Query	   *qual_product = NULL;
	List	   *rewritten = NIL;
	ListCell   *lc1;
//首先寻找含有INSERT/UPDATE/DELETE的WITH子句(说白了就是CTE)。如果存在，那么就调用自身递归地重写它们;
//扫描查询树链表，处理WITH子句中的所有CTE
		foreach(lc1, parsetree->cteList)
	{
		CommonTableExpr *cte = lfirst_node(CommonTableExpr, lc1);
		Query	   *ctequery = castNode(Query, cte->ctequery);
		List	   *newstuff;
     //如果cte的命令类型为select则跳过不进行处理
		if (ctequery->commandType == CMD_SELECT)
			continue;
        //递归重写update，delete，insert三种类型的with子句
		newstuff = RewriteQuery(ctequery, rewrite_events);
//用重写后的query覆盖CTE中的查询，对CTE，只支持单条语句的DO INSTEAD规则，其他情况都报错
		//判断是否只有一个instead规则
		if (list_length(newstuff) == 1)
		{
			//把单个查询树写回cte节点
			ctequery = linitial_node(Query, newstuff);
			cte->ctequery = (Node *) ctequery;
		}
		//如果没有instead规则则报错
		else if (newstuff == NIL)
		{
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("DO INSTEAD NOTHING rules are not supported for data-modifying statements in WITH")));
		}
		//只支持单条语句的DO INSTEAD规则，其他情况都报错
		else
		{
			ListCell   *lc2;
			//遍历查询树，决定判断报出什么样的错误信息提示
			foreach(lc2, newstuff)
			{
				Query	   *q = (Query *) lfirst(lc2);
          //只支持单条语句的DO INSTEAD规则，其他情况都报错
				if (q->querySource == QSRC_QUAL_INSTEAD_RULE)
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("conditional DO INSTEAD rules are not supported for data-modifying statements in WITH")));
				if (q->querySource == QSRC_NON_INSTEAD_RULE)
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("DO ALSO rules are not supported for data-modifying statements in WITH")));
			}

			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("multi-statement DO INSTEAD rules are not supported for data-modifying statements in WITH")));
		}
	}
	 //判断如果查询语句不是SELECT和UTILITY
	if (event != CMD_SELECT && event != CMD_UTILITY)
	{
		int			result_relation;
		RangeTblEntry *rt_entry;
		Relation	rt_entry_relation;
		List	   *locks;
		List	   *product_queries;
		bool		hasUpdate = false;
		int			values_rte_index = 0;
		bool		defaults_remaining = false;
         //根据result_relation在范围表rtable中，找到对应的项rt_entry
		result_relation = parsetree->resultRelation;
		//result_relation范围表不能为空
		Assert(result_relation != 0);
		//在范围表rtable中取得rt_entry项
		rt_entry = rt_fetch(result_relation, parsetree->rtable);
		Assert(rt_entry->rtekind == RTE_RELATION);

		//打开rte对应的表
		rt_entry_relation = table_open(rt_entry->relid, NoLock);

		//对不同语句类型做分别处理：重写targetList
		//如果是insert规则类型
		if (event == CMD_INSERT)
		{
			RangeTblEntry *values_rte = NULL;

			//判断是否为单个rte
			if (list_length(parsetree->jointree->fromlist) == 1)
			{ //初始化目标链表
				RangeTblRef *rtr = (RangeTblRef *) linitial(parsetree->jointree->fromlist);
                 //判断初始化是否成功
				if (IsA(rtr, RangeTblRef))
				{
					RangeTblEntry *rte = rt_fetch(rtr->rtindex,
												  parsetree->rtable);
 						//如果有VALUES，则获得其对应的RTE
					if (rte->rtekind == RTE_VALUES)
					{
						values_rte = rte;
						values_rte_index = rtr->rtindex;
					}
				}
			}
			//如果上一步获得了对应的rte
			if (values_rte)
			{
				//重写INSERT的targetList
				parsetree->targetList = rewriteTargetListIU(parsetree->targetList,
															parsetree->commandType,
															parsetree->override,
															rt_entry_relation,
															parsetree->resultRelation);
				//重写VALUES，把DEFAULT变成缺省值表达式
				if (!rewriteValuesRTE(parsetree, values_rte, values_rte_index,
									  rt_entry_relation, false))
					defaults_remaining = true;
			}
			else//如果没有获得对应的rte
			{
				//重写INSERT的targetList（无values情况）
				parsetree->targetList =
					rewriteTargetListIU(parsetree->targetList,
										parsetree->commandType,
										parsetree->override,
										rt_entry_relation,
										parsetree->resultRelation);
			}
		}
		//如果是update规则类型
		else if (event == CMD_UPDATE)
		{ 重写update的targetList
			parsetree->targetList =
				rewriteTargetListIU(parsetree->targetList,
									parsetree->commandType,
									parsetree->override,
									rt_entry_relation,
									parsetree->resultRelation);
		}
		//如果是delete规则类型，则不进行任何操作
		else if (event == CMD_DELETE)
		{

		}
		else//如果是insert，update，delete类型之外的其他类型则报错
			elog(ERROR, "unrecognized commandType: %d", (int) event);

		//获得匹配的规则，规则保存在RelationData结构中的rd_rules，其类型是RuleLock(rewrite/prs2lock.h))
		locks = matchLocks(event, rt_entry_relation->rd_rules,
						   result_relation, parsetree, &hasUpdate);
         //用于对上面判断出来合适的规则进行循环处理
		product_queries = fireRules(parsetree,
									result_relation,
									event,
									locks,
									&instead,
									&returning,
									&qual_product);
		
		}

		 //如果没有INSTEAD规则，并且是个视图，该视图没有INSTEAD触发器) (该视图必须是可更新视图)
		if (!instead && qual_product == NULL &&
			rt_entry_relation->rd_rel->relkind == RELKIND_VIEW &&
			!view_has_instead_trigger(rt_entry_relation, event))
		{
				//rewriteTargetView函数内部判断是否为可更新视图，如果不是可更新视图则报错
			parsetree = rewriteTargetView(parsetree, rt_entry_relation);
			 //判断，如果是insert规则类型
			if (parsetree->commandType == CMD_INSERT)
			//把原来的query加到product_queries，加在前面或者后面
			//加到product_queries开头
				product_queries = lcons(parsetree, product_queries);
			else//加到product_queries结尾
				product_queries = lappend(product_queries, parsetree);

			//把instead和returning设为true，防止原始查询再次被包含在下面
			instead = true;
			returning = true;
			updatableview = true;
		}
		//product_queries是在fireRules函数中生成的所有规则的动作语句
		//判断product_queries是否为空
		if (product_queries != NIL)
		{
			ListCell   *n;
			rewrite_event *rev;
          //遍历rewrite_events
			foreach(n, rewrite_events)
			{
				rev = (rewrite_event *) lfirst(n);
				//检查规则是否是递归，如果是无穷递归则报错
				if (rev->relation == RelationGetRelid(rt_entry_relation) &&
					rev->event == event)
					ereport(ERROR,
							(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
							 errmsg("infinite recursion detected in rules for relation \"%s\"",
									RelationGetRelationName(rt_entry_relation))));
			}
			//创建rewritten_event，并添加到列表rewritten_events的开头
			rev = (rewrite_event *) palloc(sizeof(rewrite_event));//分配内存
			rev->relation = RelationGetRelid(rt_entry_relation);//获取oid
			rev->event = event;//获取event
			rewrite_events = lcons(rev, rewrite_events);//添加到链表开头
			//重写规则中的动作语句
			foreach(n, product_queries)
			{
				Query	   *pt = (Query *) lfirst(n);
				List	   *newstuff;
               //调用RewriteQuery函数递归重写规则中的动作语句
				newstuff = RewriteQuery(pt, rewrite_events);
				//把重写结果加到rewritten列表中
				rewritten = list_concat(rewritten, newstuff);
			}
				//从rewritten_events的开头删除rewritten_event
			rewrite_events = list_delete_first(rewrite_events);
		}

		//如果有INSTEAD规则并且原始查询有RETURNING子句，而规则动作中没有RETURNING，则报错
		if ((instead || qual_product != NULL) &&
			parsetree->returningList &&
			!returning)
		{
			switch (event)
			{
				case CMD_INSERT:
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("cannot perform INSERT RETURNING on relation \"%s\"",
									RelationGetRelationName(rt_entry_relation)),
							 errhint("You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.")));
					break;
				case CMD_UPDATE:
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("cannot perform UPDATE RETURNING on relation \"%s\"",
									RelationGetRelationName(rt_entry_relation)),
							 errhint("You need an unconditional ON UPDATE DO INSTEAD rule with a RETURNING clause.")));
					break;
				case CMD_DELETE:
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("cannot perform DELETE RETURNING on relation \"%s\"",
									RelationGetRelationName(rt_entry_relation)),
							 errhint("You need an unconditional ON DELETE DO INSTEAD rule with a RETURNING clause.")));
					break;
				default:
					elog(ERROR, "unrecognized commandType: %d",
						 (int) event);
					break;
			}
		}

	//如果没有unqualified INSTEAD规则
	if (!instead)
	{   //判断是否为insert类型
		if (parsetree->commandType == CMD_INSERT)
		{
			if (qual_product != NULL)
			//把quad_product或者原来的query添加到rewritten列表的开头
				rewritten = lcons(qual_product, rewritten);
			else
			//把quad_product或者原来的query添加到rewritten列表的结尾
				rewritten = lcons(parsetree, rewritten);
		}
		else
		{
			if (qual_product != NULL)
			//把quad_product或者原来的query添加到rewritten列表
				rewritten = lappend(rewritten, qual_product);
			else//把parsetree添加到rewritten列表
				rewritten = lappend(rewritten, parsetree);
		}
	}
//如果重写结果包括多个非utility语句，并且原来的query中有CTE则报错
//判断原来的query中有无cte
	if (parsetree->cteList != NIL)
	{
		int			qcount = 0;

		foreach(lc1, rewritten)
		{
			Query	   *q = (Query *) lfirst(lc1);
//判断是否包含多个非utility语句
			if (q->commandType != CMD_UTILITY)
				qcount++;
		}
		if (qcount > 1)
			ereport(ERROR,
					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
					 errmsg("WITH cannot be used in a query that is rewritten by rules into multiple queries")));
	}
//返回写过的rewritten列表
	return rewritten;
}

QueryRewrite函数调用的fireRIRrules函数——处理select规则的重写

fireRIRrules函数在每一个RTE上应用所有的RIR规则

static Query *
fireRIRrules(Query *parsetree, List *activeRIRs)
{
	int			origResultRelation = parsetree->resultRelation;//结果表
	int			rt_index;//RTE的index
	ListCell   *lc;//临时变量
//遍历该查询树的每一个范围表
	rt_index = 0;
	while (rt_index < list_length(parsetree->rtable))
	{
		RangeTblEntry *rte;//RTE
		Relation	rel;//范围表
		List	   *locks;//锁列表
		RuleLock   *rules;//规则锁
		RewriteRule *rule;//重写规则
		int			i;

		++rt_index;//索引+1

		rte = rt_fetch(rt_index, parsetree->rtable);//获取RTE

		//如果RTE是子查询，则递归调用fireRIRrules函数，对它进行重写
		if (rte->rtekind == RTE_SUBQUERY)
		{
			rte->subquery = fireRIRrules(rte->subquery, activeRIRs);
			continue;
		}

		//如果RTE不是一个表，则什么也不做，直接跳过
		if (rte->rtekind != RTE_RELATION)
			continue;

		//如果RTE是一个物化视图，则什么也不做，直接跳过
		if (rte->relkind == RELKIND_MATVIEW)
			continue;

		//打开范围表
		rel = table_open(rte->relid, NoLock);//根据relid获取"关系"

		//获取关系上我们需要的规则
		rules = rel->rd_rules;
		//如果规则不为空
		if (rules != NULL)
		{//则不上锁
			locks = NIL;
			for (i = 0; i < rules->numLocks; i++)
			{
				rule = rules->rules[i];//获取规则
				//如果是非SELECT类型的,则继续下一个规则
				if (rule->event != CMD_SELECT)
					continue;
               //添加到列表中
				locks = lappend(locks, rule);
			}

			//如果有要应用的规则
			if (locks != NIL)
			{
				ListCell   *l;
              //检查是否存在递归
				if (list_member_oid(activeRIRs, RelationGetRelid(rel)))
					//如果存在无穷的递归则报错
					ereport(ERROR,
							(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
							 errmsg("infinite recursion detected in rules for relation \"%s\"",
									RelationGetRelationName(rel))));
				activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs);

				foreach(l, locks)
				{
					rule = lfirst(l);
					//应用规则
					parsetree = ApplyRetrieveRule(parsetree,
												  rule,
												  rt_index,
												  rel,
												  activeRIRs);
				}
				//删除已应用的规则
				activeRIRs = list_delete_first(activeRIRs);
			}
		}

		table_close(rel, NoLock);//释放资源
	}

	//如果查询树还有子链接（子查询），则调用query_tree_walker函数对整个查询树的子链接进行遍历，并使用
	//fireRIRonSubLink函数进行重写处理，而该函数实际上也是调用fireRIRrules函数来完成重写工作。
	foreach(lc, parsetree->cteList) //WITH 语句处理
	{
		CommonTableExpr *cte = (CommonTableExpr *) lfirst(lc);

		cte->ctequery = (Node *)
		//递归调用fireRIRrules对它的查询树进行重写
			fireRIRrules((Query *) cte->ctequery, activeRIRs);
	}
	if (parsetree->hasSubLinks) //如果存在子链接，则调用query_tree_walker函数对整个查询树的子链接进行遍历，并使用
	//fireRIRonSubLink函数进行重写处理
		query_tree_walker(parsetree, fireRIRonSubLink, (void *) activeRIRs,
						  QTW_IGNORE_RC_SUBQUERIES);
		}
     //释放资源
		table_close(rel, NoLock);
	}
//返回重写后的查询树
	return parsetree;
}

总结

通过此篇源码分析，了解到了PostgreSQL查询重写的处理操作中的两个核心部分——删除重写规则和对查询树进行重写操作。

感谢批评指正