Postgresql源码（79）plpgsql中多层调用时参数传递关键点分析（pl参数）

mingjie

发布于 2022-09-26 21:54:43

1.2K0

发布于 2022-09-26 21:54:43

相关《Postgresql源码（77）plpgsql中参数传递和赋值（pl参数）》《Postgresql源码（78）plpgsql中调用call proc()时的参数传递和赋值（pl参数）》《Postgresql源码（79）plpgsql中多层调用时参数传递关键点分析（pl参数）》

总结

本篇回答下面问题

问题一：外层ExecuteCallStmt用什么构造参数列表fcinfo->args？

问题二：外层ExecuteCallStmt如何fcinfo->args构造流程？

问题三：内层ExecuteCallStmt用什么构造fcinfo->args？

问题四：内层ExecuteCallStmt如何fcinfo->args构造流程？

问题五：exec_stmt_block刚进入初始化哪些变量？

问题六：exec_stmt_block刚进入为什么要初始化哪些变量直接用不行吗？

问题七：内层函数执行完的变量是在哪赋给外层的？

用例

CREATE or replace PROCEDURE tp99(a9 in int,b9 in int,c9 out int)
LANGUAGE plpgsql
AS $$
BEGIN
  c9 := 88299;
  raise notice '=====tp99=====';
  raise notice 'a9: %', a9;
  raise notice 'b9: %', b9;
  raise notice 'c9: %', c9;
END;
$$;

CREATE or replace PROCEDURE tp88(a8 in integer, b8 out integer)
LANGUAGE plpgsql
AS $$
DECLARE
  i8 int;
  c8 int;
BEGIN
  i8 := 100;
  call tp99(i8,20,c8);
  raise notice '=====tp88=====';
  raise notice 'get from tp99 c8: %', c8;
END;
$$;

call tp88(1,2);

函数是从tp88调入tp99，有两个入参和一个出参，出参由内层函数赋值。

下面提到“外层”是指调用tp88，“内层”是指调用tp99。

问题索引

standard_ProcessUtility                               <---- （外层）调用tp88
  ExecuteCallStmt                                     <---- （外层）按x构造参数列表fcinfo->args，x从哪来？
    plpgsql_call_handler                                    （外层）ExecuteCallStmt如何fcinfo->args？
      plpgsql_exec_function                           <---- 按fcinfo->args初始化参数
        exec_toplevel_block
          exec_stmt_block                             <---- 按block->initvarnos初始化参数（哪来的？为什么初始化？）
            exec_stmts
              exec_stmt_call                          <---- 怎么构造新参数列表？
                SPI_execute_plan_extended             <---- SPI怎么使用？
                  _SPI_execute_plan
                    ProcessUtility
                      standard_ProcessUtility         <---- （内层）调用tp99
                        ExecuteCallStmt               <---- （内层）按x构造参数列表，x从哪来？
                          plpgsql_call_handler
                            plpgsql_exec_function
                              exec_toplevel_block
                                exec_stmt_block
                                  exec_stmts
                                    exec_stmt_raise

问题一：外层ExecuteCallStmt用什么构造fcinfo->args：CallStmt

1 ExecuteCallStmt关键变量CallStmt

plantree_list -> [List]
{type = T_List, length = 1, max_length = 5}
 \
  ListCell -> [PlannedStmt]
  {type = T_PlannedStmt, commandType = CMD_UTILITY,..., utilityStmt = 0x16600d8}
   \
    utilityStmt -> [CallStmt]
    {type = T_CallStmt, funccall = 0x1660080, funcexpr = 0x1660958, outargs = 0x1660a78}
      \
      funccall -> [FuncCall]  // from the parser
      {type = T_FuncCall, funcname = 0x165ff60, args = 0x165fff0,.,funcformat = COERCE_EXPLICIT_CALL}
      |
      funcexpr -> [FuncExpr]  // transformed call, with only input args
      {xpr = {type = T_FuncExpr}, funcid = 24972, funcresulttype = 2249, funcretset = false, 
       funcvariadic = false, funcformat = COERCE_EXPLICIT_CALL, args = 0x1660a20, location = 5}
      |
      outargs  -> [List]      // transformed output-argument expressions

CallStmt的三个变量funccall来自parser，funcexpr和outargs都是transformed加工后的结构，参数列表在funcexpr中，注意只有入参的参数列表。

CallStmt->funccall：保存全部参数，用A_Const存，没有类型信息，只是初始状态的值。

