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
From a8f5ce5286c303259b6dab3d31da0ef399207692 Mon Sep 17 00:00:00 2001
From: Xnerv Wang <[email protected]>
Date: Fri, 6 Nov 2026 08:16:07 +0000
Subject: [PATCH v1] Fix pg_input_is_valid() reusing a stale type in PL/pgSQL
pg_input_is_valid_common() parses the type name only once if
get_fn_expr_arg_stable() says the argument is stable, but that function
also returns true for an external Param. PL/pgSQL passes variables that
way, and a simple expression keeps its ExprState, and hence fn_extra,
across evaluations within a transaction. So a loop calling
pg_input_is_valid(val, typ) kept using the type from the first iteration.
pg_input_error_info() has the same problem.
Fix by remembering the type name last parsed and parsing again when it
changes. Arguments not known to be stable are still parsed on each call.
Oversight in commit 1939d26282b.
---
src/backend/utils/adt/misc.c | 36 +++++++++++++++----
.../plpgsql/src/expected/plpgsql_simple.out | 20 +++++++++++
src/pl/plpgsql/src/sql/plpgsql_simple.sql | 15 ++++++++
3 files changed, 64 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
index c033e68ba15..1e2b24b29a9 100644
--- a/src/backend/utils/adt/misc.c
+++ b/src/backend/utils/adt/misc.c
@@ -58,6 +58,8 @@ typedef struct ValidIOData
Oid typoid;
int32 typmod;
bool typname_constant;
+ char *typname; /* type name last parsed, if typname_constant */
+ int typname_len;
Oid typiofunc;
Oid typioparam;
FmgrInfo inputproc;
@@ -751,29 +753,41 @@ pg_input_is_valid_common(FunctionCallInfo fcinfo,
if (my_extra == NULL)
{
fcinfo->flinfo->fn_extra =
- MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
- sizeof(ValidIOData));
+ MemoryContextAllocZero(fcinfo->flinfo->fn_mcxt,
+ sizeof(ValidIOData));
my_extra = (ValidIOData *) fcinfo->flinfo->fn_extra;
- my_extra->typoid = InvalidOid;
/* Detect whether typname argument is constant. */
my_extra->typname_constant = get_fn_expr_arg_stable(fcinfo->flinfo, 1);
}
/*
- * If the typname argument is constant, we only need to parse it the first
- * time through.
+ * Parse the type name unless it is stable and unchanged. An external
+ * Param counts as stable, but plpgsql can supply a new value for it each
+ * time a simple expression is evaluated.
*/
- if (my_extra->typoid == InvalidOid || !my_extra->typname_constant)
+ if (!my_extra->typname_constant || my_extra->typname == NULL ||
+ my_extra->typname_len != VARSIZE_ANY_EXHDR(typname) ||
+ memcmp(my_extra->typname, VARDATA_ANY(typname),
+ my_extra->typname_len) != 0)
{
char *typnamestr = text_to_cstring(typname);
Oid typoid;
+ int32 typmod;
+
+ /* Free the old name now, so that an error below forces a re-parse */
+ if (my_extra->typname)
+ {
+ pfree(my_extra->typname);
+ my_extra->typname = NULL;
+ }
/* Parse type-name argument to obtain type OID and encoded typmod. */
- (void) parseTypeString(typnamestr, &typoid, &my_extra->typmod, NULL);
+ (void) parseTypeString(typnamestr, &typoid, &typmod, NULL);
/* Update type-specific info if typoid changed. */
if (my_extra->typoid != typoid)
{
+ my_extra->typoid = InvalidOid;
getTypeInputInfo(typoid,
&my_extra->typiofunc,
&my_extra->typioparam);
@@ -781,6 +795,14 @@ pg_input_is_valid_common(FunctionCallInfo fcinfo,
fcinfo->flinfo->fn_mcxt);
my_extra->typoid = typoid;
}
+ my_extra->typmod = typmod;
+
+ if (my_extra->typname_constant)
+ {
+ my_extra->typname = MemoryContextStrdup(fcinfo->flinfo->fn_mcxt,
+ typnamestr);
+ my_extra->typname_len = strlen(typnamestr);
+ }
}
/* Now we can try to perform the conversion. */
diff --git a/src/pl/plpgsql/src/expected/plpgsql_simple.out b/src/pl/plpgsql/src/expected/plpgsql_simple.out
index ccf15ea2200..4de9aec2300 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_simple.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_simple.out
@@ -148,3 +148,23 @@ end $$;
ERROR: bigint out of range
CONTEXT: SQL statement "select x*x*x"
PL/pgSQL function inline_code_block line 5 at SQL statement
+-- Check case where the type name passed to pg_input_is_valid() and
+-- pg_input_error_info() changes between evaluations
+do $$
+declare r record;
+begin
+ for r in select * from (values ('42', 'integer'), ('hello', 'text'),
+ ('100000', 'smallint'),
+ ('abcdefg', 'varchar(5)'),
+ ('abcdefg', 'varchar(10)'),
+ ('abcdefghijk', 'varchar(10)')) 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: t <NULL>
+NOTICE: 100000 as smallint: f value "100000" is out of range for type smallint
+NOTICE: abcdefg as varchar(5): f value too long for type character varying(5)
+NOTICE: abcdefg as varchar(10): t <NULL>
+NOTICE: abcdefghijk as varchar(10): f value too long for type character varying(10)
diff --git a/src/pl/plpgsql/src/sql/plpgsql_simple.sql b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
index d64e791800b..3dcbf722619 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_simple.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_simple.sql
@@ -131,3 +131,18 @@ begin
-- overflow during expression evaluation step does get an extra context line
select x*x*x into y;
end $$;
+
+-- Check case where the type name passed to pg_input_is_valid() and
+-- pg_input_error_info() changes between evaluations
+do $$
+declare r record;
+begin
+ for r in select * from (values ('42', 'integer'), ('hello', 'text'),
+ ('100000', 'smallint'),
+ ('abcdefg', 'varchar(5)'),
+ ('abcdefg', 'varchar(10)'),
+ ('abcdefghijk', 'varchar(10)')) 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 $$;
--
2.47.3
