Re: The follwing error sometimes happened while updating partitioned table using inheritance; ERROR: attribute xxx of type record has wrong type

Hi Ideriha-san,

On Thu, Jan 27, 2022 at 6:07 PM ideriha(dot)takeshi(at)fujitsu(dot)com
<ideriha(dot)takeshi(at)fujitsu(dot)com> wrote:
> I defined partition using inheritance and trigger,
> and tried to UPDATE it but sometimes failed with following error.
> This error messages was the result of PostgreSQL 13.5.
>
> =========================
> postgres(8439)@[local]:5432=# update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where wk.x = a.b and a.a = '2017';
> 2022-01-27 13:57:38.307 JST [8439] ERROR: attribute 1 of type record has wrong type
> 2022-01-27 13:57:38.307 JST [8439] DETAIL: Table has type tid, but query expects integer.
> 2022-01-27 13:57:38.307 JST [8439] STATEMENT: update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where wk.x = a.b and a.a = '2017';
> ERROR: 42804: attribute 1 of type record has wrong type
> DETAIL: Table has type tid, but query expects integer.
> LOCATION: CheckVarSlotCompatibility, execExprInterp.c:1909
> =========================
>
> I confirmed this issue happened in the following version (the parameters haven't changed since initdb):
> 9.5.25, 9.6.24, 10.19, 11.14, 12.9, and 13.5. This issue did not happen with following reproduce SQL in 14.1.
> # I understand that 9.5.x and 9.6.x are no longer supported.

Thanks for the report.

This looks to me like a bug of inheritance_planner() that is used for
planning inherited UPDATEs till v13.

> * When enable_hashjoin was off, it did not happen.
>
> [The following is DDL and DML for reproducing this issue]
> ...
> [Output of EXPLAIN]
> <When this issue happened>
> =========================
> postgres(8439)@[local]:5432=# explain update a set ( a, b, c ) = (select wk.x::char(10), wk.x, wk.y) from b wk where wk.x = a.b and a.a = '2017';
> QUERY PLAN
> ----------------------------------------------------------------------
> Update on a (cost=0.01..386.62 rows=4 width=96)
> Update on a
> Update on a_1
> Update on a_2
> Update on a_3
> -> Hash Join (cost=0.01..1.47 rows=1 width=96)
> Hash Cond: (wk.x = a.b)
> -> Seq Scan on b wk (cost=0.00..1.31 rows=31 width=14)
> -> Hash (cost=0.00..0.00 rows=1 width=10)
> -> Seq Scan on a (cost=0.00..0.00 rows=1 width=10)
> Filter: (a = '2017'::bpchar)
> SubPlan 1 (returns $2,$3,$4)
> -> Result (cost=0.00..0.02 rows=1 width=52)
> -> Nested Loop (cost=0.00..169.55 rows=1 width=96)
> Join Filter: (a_1.b = wk.x)
> -> Seq Scan on a_1 (cost=0.00..167.84 rows=1 width=10)
> Filter: (a = '2017'::bpchar)
> -> Seq Scan on b wk (cost=0.00..1.31 rows=31 width=14)
> -> Hash Join (cost=2.40..3.85 rows=1 width=96)
> Hash Cond: (wk.x = a_2.b)
> -> Seq Scan on b wk (cost=0.00..1.31 rows=31 width=14)
> -> Hash (cost=2.39..2.39 rows=1 width=10)
> -> Seq Scan on a_2 (cost=0.00..2.39 rows=1 width=10)
> Filter: (a = '2017'::bpchar)
> -> Nested Loop (cost=0.00..211.75 rows=1 width=96)
> Join Filter: (a_3.b = wk.x)
> -> Seq Scan on a_3 (cost=0.00..210.04 rows=1 width=10)
> Filter: (a = '2017'::bpchar)
> -> Seq Scan on b wk (cost=0.00..1.31 rows=31 width=14)
> (29 rows)
> =========================

The problem seems to be that the SubPlan corresponding to the SubLink
in the query's target list appears only once (SubPlan 1 seen under the
plan for the 1st child result relation), whereas the correct thing
would have been there to be one for each child result relation and
correspondingly separate sets of param IDs for each child relation.
In the absence of a separate SubPlan and set of param IDs for each
child result relation, the setParam and parParam sets of parameters
end up being shared across all child result relations, which doesn't
bode well for how those parameters get evaluated during execution.
Specifically, SubPlanState of a given child relation that is assigned
by ExecInitSubPlan() to a parameter's ParamExecData.execPlan gets
overwritten with a SubPlanState of later child relations: with this
code:

if (subplan->setParam != NIL && subplan->subLinkType != CTE_SUBLINK)
{
ListCell *lst;

foreach(lst, subplan->setParam)
{
int paramid = lfirst_int(lst);
ParamExecData *prm = &(estate->es_param_exec_vals[paramid]);

prm->execPlan = sstate;
}
}

In the above code block, set of values of "paramid" for setParam
params is the same no matter which child relation's SubPlanState is
being initialized. So, prm->execPlan that would have been set when
initializing the SubPlanState for the 1st child relation ('a') would
get overwritten when initializing the SubPlanState for the 2nd and
subsequent child relations. IOW, when it's time to evaluate the
parameters for the 1st child relation, what a given parameter would be
referring to is the result of evaluating the SubPlan belonging to the
last child relation, which may not always work.

In this particular case, what causes the error is that the 3rd child
relation's SubPlan parameter's reference to the source (join) plan's
output ends up pointing to the wrong component relation of the join,
because the actual SubPlanState that is used belongs to the 4th child
relation whose parameters refer to the inner relation of the join (an
INNER_VAR). Because the ordering of joining a and b differs in their
join plans, INNER_VAR doesn't refer to the same relation in the two
joins, hence the type mismatch error.

To fix this, I think inheritance_planner() will need to translate the
original parsetree such that each child gets assigned its own copies
of any SubPlans and corresponding sets of parameters. That is, the
following would need to do more than it does now:

/*
* Generate modified query with this rel as target. We first apply
* adjust_appendrel_attrs, which copies the Query and changes
* references to the parent RTE to refer to the current child RTE,
* then fool around with subquery RTEs.
*/
subroot->parse = (Query *)
adjust_appendrel_attrs(subroot,
(Node *) parent_parse,
1, &appinfo);

adjust_appendrel_attrs() simply copies any Params it finds in the
original tree as-is by way of expression_tree_mutator(), though
perhaps it should add new ones that refer to the child plan. I'll
take a shot at that.

--
Amit Langote
EDB: http://www.enterprisedb.com