首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Postgresql源码(81)plpgsql中如何给占位符赋值(SPI进入执行器取值流程初步分析)

Postgresql源码(81)plpgsql中如何给占位符赋值(SPI进入执行器取值流程初步分析)

作者头像
mingjie
发布2022-09-26 21:55:01
6680
发布2022-09-26 21:55:01
举报

相关: 《Postgresql源码(56)可扩展类型分析ExpandedObject/ExpandedRecord》

本文探索raise notice 'sqlstate: %', sqlstate;时,%的值是怎么拿到的。

0 总结

  • plpgsql中通过SPI调用server语法解析时不需要加select,例如src="sqlstate"就可以直接跑,不需要`src=“select sqlstate”。gram.y中可以匹配按a_expr解析。
  • 取值流程:
- 入口:paramvalue = exec\_eval\_expr
- `[1]` 生成执行计划exec\_prepare\_plan      
    - 在pg\_analyze\_and\_rewrite\_params开始时会调钩子配置ParseState:用plpgsql\_parser\_setup给ParseState的四个变量赋予三个钩子函数,一个变量expr。
    - 在优化器里面会用前面配置的回调函数plpgsql\_post\_column\_ref识别sqlstate列的类型。
- `[2]` 执行执行计划exec\_eval\_simple\_expr      
    - 进入表达式执行分支:ExecEvalExpr
    - 表达式为PARAM回调类型,走EEOP\_PARAM\_CALLBACK分支回调PL的plpgsql\_param\_eval\_var\_ro拿值,值拼接成扩展类型返回:[《Postgresql源码(56)可扩展类型分析ExpandedObject/ExpandedRecord》](https://blog.csdn.net/jackgo73/article/details/125372466)
exec_eval_expr
[1] exec_prepare_plan
      SPI_prepare_extended(expr->query, &options);
        _SPI_begin_call
        _SPI_prepare_plan
          raw_parser
          pg_analyze_and_rewrite_params
[2] exec_eval_simple_expr
      ExecInitExprWithParams
        ExecEvalExpr
          ExecInterpExprStillValid
            ExecInterpExpr
              EEO_CASE(EEOP_PARAM_CALLBACK)
                plpgsql_param_eval_var_ro
                  var = (PLpgSQL_var *) estate->datums[dno];
                  *op->resvalue = MakeExpandedObjectReadOnly(var->value, var->isnull, -1);

1 案例

本文探索raise notice 'sqlstate: %', sqlstate;时,%的值是怎么拿到的。

do $g$
BEGIN
  RAISE division_by_zero;
EXCEPTION
    WHEN division_by_zero THEN
        raise notice 'sqlstate: %', sqlstate;
        raise notice 'sqlerrm: %', sqlerrm;
END;
$g$;

-- NOTICE:  sqlstate: 22012
-- NOTICE:  sqlerrm: division_by_zero

2 执行raise notice时如何给%赋值

当前PLpgSQL_stmt_raise的值:

PLpgSQL_stmt_raise
{cmd_type = PLPGSQL_STMT_RAISE, 
 lineno = 6, stmtid = 2, elog_level = 18, 
 condname = 0x0, 
 message = 0x104d338 "sqlstate: %", 
 params = 0x104d860,       
   -> [List] 
     -> [PLpgSQL_expr] 
       --> {query = 0x104d838 "sqlstate", parseMode = RAW_PARSE_PLPGSQL_EXPR, 
            plan = 0x0, paramnos = 0x0, func = 0x0, ns = 0x104d1f8, 
            expr_simple_expr = 0x0, expr_simple_type = 0, expr_simple_typmod = 0, 
            expr_simple_mutable = false, target_param = -1, expr_rw_param = 0x0, 
            expr_simple_plansource = 0x0, expr_simple_plan = 0x0, expr_simple_plan_lxid = 0, 
            expr_simple_state = 0x0, expr_simple_in_use = false, expr_simple_lxid = 0}
 
 options = 0x0}

执行流程

exec_stmt_raise
  ...
  ...
	if (stmt->message)                                  // "sqlstate: %"
	{
		StringInfoData ds;
		ListCell   *current_param;
		char	   *cp;
		MemoryContext oldcontext;
		oldcontext = MemoryContextSwitchTo(stmt_mcontext); // 进入"PLpgSQL per-statement data"
		initStringInfo(&ds);
		MemoryContextSwitchTo(oldcontext);       

		current_param = list_head(stmt->params);

		for (cp = stmt->message; *cp; cp++)
		{
			if (cp[0] == '%')                                // "sqlstate: %" 匹配到 "%”
			{
				Oid			paramtypeid;
				int32		paramtypmod;
				Datum		paramvalue;
				bool		paramisnull;
				char	   *extval;

				...
				paramvalue = exec_eval_expr(estate,            // 进入 exec_eval_expr 下面展开分析
											(PLpgSQL_expr *) lfirst(current_param),
											&paramisnull,                    // 获取 参数为空?
											&paramtypeid,                    // 获取 参数类型?
											&paramtypmod);                   // 获取 返回类型?
                                                       // 拿到执行结果 Datum 指向 "22012"

				if (paramisnull)
					extval = "<NULL>";
				else
					extval = convert_value_to_string(estate,     // 结果转换为字符串 "22012"
													 paramvalue,
													 paramtypeid);
				appendStringInfoString(&ds, extval);           // 在"sqlstate: " 后面拼上 "22012"
				current_param = lnext(stmt->params, current_param);
				exec_eval_cleanup(estate);
			}
			else
				appendStringInfoChar(&ds, cp[0]);
		}

		/* should have been checked at compile time */
		if (current_param != NULL)
			elog(ERROR, "unexpected RAISE parameter list length");

		err_message = ds.data;
	}
  ...
  ...

重要函数:exec_eval_expr

入参:

expr -> [PLpgSQL_expr] 
       --> {query = 0x104d838 "sqlstate", parseMode = RAW_PARSE_PLPGSQL_EXPR, 
            plan = 0x0, paramnos = 0x0, func = 0x0, ns = 0x104d1f8, 
            expr_simple_expr = 0x0, expr_simple_type = 0, expr_simple_typmod = 0, 
            expr_simple_mutable = false, target_param = -1, expr_rw_param = 0x0, 
            expr_simple_plansource = 0x0, expr_simple_plan = 0x0, expr_simple_plan_lxid = 0, 
            expr_simple_state = 0x0, expr_simple_in_use = false, expr_simple_lxid = 0}

流程:

exec_eval_expr
  exec_prepare_plan(estate, expr, CURSOR_OPT_PARALLEL_OK)
    // 【第一步】拼SPIPrepareOptions options
    expr->func = estate->func;                    // 存执行信息的总结构
    options.parserSetup 
      = (ParserSetupHook) plpgsql_parser_setup;   // 给动态参数的获取装钩子
                                                    // 给执行器提供函数:plpgsql_pre_column_ref
                                                    // 给执行器提供函数:plpgsql_post_column_ref
                                                    // 给执行器提供函数:plpgsql_param_ref
                                                    // 给执行器提供变量:PLpgSQL_expr *expr
    options.parserSetupArg = (void *) expr;       // 挂上上面expr结构
    options.parseMode = expr->parseMode;
    options.cursorOptions = cursorOptions;

    // SPIPrepareOptions { 
    //   parserSetup = 0x7fc1755fa264 <plpgsql_parser_setup>,  
    //   parserSetupArg = 0x104d7a0,       --> parserSetup和parserSetupArg 是一对,Arg是给上面函数的参数
    //   parseMode = RAW_PARSE_PLPGSQL_EXPR, 
    //   cursorOptions = 2048}

    // 【第二步】执行query = "sqlstate"
    SPI_prepare_extended(expr->query, &options);

重要函数:SPI_prepare_extended

1 SPI_prepare_extended第一步:_SPI_begin_call 准备上下文、 拼接Plan
SPI_prepare_extended
  _SPI_begin_call     // 切换上下文 "SPI Proc" --> "SPI Exec"
  ...                 // 拼接_SPI_plan
  // _SPI_plan {
  //   magic = 569278163, 
  //   saved = false, 
  //   oneshot = false, 
  //   plancache_list = 0x0, 
  //   plancxt = 0x0, 
  //   parse_mode = RAW_PARSE_PLPGSQL_EXPR, 
  //   cursor_options = 2048,
  //   nargs = 0, 
  //   argtypes = 0x0, 
  //   parserSetup = 0x7fc1755fa264 <plpgsql_parser_setup>,   --> 给执行器的钩子,钩子需要下面的expr
  //   parserSetupArg = 0x104d7a0}                            --> PLpgSQL_expr
2 SPI_prepare_extended第二步:开始执行_SPI_prepare_plan
  _SPI_prepare_plan(src, &plan)                 // src = "sqlstate"
  1. 语法解析
    raw_parser(src, plan->parse_mode)           // plan->parse_mode = RAW_PARSE_PLPGSQL_EXPR
      (1) base_yylex->[736]->[MODE_PLPGSQL_EXPR]
      (2) base_yylex->[258]->[IDENT]
      (3) base_yylex->[0]
      (4) ColId: | unreserved_keyword { $$ = pstrdup($1); }
      (5) columnref: ColId { $$ = makeColumnRef($1, NIL, @1, yyscanner); }
            {type = T_ColumnRef, fields = 0x1047238, location = 0}
              fields -> [List] -> {type = T_String, val = {str = "sqlstate"}}
      (6) c_expr: | AexprConst { $$ = $1; }
      (7) a_expr: | a_expr TYPECAST Typename { $$ = makeTypeCast($1, $3, @2); }
      (8) target_el: | a_expr { $$ = makeNode(ResTarget); $$->val = (Node *)$1; $$->location = @1; }
      (9) target_list: | target_list ',' target_el { $$ = lappend($1, $3); }
      (10)opt_target_list: | { $$ = NULL; }
      ...
      raw_parsetree_list -> [List] ->
      {type = T_RawStmt, stmt = 0x1047368, stmt_location = 0, stmt_len = 0}
        stmt -> [SelectStmt] -> {type = T_SelectStmt, ..., targetList = 0x10472e8}
          targetList -> [List] 
                       -> [ResTarget] 
                         -> {type = T_ResTarget, val = 0x10471d8}
            val -> [ColumnRef] -> {type = T_ColumnRef, fields = 0x1047238}
              fields -> [List] -> {type = T_String, val = {str = "sqlstate"}}
  1. 优化器

通过钩子函数,成功构造Param {xpr = {type = T_Param}, paramkind = PARAM_EXTERN, paramid = 2, paramtype = 25, paramtypmod = -1, paramcollid = 100, location = 0}

    pg_analyze_and_rewrite_params(parsetree, src, plan->parserSetup, plan->parserSetupArg,...)
      // plpgsql_parser_setup给ParseState的四个变量赋予三个钩子函数,一个变量
      (*parserSetup) (pstate, parserSetupArg)
      
      transformTopLevelStmt
        transformOptionalSelectInto
          transformStmt
            transformSelectStmt
              transformTargetList
                transformTargetEntry
                  transformExpr
                    transformExprRecurse
                      transformColumnRef(ParseState *pstate, ColumnRef *cref)    
                        // ColumnRef {type = T_ColumnRef, fields = 0x1047238, location = 0}
                        // parserSetup的钩子在pg_analyze_and_rewrite_params第三、四个参数传入
                        // 如果配了钩子,直接返回钩子函数构造的node:pstate->p_pre_columnref_hook
                        // 进入PLPGSQL:
                        plpgsql_pre_column_ref   // 钩子进入plpgsql_pre_column_ref返回NULL
                        plpgsql_post_column_ref  // 钩子进入plpgsql_post_column_ref
                          resolve_column_ref
                            // 按A | A.B | A.B.C 解析出变量名字到name1、name2、name3
                            plpgsql_ns_lookup(...,name1,name2,name3,...)  // 查询变量名字
                            // nse->itemno = 1    cref->location = 0
                            // 构造Param {xpr = {type = T_Param}, paramkind = PARAM_EXTERN, 
                            //           paramid = 2, paramtype = 25, 
                            //           paramtypmod = -1, paramcollid = 100, location = 0}
                            // paramid = 2 == dnp + 1 记录参数位置
                            return make_datum_param(expr, nse->itemno, cref->location)
  1. 继续执行拿到SPIPlanPtr
  result = _SPI_make_plan_non_temp(&plan)
  _SPI_end_call(true)
  return result
  
    
[ SPIPlanPtr ]
{ magic = 569278163, saved = false, oneshot = false, 
  plancache_list = 0xf7b5e8, 
  plancxt = 0xf7b470, 
  parse_mode = RAW_PARSE_PLPGSQL_EXPR,
  cursor_options = 2048, 
  nargs = 0, argtypes = 0x0, 
  parserSetup = 0x7fc1755fa264 <plpgsql_parser_setup>, 
  parserSetupArg = 0x10459b0}
3 SPI_prepare_extended第三步:开始执行exec_eval_simple_expr
exec_eval_simple_expr
  ExecInitExprWithParams
  ExecEvalExpr
    ExecInterpExprStillValid
      ExecInterpExpr
        EEO_CASE(EEOP_PARAM_CALLBACK)
          plpgsql_param_eval_var_ro
            var = (PLpgSQL_var *) estate->datums[dno];
            *op->resvalue = MakeExpandedObjectReadOnly(var->value,
											   var->isnull,
											   -1);
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2022-09-24,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 0 总结
  • 1 案例
  • 2 执行raise notice时如何给%赋值
    • 执行流程
      • 重要函数:exec_eval_expr
        • 重要函数:SPI_prepare_extended
          • 1 SPI_prepare_extended第一步:_SPI_begin_call 准备上下文、 拼接Plan
          • 2 SPI_prepare_extended第二步:开始执行_SPI_prepare_plan
          • 3 SPI_prepare_extended第三步:开始执行exec_eval_simple_expr
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档