Thread: When and how many times does ExecSetParamPlan executes?
Hello all, I was going through the Expression Evaluator and was trying to understand how the expressions are formed and evaluated. I was informed on the IRC channel that the PARAM nodes are quite important and many well written client applications use PARAMs for sending query to the backend. I found while looking at the ExecEvalParam function that a call is made to the ExecSetParamPlan for the first time when the PARAM is caught in the ExprState tree. I am confused about how many times the call is made to the ExecSetParamPlan funtion. Since the executor runs the expression evaluator for each tuple (trying to find qual satisfaction) while the expression tree is created only once, the possibility (from my viewpoint) is that the ExecSetParamPlan might be called once only; which would be when the first PARAM is encountered during the query execution. OR, it might get called individually for each tuple (when the PARAM node is caught in the tree). So I am confused about which case is it? Does it (ExecSetParamPlan) get called on per-tuple basis or first encounter in the qual/expression evaluation basis? Kindly help. Regards, Vaibhav
Vaibhav Kaushal <vaibhavkaushal123@gmail.com> writes:
> Hello all,
> I was going through the Expression Evaluator and was trying to
> understand how the expressions are formed and evaluated. I was informed
> on the IRC channel that the PARAM nodes are quite important and many
> well written client applications use PARAMs for sending query to the
> backend. I found while looking at the ExecEvalParam function that a call
> is made to the ExecSetParamPlan for the first time when the PARAM is
> caught in the ExprState tree.
> I am confused about how many times the call is made to the
> ExecSetParamPlan funtion.
Indeed ... ExecSetParamPlan has nothing at all to do with Params
representing values coming from a client (PARAM_EXTERN parameters).
It's used in connection with PARAM_EXEC parameters that represent
the outputs of InitPlan subplans (that is, execute-only-once subplans).
regards, tom lane
Thanks for the reply Mr. Tom. So, I think that the function ExecSetParamPlan (as the code suggests too) is called _once_ in any plan/expression and that should be mostly for a sub-select query. Kindly correct me if I am wrong. Since I am not able to understand this usecase completely, a sample query which is capable of calling this function (ExecSetParamPlan) could show some light. It would be really kind of you / anyone to show me a query executable through psql which can actually call ExecSetParamPlan and involves the use of a on-disk relation. The reason I am asking for a query which I could run on psql is that I am using gdb to debug (and notice the steps taken by) PG. Regards, Vaibhav On Fri, 2011-03-25 at 14:37 -0400, Tom Lane wrote: > Vaibhav Kaushal <vaibhavkaushal123@gmail.com> writes: > > Hello all, > > I was going through the Expression Evaluator and was trying to > > understand how the expressions are formed and evaluated. I was informed > > on the IRC channel that the PARAM nodes are quite important and many > > well written client applications use PARAMs for sending query to the > > backend. I found while looking at the ExecEvalParam function that a call > > is made to the ExecSetParamPlan for the first time when the PARAM is > > caught in the ExprState tree. > > > I am confused about how many times the call is made to the > > ExecSetParamPlan funtion. > > Indeed ... ExecSetParamPlan has nothing at all to do with Params > representing values coming from a client (PARAM_EXTERN parameters). > It's used in connection with PARAM_EXEC parameters that represent > the outputs of InitPlan subplans (that is, execute-only-once subplans). > > regards, tom lane
Vaibhav Kaushal <vaibhavkaushal123@gmail.com> writes:
> So, I think that the function ExecSetParamPlan (as the code suggests
> too) is called _once_ in any plan/expression and that should be mostly
> for a sub-select query.
> Kindly correct me if I am wrong. Since I am not able to understand this
> usecase completely, a sample query which is capable of calling this
> function (ExecSetParamPlan) could show some light. It would be really
> kind of you / anyone to show me a query executable through psql which
> can actually call ExecSetParamPlan and involves the use of a on-disk
> relation.
regression=# explain verbose select *, (select sum(f1) from int4_tbl) ss from int8_tbl;
QUERYPLAN
-----------------------------------------------------------------------------Seq Scan on public.int8_tbl
(cost=1.07..2.12rows=5 width=16) Output: int8_tbl.q1, int8_tbl.q2, $0 InitPlan 1 (returns $0) -> Aggregate
(cost=1.06..1.07rows=1 width=4) Output: sum(int4_tbl.f1) -> Seq Scan on public.int4_tbl
(cost=0.00..1.05rows=5 width=4) Output: int4_tbl.f1
(7 rows)
$0 here represents the PARAM_EXEC Param.
regards, tom lane
Thanks a lot Mr. Tom. I understand it better now. I see that the function ExecSetParamPlan is indeed called only once when executing a query which would have a paramkind = PARAM_EXEC. The query helped me see the run in debugger, making things clearer (a lot clearer in fact, especially reading your last reply again and again). Thank you again. Regards, Vaibhav On Fri, 2011-03-25 at 15:59 -0400, Tom Lane wrote: > Vaibhav Kaushal <vaibhavkaushal123@gmail.com> writes: > > So, I think that the function ExecSetParamPlan (as the code suggests > > too) is called _once_ in any plan/expression and that should be mostly > > for a sub-select query. > > > Kindly correct me if I am wrong. Since I am not able to understand this > > usecase completely, a sample query which is capable of calling this > > function (ExecSetParamPlan) could show some light. It would be really > > kind of you / anyone to show me a query executable through psql which > > can actually call ExecSetParamPlan and involves the use of a on-disk > > relation. > > regression=# explain verbose select *, (select sum(f1) from int4_tbl) ss from int8_tbl; > QUERY PLAN > ----------------------------------------------------------------------------- > Seq Scan on public.int8_tbl (cost=1.07..2.12 rows=5 width=16) > Output: int8_tbl.q1, int8_tbl.q2, $0 > InitPlan 1 (returns $0) > -> Aggregate (cost=1.06..1.07 rows=1 width=4) > Output: sum(int4_tbl.f1) > -> Seq Scan on public.int4_tbl (cost=0.00..1.05 rows=5 width=4) > Output: int4_tbl.f1 > (7 rows) > > $0 here represents the PARAM_EXEC Param. > > regards, tom lane