Обсуждение: The follwing error sometimes happened while updating partitioned table using inheritance; ERROR: attribute xxx of type record has wrong type

Hi.

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
anda.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
wherewk.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.

* When enable_hashjoin was off, it did not happen.

[The following is DDL and DML for reproducing this issue]
=========================
-- CREATE tables with inheritance.
create table a (a char(10), b int, c int);
create table a_1() inherits (a);
create table a_2() inherits (a);
create table a_3() inherits (a);

-- CREATE partitioning trigger.
create or replace function a_func() returns trigger as $$
begin
  if    (new.a >= '2021') then insert into a_1 values(new.*);
  elsif (new.a >= '2011' and new.a < '2021') then insert into a_2 values(new.*);
  else  insert into a_3 values (new.*);
  end if;
  return null;
end;
$$ language plpgsql;
create trigger a_trigger before insert on a for each row execute procedure a_func();

-- INSERT initial data.
insert into a select i::char(10), i, i * 2 from generate_series(200, 20300) i;

-- CREATE additional data table.
create table b (x int, y int);
insert into b select i, i * 3 from generate_series(2000, 2030) i;

-- **ANALYZE**
ANALYZE;

-- SQL (ERROR happened)
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';
=========================

[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)
=========================

<When this issue did NOT happen>
=========================
postgres(8439)@[local]:5432=# set enable_hashjoin to off;
SET
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.00..387.12 rows=4 width=96)
   Update on a
   Update on a_1
   Update on a_2
   Update on a_3
   ->  Nested Loop  (cost=0.00..1.72 rows=1 width=96)
         Join Filter: (a.b = wk.x)
         ->  Seq Scan on a  (cost=0.00..0.00 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
         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)
   ->  Nested Loop  (cost=0.00..4.10 rows=1 width=96)
         Join Filter: (a_2.b = wk.x)
         ->  Seq Scan on a_2  (cost=0.00..2.39 rows=1 width=10)
               Filter: (a = '2017'::bpchar)
         ->  Seq Scan on b wk  (cost=0.00..1.31 rows=31 width=14)
   ->  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)
(27 rows)
=========================

[Log of debug_print_plan when this issue happened]
Attached.

Regards,
Takeshi Ideriha

Hi Ideriha-san,

On Thu, Jan 27, 2022 at 6:07 PM ideriha.takeshi@fujitsu.com
<ideriha.takeshi@fujitsu.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.band 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
wkwhere 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

Hi Amit,

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

Thank you for the quick response and detailed explanation!
I'm not familiar with source code around planner, so I really appreciate that.

> 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.

If you work on this, it'd be wonderful because this issue actually occurred in one of my client's system.
(Of course, SQL I wrote is not actual one.)

Best regards,
Takeshi Ideriha

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.

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?
Would you hit upon any other work arounds?

Regards,
Takeshi Ideriha

Ideriha-san,

On Mon, Jan 31, 2022 at 6:04 PM ideriha.takeshi@fujitsu.com
<ideriha.takeshi@fujitsu.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

Hi, thank you for your kind replay.

>> 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';

Thank you, it seems work in my test cases.
I misunderstood that SubLink (and following SubPlan) is always made
from update on a partitioned table,
but it actually made from subquery in update target list.

Regards.
Takeshi Ideriha