funccall -> [FuncCall]  // from the parser
{type = T_FuncCall, funcname = 0x165ff60, args = 0x165fff0,.,funcformat = COERCE_EXPLICIT_CALL}
  funcname -> [List] -> [String] -> "tp88"
  args     -> [List] -> [A_Const]  -> val = {type = T_Integer, val = {ival = 1, str = 0x1}}
                     -> [A_Const]  -> val = {type = T_Integer, val = {ival = 2, str = 0x2}}

CallStmt->funcexpr：只保存IN参数：用Const存带类型

funcexpr -> [FuncExpr] // transformed call, with only input args
{xpr = {type = T_FuncExpr}, funcid = 24972, funcresulttype = 2249, funcretset = false, 
       funcvariadic = false, funcformat = COERCE_EXPLICIT_CALL, args = 0x1660a20, location = 5}
  
  funcid -> 24972        ： 在系统表中找到函数添加到这里
  funcresulttype -> 2249 ： 注意procedure返回的是record类型，用来给Out参数填值。虽然不能直接return：
                            但是在C函数中还是能返回的。SQL函数直接把结果按列吐给out类型参数。

  args   -> [List] -> [Const] -> {consttype = 23, consttypmod = -1, constvalue = 1}
                   -> 没了，只有入参，没有出参

CallStmt->outargs：只保存OUT参数：用Const存带类型

outargs  -> [List]      // transformed output-argument expressions
  {consttype = 23, consttypmod = -1, constvalue = 2}

问题二：外层ExecuteCallStmt如何fcinfo->args构造流程？

外层拿到上述优化器返回的CallStmt结构后，开始用FuncExpr的解析过得参数列表拼接fcinfo->args。

核心逻辑是表达式执行，拿结果。

ExecuteCallStmt
  ...
	i = 0;
	foreach(lc, fexpr->args)
	{
    ...
		exprstate = ExecPrepareExpr(lfirst(lc), estate);                // 初始化表达式
		val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull);  // 运行表达式拿值

		fcinfo->args[i].value = val;
		fcinfo->args[i].isnull = isnull;

		i++;
	}

1 ExecEvalExprSwitchContext简单值的场景：call tp88(1,2);

输入：args   -> [List] -> [Const] -> {consttype = 23, consttypmod = -1, constvalue = 1}

进入：ExecEvalExprSwitchContext

  ExecEvalExprSwitchContext
    ExecInterpExprStillValid
      ExecJustConst： 直接赋值即可！

2 ExecEvalExprSwitchContext表达式的场景：call tp88(1+1,2);

输入：args   -> [List] -> [OpExpr] -> 
  { xpr = {type = T_OpExpr}, 
    opno = 551,         
    opfuncid = 177,     -> int4pl
    opresulttype = 23,  -> int4
    opretset = false, 
    opcollid = 0, 
    inputcollid = 0, 
    args = 0x1660478,   -> [List] -> [Const] -> {consttype = 23, constvalue = 1}
                                  -> [Const] -> {consttype = 23, constvalue = 1}
    location = 11}

数据准备：ExecPrepareExpr

ExecPrepareExpr
  expression_planner
    eval_const_expressions_mutator
----> case T_OpExpr:                  
        simplify_function
          evaluate_function
            evaluate_expr
              ExecEvalExprSwitchContext
                ExecInterpExprStillValid
                  ExecInterpExpr
                    int4pl

进入：ExecEvalExprSwitchContext

ExecEvalExprSwitchContext
  ExecInterpExprStillValid
    ExecJustConst

2 ExecEvalExprSwitchContext表达式的场景：call tp88(abs(-1),2);

输入： args   -> [List] -> [FuncExpr] -> 
  { xpr = {type = T_FuncExpr}, 
    funcid = 1397,                       -> abs
    funcresulttype = 23,                 -> int4
    funcretset = false, 
    funcvariadic = false, 
    funcformat = COERCE_EXPLICIT_CALL,
    funccollid = 0, 
    inputcollid = 0, 
    args = 0x1660460,           -> [Const] -> {consttype = 23, constvalue = 18446744073709551615}
    location = 10}

数据准备：ExecPrepareExpr

ExecPrepareExpr
  expression_planner
    eval_const_expressions
      eval_const_expressions_mutator
---->   case T_FuncExpr:
          simplify_function
            evaluate_function
              evaluate_expr
                ExecEvalExprSwitchContext
                  ExecInterpExprStillValid
                    ExecInterpExpr
                      int4abs

