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

Ideriha-san,

On Mon, Jan 31, 2022 at 6:04 PM ideriha(dot)takeshi(at)fujitsu(dot)com
<ideriha(dot)takeshi(at)fujitsu(dot)com> wrote:
>
> Hi Amit,
>
> >>* When enable_hashjoin was off, it did not happen.
>
> Let me correct my information to make sure.
> As you may already know, I found "set enable_hashjoin = off" doesn't help in some cases
> including my client's case.
> It makes sense since in my understanding postgres refers to wrong column information
> when executing of join of each child table due to mistakenly shared SubPlan as you explained.

Yeah, even if it doesn't manifest itself in all situations, the bug
exists nonetheless.

> I'd be happy if there are any work around.
> Not using partitioned table with inheritance but using declarative partition could become a work around?

That wouldn't help because partitioning uses the same underlying code
as traditional inheritance, that is, the function
inheritance_planner() which is where I suspect the bug lies. That
said, thg bug should no longer exist as of v14, because we got rid of
inheritance_planner() for both partitioning and traditional
inheritance in v14.

> Would you hit upon any other work arounds?

Have you considered not using the sub-select expression in the UPDATE
targetlist? That is, doesn't the following query, which doesn't use
the subquery expression, do the same job as the original query:

update a set a = wk.x::char(10), b = wk.x, c = wk.y from b wk where
wk.x = a.b and a.a = '2017';

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