When and how many times does ExecSetParamPlan executes?

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

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

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