[PATCH] pg_input_is_valid() reuses the first type name inside a PL/pgSQL loop
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
On Nov 10, 2026, at 16:11, Xnerv Wang <xnervwang@gmail.com> 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/
On Nov 11, 2026, at 16:04, Chao Li <li.evan.chao@gmail.com> wrote:
On Nov 10, 2026, at 16:11, Xnerv Wang <xnervwang@gmail.com> 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.
I just had another thought. Comparing type names might not completely resolve the caching problem.
For example, if the type is a domain, a subsequent DDL could drop and recreate the domain with a different underlying type. When execution reaches the same pg_input_is_valid() call again, the type name is unchanged, but it would resolve to a different type OID, leaving the cache stale.
I don't think this is a practical use case, but it seems theoretically possible, assuming the cached FmgrInfo survives the DDL operation. I wouldn't want to block this patch over this concern. I'm just mentioning it for your consideration.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Wed, Nov 11, 2026 at 12:26 AM Chao Li <li.evan.chao@gmail.com> wrote:
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.
I did that in the attached v2: I check whether the second FuncExpr
argument is a Const, since get_fn_expr_arg_stable() returns true for
both Const and external Param. A Const type name is parsed on the first
call and reused, as on master, without storing or comparing the name.
An external Param, as used for PL/pgSQL variables, retains the last
parsed name and is parsed again when it changes.
I timed 5 million calls with the constant 'int4': 704 ms with v2,
717 ms on unpatched master (-O2, no assertions, median of 11 runs).
The test is the same as in v1; it fails without the fix, and
check-world passes with v2.
For example, if the type is a domain, a subsequent DDL could drop and
recreate the domain with a different underlying type. When execution
reaches the same pg_input_is_valid() call again, the type name is
unchanged, but it would resolve to a different type OID, leaving the
cache stale.
I ran this on unpatched master:
CREATE DOMAIN dd AS int;
DO $$
BEGIN
FOR i IN 1..2 LOOP
IF i = 2 THEN DROP DOMAIN dd; CREATE DOMAIN dd AS text; END IF;
RAISE NOTICE 'const name, iteration %: %', i,
pg_input_is_valid('abc', 'dd');
END LOOP;
END $$;
NOTICE: const name, iteration 1: f
NOTICE: const name, iteration 2: f
I get the same output with v2. With the type name in a PL/pgSQL
variable instead of the constant 'dd', I also get f on the second
iteration with v2; it should return t because 'abc' is valid text.
Thanks,
Xnerv Wang
Amazon Web Services
On Wed, Nov 11, 2026 at 12:27 AM Chao Li <li.evan.chao@gmail.com> wrote:
Show quoted text
On Nov 11, 2026, at 16:04, Chao Li <li.evan.chao@gmail.com> wrote:
On Nov 10, 2026, at 16:11, Xnerv Wang <xnervwang@gmail.com> 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.I just had another thought. Comparing type names might not completely
resolve the caching problem.For example, if the type is a domain, a subsequent DDL could drop and
recreate the domain with a different underlying type. When execution
reaches the same pg_input_is_valid() call again, the type name is
unchanged, but it would resolve to a different type OID, leaving the cache
stale.I don't think this is a practical use case, but it seems theoretically
possible, assuming the cached FmgrInfo survives the DDL operation. I
wouldn't want to block this patch over this concern. I'm just mentioning it
for your consideration.Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On Nov 12, 2026, at 03:58, Xnerv Wang <xnervwang@gmail.com> wrote:
On Wed, Nov 11, 2026 at 12:26 AM Chao Li <li.evan.chao@gmail.com> wrote:
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.I did that in the attached v2: I check whether the second FuncExpr
argument is a Const, since get_fn_expr_arg_stable() returns true for
both Const and external Param. A Const type name is parsed on the first
call and reused, as on master, without storing or comparing the name.
An external Param, as used for PL/pgSQL variables, retains the last
parsed name and is parsed again when it changes.I timed 5 million calls with the constant 'int4': 704 ms with v2,
717 ms on unpatched master (-O2, no assertions, median of 11 runs).
The test is the same as in v1; it fails without the fix, and
check-world passes with v2.For example, if the type is a domain, a subsequent DDL could drop and
recreate the domain with a different underlying type. When execution
reaches the same pg_input_is_valid() call again, the type name is
unchanged, but it would resolve to a different type OID, leaving the
cache stale.I ran this on unpatched master:
CREATE DOMAIN dd AS int;
DO $$
BEGIN
FOR i IN 1..2 LOOP
IF i = 2 THEN DROP DOMAIN dd; CREATE DOMAIN dd AS text; END IF;
RAISE NOTICE 'const name, iteration %: %', i, pg_input_is_valid('abc', 'dd');
END LOOP;
END $$;
NOTICE: const name, iteration 1: f
NOTICE: const name, iteration 2: fI get the same output with v2. With the type name in a PL/pgSQL
variable instead of the constant 'dd', I also get f on the second
iteration with v2; it should return t because 'abc' is valid text.
So, you've confirmed the problem. It's not limited to domains. Dropping and recreating a user-defined type under the same name can cause the same issue. Even a change to search_path could cause the same type name to resolve to a different type OID.
I'm afraid we don't have a reliable way to determine if the resolved type has changed in pg_input_is_valid_common() without either resolving the type OID. This means the patch only partially addresses the caching problem. Given that, I'd wait and see if the committers have any concerns about this partial fix.
It feels to me that, without some refactoring, the current caching mechanism cannot reliably handle all these cases.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
On 11/12/26 02:37, Chao Li wrote:
I'm afraid we don't have a reliable way to determine if the resolved type has changed in pg_input_is_valid_common() without either resolving the type OID. This means the patch only partially addresses the caching problem. Given that, I'd wait and see if the committers have any concerns about this partial fix.
It feels to me that, without some refactoring, the current caching mechanism cannot reliably handle all these cases.
I am not a fan of the caching mechanism and I wonder why the
pg_input_is_valid() should even try to optimize this in the first place.
As an end user of PostgreSQL I would not expect any caching of the
lookup from type name to oid. If this function had been added now I
personally would have preferred the signature to be:
pg_input_is_valid(text, regtype)
Do we have similar optimizations for other functions which take a type name?
--
Andreas Karlsson
Percona
On Fri, Nov 13, 2026 at 1:53 AM Andreas Karlsson <andreas@proxel.se> wrote:
I am not a fan of the caching mechanism and I wonder why the
pg_input_is_valid() should even try to optimize this in the first place.
As an end user of PostgreSQL I would not expect any caching of the
lookup from type name to oid.
I measured it. I used x86_64 Linux, gcc 13.3, -O2, no assertions, and
master at bc192c898b2. The results are medians of 11 runs. I rotated the
build order each run and pinned each server to its own CPU.
The SQL queries were
SELECT count(*) FROM generate_series(1, 5000000) g
WHERE pg_input_is_valid(g::text, '<type name>')
with the type names in the table. I also ran a PL/pgSQL loop with
2 million calls and the type name in a variable. The per-call numbers
for all queries subtract the time of the same loop without
pg_input_is_valid().
The "no name cache" build resolves the type name on every call and
keeps only the input function of the resolved type across calls.
The "cache + check" build keeps v2's cache but checks two things before
reusing a cached type: whether search_path still matches the one used
to resolve the name, using a SearchPathMatcher and
SearchPathMatchesCurrentEnvironment() as plancache does for cached
plans; and whether any pg_type entry has changed, by comparing the
cache's remembered counter value with a counter bumped by a syscache
invalidation callback on TYPENAMENSP. If either check fails, it
resolves the name again. It also registers one more syscache callback.
Compared with master:
no name cache cache + check
per call query per call query
'int4' +277 ns 2.9x +3.5 ns +2.4%
'varchar(10)' +509 ns 4.4x +4.2 ns +2.7%
'numeric(7,4)' +649 ns 4.9x +4.3 ns +2.6%
domain in public +302 ns 3.0x +3.5 ns +2.2%
PL/pgSQL variable +270 ns 3.8x +7.7 ns +8.4%
On master, pg_input_is_valid() costs 23 to 62 ns per call in these
queries.
With "cache + check", the function gives the same answers as if
nothing were cached when a type is dropped and recreated under the
same name, or search_path changes between calls, including between
two FETCHes from the same cursor. If a type is dropped without being
recreated, it now reports "type ... does not exist" instead of
"cache lookup failed for type".
Keeping only the parse tree and looking up the name on every call
costs 57 to 91 ns more per call than master, making the queries
1.4 to 1.6 times as long.
If this function had been added now I
personally would have preferred the signature to be:pg_input_is_valid(text, regtype)
regtypein() parses the typmod but returns only the OID, so a regtype
argument loses the typmod. The documentation example
pg_input_is_valid('1234.567', 'numeric(7,4)') returns false, but with
a regtype argument it would lose the (7,4) and return true.
Do we have similar optimizations for other functions which take a type
name?
No. Other functions that take a type name as text, such as
to_regtype() and regtypein(), parse it on every call. I did not find
another function that caches it.
Thanks,
Xnerv Wang
Amazon Web Services