进入：ExecEvalExprSwitchContext

ExecEvalExprSwitchContext
  ExecInterpExprStillValid
    ExecJustConst

问题三：内层ExecuteCallStmt用什么构造fcinfo->args：CallStmt

进入内层堆栈

#0  ExecuteCallStmt (stmt=0x17afa68, params=0x176d370, atomic=false, dest=0xce9120 <spi_printtupDR>) at functioncmds.c:2203
#1  0x000000000097eea4 in standard_ProcessUtility (pstmt=0x17af9d0, queryString=0x1748ff8 "call tp99(10,20,c8)", readOnlyTree=true,  context=PROCESS_UTILITY_QUERY_NONATOMIC, params=0x176d370, queryEnv=0x0, dest=0xce9120 <spi_printtupDR>, qc=0x7ffd124d0230) at utility.c:850
#2  0x000000000097e69b in ProcessUtility (pstmt=0x17b1f80, queryString=0x1748ff8 "call tp99(10,20,c8)", readOnlyTree=true, context=PROCESS_UTILITY_QUERY_NONATOMIC, params=0x176d370, queryEnv=0x0, dest=0xce9120 <spi_printtupDR>, qc=0x7ffd124d0230) at utility.c:527
#3  0x0000000000795198 in _SPI_execute_plan (plan=0x1747f80, options=0x7ffd124d0390, snapshot=0x0, crosscheck_snapshot=0x0, fire_triggers=true) at spi.c:2703
#4  0x000000000079192d in SPI_execute_plan_extended (plan=0x1747f80, options=0x7ffd124d0390) at spi.c:731
#5  0x00007fde4870b560 in exec_stmt_call (estate=0x7ffd124d0670, stmt=0x17124b0) at pl_exec.c:2197
#6  0x00007fde4870afd0 in exec_stmts (estate=0x7ffd124d0670, stmts=0x1782d90) at pl_exec.c:1995
#7  0x00007fde4870ad56 in exec_stmt_block (estate=0x7ffd124d0670, block=0x17830a8) at pl_exec.c:1910
#8  0x00007fde4870a563 in exec_toplevel_block (estate=0x7ffd124d0670, block=0x17830a8) at pl_exec.c:1608
#9  0x00007fde487085b8 in plpgsql_exec_function (func=0x169f070, fcinfo=0x7ffd124d0990, simple_eval_estate=0x0, simple_eval_resowner=0x0, procedure_resowner=0x169ff18, atomic=false) at pl_exec.c:611
#10 0x00007fde48722943 in plpgsql_call_handler (fcinfo=0x7ffd124d0990) at pl_handler.c:277
#11 0x00000000006ae193 in ExecuteCallStmt (stmt=0x16600d8, params=0x0, atomic=false, dest=0x1781f20) at functioncmds.c:2311
#12 0x000000000097eea4 in standard_ProcessUtility (pstmt=0x1660738, queryString=0x165f4e0 "call tp88(1,2);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1781f20, qc=0x7ffd124d1290) at utility.c:850
#13 0x000000000097e69b in ProcessUtility (pstmt=0x1660738, queryString=0x165f4e0 "call tp88(1,2);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1781f20, qc=0x7ffd124d1290) at utility.c:527
#14 0x000000000097d297 in PortalRunUtility (portal=0x16fa040, pstmt=0x1660738, isTopLevel=true, setHoldSnapshot=true, dest=0x1781f20, qc=0x7ffd124d1290) at pquery.c:1155
#15 0x000000000097d000 in FillPortalStore (portal=0x16fa040, isTopLevel=true) at pquery.c:1028
#16 0x000000000097c972 in PortalRun (portal=0x16fa040, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x1660828, altdest=0x1660828, qc=0x7ffd124d1460) at pquery.c:760
#17 0x000000000097663b in exec_simple_query (query_string=0x165f4e0 "call tp88(1,2);") at postgres.c:1213
#18 0x000000000097ab59 in PostgresMain (argc=1, argv=0x7ffd124d16f0, dbname=0x1688cb0 "postgres", username=0x1688c88 "mingjiegao") at postgres.c:4494
#19 0x00000000008b6d4e in BackendRun (port=0x1680a80) at postmaster.c:4530
#20 0x00000000008b66cd in BackendStartup (port=0x1680a80) at postmaster.c:4252
#21 0x00000000008b2b45 in ServerLoop () at postmaster.c:1745
#22 0x00000000008b2417 in PostmasterMain (argc=1, argv=0x16590d0) at postmaster.c:1417
#23 0x00000000007b4c93 in main (argc=1, argv=0x16590d0) at main.c:209

