Re: BUG #17610: Use of multiple composite types incompatible with record-typed function parameter

On Thu, 08 Sep 2022 at 18:19, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17610
> Logged by: Martin Jurča
> Email address: mjurca(at)centrum(dot)cz
> PostgreSQL version: 14.5
> Operating system: Linux, Debian 10.2.1-6, x86
> Description:
>
> The SQL code at the end of this bug report ends with the following error:
>
> 2022-09-08 10:09:02.173 GMT [174] ERROR: type of parameter 1
> (composite_type_2) does not match that when preparing the plan
> (composite_type_1)
> 2022-09-08 10:09:02.173 GMT [174] CONTEXT: PL/pgSQL function
> polymorphic_fuction(record) line 6 at EXECUTE
> 2022-09-08 10:09:02.173 GMT [174] STATEMENT: SELECT * FROM
> polymorphic_fuction(
> CAST(ROW(2) AS composite_type_2)
> );
> ERROR: 42804: type of parameter 1 (composite_type_2) does not match that
> when preparing the plan (composite_type_1)
> CONTEXT: PL/pgSQL function polymorphic_fuction(record) line 6 at EXECUTE
> LOCATION: plpgsql_param_eval_generic_ro, pl_exec.c:6648
>
> The expected output is:
>
> polymorphic_fuction
> ---------------------
> 2
> (1 row)
>

The exec_eval_using_params [1] function will handle the parameters, however,
it creates a plan for the expression only the first time [2], for the other
time, it uses the cached plan, which also caches the parameters' type
information, so it causes the problem that you mentation.

> If I understand the documentation
> (https://www.postgresql.org/docs/current/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING)
> correctly, this should work. I noticed that the record type is not listed

The documentation says:

The mutable nature of record variables presents another problem in this
connection. When fields of a record variable are used in expressions or
statements, **the data types of the fields must not change from one call
of the function to the next**, since each expression will be analyzed
using the data type that is present when the expression is first reached.

EXECUTE can be used to get around this problem when necessary.

However, I'am not sure how to use EXECUTE to avoid this problem.

[1] https://github.com/postgres/postgres/blob/ccd10a9bfa54c1aad3561232bf24222f1b455e1c/src/pl/plpgsql/src/pl_exec.c#L8573
[2] https://github.com/postgres/postgres/blob/ccd10a9bfa54c1aad3561232bf24222f1b455e1c/src/pl/plpgsql/src/pl_exec.c#L5671

--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.