> On Nov 10, 2026, at 16:11, Xnerv Wang <[email protected]> wrote:
>
> Hi hackers,
>
> One of our tests checks exported values with pg_input_is_valid() in a
> PL/pgSQL loop and it returned wrong results. I can reproduce the
> problem on master and 19rc1:
>
> DO $$
> DECLARE r record;
> BEGIN
> FOR r IN SELECT * FROM (VALUES ('42', 'integer'), ('hello', 'text'),
> ('100000', 'smallint')) AS v(val, typ)
> LOOP
> RAISE NOTICE '% as %: % %', r.val, r.typ,
> pg_input_is_valid(r.val, r.typ),
> (pg_input_error_info(r.val, r.typ)).message;
> END LOOP;
> END $$;
> NOTICE: 42 as integer: t <NULL>
> NOTICE: hello as text: f invalid input syntax for type integer: "hello"
> NOTICE: 100000 as smallint: t <NULL>
>
> The second and third calls should return t and f, but both validate
> against integer, the type from the first call. pg_input_error_info()
> has the same problem. The typmod is reused too: varchar(5) followed
> by varchar(10) still checks against varchar(5).
>
> In misc.c, pg_input_is_valid_common() parses the type name only on the
> first call when get_fn_expr_arg_stable() says the argument is stable.
> That function returns true for a PARAM_EXTERN Param, which is how
> PL/pgSQL passes variables. But a PL/pgSQL simple expression keeps its
> ExprState, and so fn_extra, across evaluations within a transaction
> while the variable changes. This logic came in with commit 1939d26282b,
> in PostgreSQL 16.
>
> For a stable argument, the attached patch keeps the last type name
> string it parsed and parses again when a later call passes a different
> one. The new test in plpgsql_simple.sql (a similar loop plus varchar
> typmod cases) fails without the fix, and check-world passes with it. A
> constant type name now costs one extra name comparison per call: 5
> million calls with 'int4' took 722 ms instead of 691 ms (-O2, no
> assertions, median of 11 runs).
>
> Dropping typname_constant and always comparing names would be simpler
> and would also fix this bug. A type name from a column would then be
> parsed only when its text changes, rather than on every call as today,
> so it would behave like a constant name does now. If the text stays
> the same, a search_path change between two FETCHes from a cursor would
> go unnoticed. I kept the flag so that non-constant names behave as
> before, and can send the simpler version if others prefer it.
>
> Thanks,
> Xnerv Wang
> Amazon Web Services
> <v1-0001-Fix-pg_input_is_valid-reusing-a-stale-type-in-PL-.patch>
I can reproduce the issue with a slightly simpler example:
```
evantest=# do $$
evantest$# declare
evantest$# typ text;
evantest$# result boolean;
evantest$# begin
evantest$# for i in 1..2 loop
evantest$# if i = 1 then
evantest$# typ := 'integer';
evantest$# else
evantest$# typ := 'text';
evantest$# end if;
evantest$# result := pg_input_is_valid('abc', typ);
evantest$# raise notice 'type=%, valid=%', typ, result;
evantest$# end loop;
evantest$# end $$;
NOTICE: type=integer, valid=f
NOTICE: type=text, valid=f
```
After some debugging, I think the root cause is that get_fn_expr_arg_stable()
returns true for both Const and PARAM_EXTERN. While an external parameter is
stable within a single query execution, its value can change between
evaluations of a cached PL/pgSQL expression.
n pg_input_is_valid_common(), we have no info to distinguish whether the
function is being called from PL/pgSQL. As you mentioned, the proposed fix
introduces some overhead for genuine Const arguments, since their values cannot
change.
Since we have already added two fields to ValidIOData, I'm wondering if we
could add another boolean field, say typname_stable, to store the result of
get_fn_expr_arg_stable(), and use the existing typname_constant field to
indicate whether the argument is a genuine Const. With this distinction, we
could retain the original caching behavior for genuine constants, without
comparing the type name on subsequent calls. We wouldn't even need to store the
type name in ValidIOData for Const arguments, which would help mitigate the
overhead.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