内层函数的调用：call tp99(i8,20,c8);

其中三个参数：

i8是外层函数定义的变量，已赋值100。
20是常数。
c8是外层函数的变量，无值，c8位置出参。

1 内层CallStmt

[CallStmt]
    {type = T_CallStmt, funccall = 0x178ba70, funcexpr = 0x178bda8, outargs = 0x178bee8}
      \
      funccall -> [FuncCall]  // from the parser
      {type = T_FuncCall, funcname = 0x178bac8, args = 0x178bb68,.,funcformat = COERCE_EXPLICIT_CALL}
      |
      funcexpr -> [FuncExpr]  // transformed call, with only input args
      {xpr = {type = T_FuncExpr}, funcid = 24973, funcresulttype = 2249, funcretset = false, 
       funcvariadic = false, funcformat = COERCE_EXPLICIT_CALL, args = 0x178be00, location = 5}
      |
      outargs  -> [List]      // transformed output-argument expressions

funccall->args的值就很有意思了：对比外层调用的三个A_Const，这里有两个解析成了列引用类型。

funccall->args 
-> [List] 
  -> [ColumnRef] -> {type = T_ColumnRef, fields = 0x178bbf8, location = 10}
                                         -> [List] -> [Value] -> "i8"
  -> [A_Const]   -> {type = T_A_Const, val = {type = T_Integer, val = {ival = 20, str = 0x14}}
  -> [ColumnRef] -> {type = T_ColumnRef, fields = 0x178bd08, location = 16}
                                         -> [List] -> [Value] -> "c8"

在看看funcexpr->args：对比外层的一个Const，这里有两个入参，有一个需要取值的用Param表示。

funcexpr->args
-> [List]
  -> [Param] -> {xpr = {type = T_Param}, paramkind = PARAM_EXTERN, paramid = 5, 
                 paramtype = 23, paramtypmod = -1, paramcollid = 0, location = 10}
  -> [Const] -> {consttype = 23, constvalue = 20}

2 内层CallStmt哪来的？？

答案：源头Plan是从exec_stmt_call里面拼出来的，经过_SPI_execute_plan送入优化器，生产出来CallStmt

_SPI_execute_plan: 入参plan
  1. 从plan->plancache_list中拿到CachedPlanSource
  2. 遍历plancache_list调用GetCachedPlan拿到cplan，需要的话重新生成PLAN
  3. stmt_list = cplan->stmt_list
  4. 遍历stmt_list，执行ProcessUtility传入stmt，stmt里面已经带CallStmt了
  
  PlannedStmt {utilityStmt = 0x1761478 (CallStmt) }

_SPI_execute_plan的入参？哪来的？

SPIPlanPtr 0x177fbe0
  { magic = 569278163, 
    saved = true, 
    oneshot = false, 
    plancache_list = 0x177fc38, 
    plancxt = 0x177fac0, 
    parse_mode = RAW_PARSE_DEFAULT, 
    cursor_options = 0,
    nargs = 0, 
    argtypes = 0x0, 
    parserSetup = 0x7fde48705264 <plpgsql_parser_setup>, 
    parserSetupArg = 0x1783758}
    
 
 exec_stmt_call
   SPI_execute_plan_extended(expr->plan, &options)
   
 expr->plan 就是入参PLAN

后面看exec_stmt_call的时候看看PLAN是怎么生成的。

问题四：内层ExecuteCallStmt如何fcinfo->args构造流程？

内层拿到的funcexpr->args有一个Param，我们看看这个参数的值是怎么拿到的

funcexpr->args
-> [List]
  -> [Param] -> {xpr = {type = T_Param}, paramkind = PARAM_EXTERN, paramid = 5, 
                 paramtype = 23, paramtypmod = -1, paramcollid = 0, location = 10}
  -> [Const] -> {consttype = 23, constvalue = 20}

代码还是不变：

ExecuteCallStmt
  ...
	i = 0;
	foreach(lc, fexpr->args)
	{
    ...
		exprstate = ExecPrepareExpr(lfirst(lc), estate);                // 初始化表达式
		val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull);  // 运行表达式拿值

		fcinfo->args[i].value = val;
		fcinfo->args[i].isnull = isnull;

		i++;
	}

