Re: [PATCH] pg_input_is_valid() reuses the first type name inside a PL/pgSQL loop

On Fri, Nov 13, 2026 at 1:53 AM Andreas Karlsson <andreas(at)proxel(dot)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