Horton
Menu

Re: Attribute of type record has wrong type error with MERGE ... WHEN NOT MATCHED BY SOURCE THEN DELETE

Tender Wang <tndrwang@gmail.com>

View thread
From: Tender Wang <tndrwang@gmail.com>
To: Duncan Sands <duncan.sands@deepbluecap.com>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2025-03-10T13:46:36Z
Lists: pgsql-bugs 
Duncan Sands <duncan.sands@deepbluecap.com> 于2025年3月10日周一 18:43写道：

> Postgresql version 17.4 (Ubuntu 17.4-1.pgdg24.10+2) on x86_64-pc-linux-gnu
>
> To reproduce, execute the statements in the attached file cr.sql.  I get:
>
> duncan=> \i cr.sql
> CREATE TABLE
> CREATE TABLE
> CREATE VIEW
> CREATE TABLE
> COPY 1
> COPY 2
> COPY 1
> psql:cr.sql:42: ERROR:  attribute 2 of type record has wrong type
> DETAIL:  Table has type _country_or_region, but query expects record.
>
> I attribute it to the "WHEN NOT MATCHED BY SOURCE THEN DELETE" part of the
> MERGE
> as it doesn't happen if that part is left off.


When the query has NOT MATCHED BY SOURCE, commit d7d297f84 add "src IS NOT
NULL" join condition.
In this case, the src is view(e.g. subquery), so in makeWholeRowVar(), it
will call below code:
result = makeVar(varno,
                      InvalidAttrNumber,
                      RECORDOID,
                      -1,
                      InvalidOid,
                      varlevelsup);

the vartype is RECORDOID, but te reltype of src is not RECORDOID, so
$SUBJECT error reports.

I add the below codes to makeWholeRowVar() default branch:

if (rte->relkind == RELKIND_VIEW)
        toid = get_rel_type_id(rte->relid);
else
        toid = RECORDOID;

It can work.


-- 
Thanks,
Tender Wang

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Repair commits 317aba70e et al for -DWRITE_READ_PARSE_PLAN_TREES.

  2. Build whole-row Vars the same way during parsing and planning.

  3. Preserve RangeTblEntry.relid when expanding a view RTE.

  4. Fix incorrect non-strict join recheck in MERGE WHEN NOT MATCHED BY SOURCE.