输入

[Param] -> {xpr = {type = T_Param}, paramkind = PARAM_EXTERN, paramid = 5, 
                 paramtype = 23, paramtypmod = -1, paramcollid = 0, location = 10}

ExecPrepareExpr：没做什么具体工作

ExecPrepareExpr
  expression_planner
    eval_const_expressions_mutator
----> case T_Param:
        return (Node *) copyObject(param)
  ExecInitExpr
    ExecInitExprSlots    ------ 执行表达式后面统一整理一篇
    ExecInitExprRec
    ExprEvalPushStep
    ExecReadyExpr

ExecEvalExprSwitchContext

ExecEvalExprSwitchContext
  ExecInterpExprStillValid
    ExecInterpExpr           ----- 表达式执行核心函数
----> EEO_CASE(EEOP_PARAM_EXTERN)
        ExecEvalParamExtern
          1. 拿到ParamListInfo：{paramFetch = 0x7fde48712d96 <plpgsql_param_fetch>, 
                                paramFetchArg = 0x7ffd124d0670, 
                                paramCompile = 0x7fde4871305c <plpgsql_param_compile>, 
                                paramCompileArg = 0x0, 
                                parserSetup = 0x7fde48705264 <plpgsql_parser_setup>, 
                                parserSetupArg = 0x1783758, 
                                paramValuesStr = 0x0, 
                                numParams = 6, 
                                params = 0x1743b90}
          2. 发现拿到ParamListInfo的拿数据钩子存在：paramFetch = 0x7fde48712d96 <plpgsql_param_fetch>
          3. 进入PL逻辑plpgsql_param_fetch拿值存入ParamExternData
          结束

不得不提ParamListInfo结构：

ParamListInfo用来给执行器提供参数，给那些带参数的执行计划运行使用。
ParamListInfo两种存在形式，静态和动态。
静态：ParamListInfo后面跟几个ParamExternData结构，每个保存参数值。
动态：ParamListInfo后面不跟ParamExternData，所有值用钩子拿回来。
注意：params数组是从1开始的。
三个钩子：
- ParamFetchHook：拿到指定ID的参数值。
- ParamCompileHook：控制编译Param节点，给执行器选择合适的eval函数。
- ParserSetupHook：重新给parsing的钩子挂上合适的函数。例如plpgsql_param_ref、plpgsql_pre_column_ref。

typedef struct ParamListInfoData
{
	ParamFetchHook paramFetch;	/* parameter fetch hook */
	void	   *paramFetchArg;
	ParamCompileHook paramCompile;	/* parameter compile hook */
	void	   *paramCompileArg;
	ParserSetupHook parserSetup;	/* parser setup hook */
	void	   *parserSetupArg;
	char	   *paramValuesStr; /* params as a single string for errors */
	int			numParams;		/* nominal/maximum # of Params represented */

	/*
	 * params[] may be of length zero if paramFetch is supplied; otherwise it
	 * must be of length numParams.
	 */
	ParamExternData params[FLEXIBLE_ARRAY_MEMBER];
}			ParamListInfoData;


当前值
{ paramFetch = 0x7fde48712d96 <plpgsql_param_fetch>, 
  paramFetchArg = 0x7ffd124d0670, 
  paramCompile = 0x7fde4871305c <plpgsql_param_compile>,
  paramCompileArg = 0x0, 
  parserSetup = 0x7fde48705264 <plpgsql_parser_setup>, 
  parserSetupArg = 0x1783758, 
  paramValuesStr = 0x0, 
  numParams = 6,
  params = 0x1743b90}

问题五：exec_stmt_block刚进入初始化哪些变量？所有当前block的var/rec类型值

看下外层函数

CREATE or replace PROCEDURE tp88(a8 in integer, b8 out integer)
LANGUAGE plpgsql
AS $$
DECLARE
  i8 int;
  c8 int;
BEGIN
  i8 := 100;
  call tp99(i8,20,c8);
  raise notice '=====tp88=====';
  raise notice 'get from tp99 c8: %', c8;
END;
$$;

call tp88(1,2);

在进入exec_stmt_block时：会有一些变量清空/赋默认值的逻辑，那么哪些应该初始化？如何决定的？

static int
exec_stmt_block(PLpgSQL_execstate *estate, PLpgSQL_stmt_block *block)
{
	volatile int rc = -1;
	int			i;

	/*
	 * First initialize all variables declared in this block
	 */
	estate->err_text = gettext_noop("during statement block local variable initialization");

	for (i = 0; i < block->n_initvars; i++)
	{
		int			n = block->initvarnos[i];
		PLpgSQL_datum *datum = estate->datums[n];

		/*
		 * The set of dtypes handled here must match plpgsql_add_initdatums().
		 *
		 * Note that we currently don't support promise datums within blocks,
		 * only at a function's outermost scope, so we needn't handle those
		 * here.
		 */
		switch (datum->dtype)
		{
			case PLPGSQL_DTYPE_VAR:
				{
					PLpgSQL_var *var = (PLpgSQL_var *) datum;

					/*
					 * Free any old value, in case re-entering block, and
					 * initialize to NULL
					 */
					assign_simple_var(estate, var, (Datum) 0, true, false);

					if (var->default_val == NULL)
					{
						/*
						 * If needed, give the datatype a chance to reject
						 * NULLs, by assigning a NULL to the variable.  We
						 * claim the value is of type UNKNOWN, not the var's
						 * datatype, else coercion will be skipped.
						 */
						if (var->datatype->typtype == TYPTYPE_DOMAIN)
							exec_assign_value(estate,
											  (PLpgSQL_datum *) var,
											  (Datum) 0,
											  true,
											  UNKNOWNOID,
											  -1);

						/* parser should have rejected NOT NULL */
						Assert(!var->notnull);
					}
					else
					{
						exec_assign_expr(estate, (PLpgSQL_datum *) var,
										 var->default_val);
					}
				}
				break;

			case PLPGSQL_DTYPE_REC:
				{
					PLpgSQL_rec *rec = (PLpgSQL_rec *) datum;

					/*
					 * Deletion of any existing object will be handled during
					 * the assignments below, and in some cases it's more
					 * efficient for us not to get rid of it beforehand.
					 */
					if (rec->default_val == NULL)
					{
						/*
						 * If needed, give the datatype a chance to reject
						 * NULLs, by assigning a NULL to the variable.
						 */
						exec_move_row(estate, (PLpgSQL_variable *) rec,
									  NULL, NULL);

						/* parser should have rejected NOT NULL */
						Assert(!rec->notnull);
					}
					else
					{
						exec_assign_expr(estate, (PLpgSQL_datum *) rec,
										 rec->default_val);
					}
				}
				break;

			default:
				elog(ERROR, "unrecognized dtype: %d", datum->dtype);
		}
	}
...
...

关注block->n_initvars，发现这个值只在pl_gram.y中有过赋值：那么就是在语法解析时就决定了执行时，应该对哪些值做清零。

pl_block是上层的执行语法块，注意到n_initvars是从声明区域拿到的decl_sect：

pl_block		: decl_sect K_BEGIN proc_sect exception_sect K_END opt_label
					{
						PLpgSQL_stmt_block *new;

						new = palloc0(sizeof(PLpgSQL_stmt_block));

						new->cmd_type	= PLPGSQL_STMT_BLOCK;
						new->lineno		= plpgsql_location_to_lineno(@2);
						new->stmtid		= ++plpgsql_curr_compile->nstatements;
						new->label		= $1.label;
						new->n_initvars = $1.n_initvars;
						new->initvarnos = $1.initvarnos;
						new->body		= $3;
						new->exceptions	= $4;

						check_labels($1.label, $6, @6);
						plpgsql_ns_pop();

						$$ = (PLpgSQL_stmt *)new;
					}
				;

当前语法块的声明区域：

只有带变量声明区域的规则有可能返回initvarnos，因为decl_sect的声明结构是declhdr{char *label;int n_initvars;int *initvarnos;}所以这里可以直接赋值。

decl_sect		: opt_block_label
					{
						/* done with decls, so resume identifier lookup */
						plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_NORMAL;
						$$.label	  = $1;
						$$.n_initvars = 0;
						$$.initvarnos = NULL;
					}
				| opt_block_label decl_start
					{
						plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_NORMAL;
						$$.label	  = $1;
						$$.n_initvars = 0;
						$$.initvarnos = NULL;
					}
				| opt_block_label decl_start decl_stmts
					{
						plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_NORMAL;
						$$.label	  = $1;
						/* Remember variables declared in decl_stmts */
						$$.n_initvars = plpgsql_add_initdatums(&($$.initvarnos));
					}
				;

继续看plpgsql_add_initdatums函数：总结下逻辑，就是把所有PLPGSQL_DTYPE_VAR、PLPGSQL_DTYPE_REC类型的记录下来。

也就是所有VAR、REC类型都需要初始化，清零或赋默认值。

int
plpgsql_add_initdatums(int **varnos)
{
	int			i;
	int			n = 0;
	for (i = datums_last; i < plpgsql_nDatums; i++)
	{
		switch (plpgsql_Datums[i]->dtype)
		{
			case PLPGSQL_DTYPE_VAR:
			case PLPGSQL_DTYPE_REC:
				n++;
				break;

			default:
				break;
		}
	}
	if (varnos != NULL)
	{
		if (n > 0)
		{
			*varnos = (int *) palloc(sizeof(int) * n);

			n = 0;
			for (i = datums_last; i < plpgsql_nDatums; i++)
			{
				switch (plpgsql_Datums[i]->dtype)
				{
					case PLPGSQL_DTYPE_VAR:
					case PLPGSQL_DTYPE_REC:
						(*varnos)[n++] = plpgsql_Datums[i]->dno;

					default:
						break;
				}
			}
		}
		else
			*varnos = NULL;
	}

	datums_last = plpgsql_nDatums;
	return n;
}

问题六：exec_stmt_block刚进入为什么要初始化哪些变量直接用不行吗？

结论：为了赋默认值，默认值是不在执行块中的，如果不在进入时赋值，那么就永远拿不到默认值了。

一开始我以为是因为内外层共享变量的原因：结果测试发现PG的内外层block是完全隔离的，嵌套块执行也不需要重新初始化变量。

CREATE or replace PROCEDURE tp1()
LANGUAGE plpgsql
AS $$
DECLARE
  i int;
BEGIN
  i := 1;
  raise notice 'outter i: %', i;
  DECLARE
    i int;
  BEGIN
    i := 2;
    raise notice 'inner i: %', i;
  END;
  raise notice 'outter i: %', i;
END;
$$;

call tp1();
postgres=# call tp1();
NOTICE:  outter i: 1
NOTICE:  inner i: 2
NOTICE:  outter i: 1
CALL

看第一次进入exec_stmt_block的变量就知道了，这里有两个i，完全没关系。

(gdb) p *(PLpgSQL_var*)estate->datums[0]
$6 = { dtype = PLPGSQL_DTYPE_VAR, dno = 0, refname = 0x17b22b0 "found", lineno = 0, 
       isconst = false, notnull = false, default_val = 0x0,  datatype = 0x17b2238, 
       cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0, cursor_options = 0, 
       value = 0, isnull = false, freeval = false, promise = PLPGSQL_PROMISE_NONE}
(gdb) p *(PLpgSQL_var*)estate->datums[1]
$7 = { dtype = PLPGSQL_DTYPE_VAR, dno = 1, refname = 0x17b2e08 "i", lineno = 3, 
       isconst = false, notnull = false, default_val = 0x0, datatype = 0x17b2cf8,
       cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0, cursor_options = 0, 
       value = 0, isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}
(gdb) p *(PLpgSQL_var*)estate->datums[2]
$8 = { dtype = PLPGSQL_DTYPE_VAR, dno = 2, refname = 0x176cce8 "i", lineno = 8, 
       isconst = false, notnull = false, default_val = 0x0, datatype = 0x176cbd8,
       cursor_explicit_expr = 0x0, cursor_explicit_argrow = 0, cursor_options = 0, 
       value = 0, isnull = true, freeval = false, promise = PLPGSQL_PROMISE_NONE}

问题七：内层函数执行完的变量是在哪赋给外层的？

在exec_stmt_call最后给外层赋值的：

(gdb) bt
#0  assign_simple_var (estate=0x7ffd124d0670, var=0x17b3490, newvalue=88299, isnull=false, freeable=false) at pl_exec.c:8446
#1  0x00007fde4871120e in exec_assign_value (estate=0x7ffd124d0670, target=0x17b3490, value=88299, isNull=false, valtype=23, valtypmod=-1) at pl_exec.c:5084
#2  0x00007fde487146bc in exec_move_row_from_fields (estate=0x7ffd124d0670, target=0x1783c20, newerh=0x0, values=0x7ffd124d0140, nulls=0x7ffd124d0100,  tupdesc=0x17a5740) at pl_exec.c:7178
#3  0x00007fde48713c9a in exec_move_row (estate=0x7ffd124d0670, target=0x1783c20, tup=0x17a5858, tupdesc=0x17a5740) at pl_exec.c:6791
#4  0x00007fde4870b69f in exec_stmt_call (estate=0x7ffd124d0670, stmt=0x17836c0) at pl_exec.c:2227
#5  0x00007fde4870afd0 in exec_stmts (estate=0x7ffd124d0670, stmts=0x1783628) at pl_exec.c:1995
#6  0x00007fde4870ad56 in exec_stmt_block (estate=0x7ffd124d0670, block=0x1783ae8) at pl_exec.c:1910
#7  0x00007fde4870a563 in exec_toplevel_block (estate=0x7ffd124d0670, block=0x1783ae8) at pl_exec.c:1608
#8  0x00007fde487085b8 in plpgsql_exec_function (func=0x169f070, fcinfo=0x7ffd124d0990, simple_eval_estate=0x0, simple_eval_resowner=0x0, procedure_resowner=0x169ff18, atomic=false) at pl_exec.c:611
#9  0x00007fde48722943 in plpgsql_call_handler (fcinfo=0x7ffd124d0990) at pl_handler.c:277
#10 0x00000000006ae193 in ExecuteCallStmt (stmt=0x16600d8, params=0x0, atomic=false, dest=0x1781f20) at functioncmds.c:2311
#11 0x000000000097eea4 in standard_ProcessUtility (pstmt=0x1660738, queryString=0x165f4e0 "call tp88(1,2);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1781f20, qc=0x7ffd124d1290) at utility.c:850
#12 0x000000000097e69b in ProcessUtility (pstmt=0x1660738, queryString=0x165f4e0 "call tp88(1,2);", readOnlyTree=false, context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x1781f20, qc=0x7ffd124d1290) at utility.c:527
#13 0x000000000097d297 in PortalRunUtility (portal=0x16fa040, pstmt=0x1660738, isTopLevel=true, setHoldSnapshot=true, dest=0x1781f20, qc=0x7ffd124d1290) at pquery.c:1155
#14 0x000000000097d000 in FillPortalStore (portal=0x16fa040, isTopLevel=true) at pquery.c:1028
#15 0x000000000097c972 in PortalRun (portal=0x16fa040, count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x1660828, altdest=0x1660828, qc=0x7ffd124d1460) at pquery.c:760
#16 0x000000000097663b in exec_simple_query (query_string=0x165f4e0 "call tp88(1,2);") at postgres.c:1213
#17 0x000000000097ab59 in PostgresMain (argc=1, argv=0x7ffd124d16f0, dbname=0x1688cb0 "postgres", username=0x1688c88 "mingjiegao") at postgres.c:4494
#18 0x00000000008b6d4e in BackendRun (port=0x1680a80) at postmaster.c:4530
#19 0x00000000008b66cd in BackendStartup (port=0x1680a80) at postmaster.c:4252
#20 0x00000000008b2b45 in ServerLoop () at postmaster.c:1745
#21 0x00000000008b2417 in PostmasterMain (argc=1, argv=0x16590d0) at postmaster.c:1417
#22 0x00000000007b4c93 in main (argc=1, argv=0x16590d0) at main.c:209

过程：

exec_stmt_call
  第一步：从上次执行结果中拿值记录到SPI内部。
  SPI_execute_plan_extended
    _SPI_execute_plan
      SPITupleTable *my_tuptable = NULL;
      ...
      ProcessUtility
        ExecuteCallStmt
          plpgsql_call_handler
            plpgsql_exec_function
              exec_toplevel_block
                exec_stmt_block
             
            begin_tup_output_tupdesc        // CALL完了开始构造输出tuple
              spi_dest_startup
                _SPI_current->tuptable = tuptable = (SPITupleTable *) palloc0(sizeof(SPITupleTable));
            ExecStoreHeapTuple
            tstate->dest->receiveSlot      // 把结果tuple塞到_SPI_current->tuptable里面
            
            
      my_tuptable = _SPI_current->tuptable;
      ...
      SPI_tuptable = my_tuptable;

  第二步：拿出来向变量列表中塞值。
  SPITupleTable *tuptab = SPI_tuptable;
  exec_move_row(estate, stmt->target, tuptab->vals[0], tuptab->tupdesc);

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2022-09-20，如有侵权请联系 cloudcommunity@tencent.com 删除

linux

编程算法

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

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

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

linux

编程算法

登录后参与评论

0 条评论

热度