Behavior of pg_catalog dependent on search_path: expected or bug?

Hello All!

I hope 2023 has been good for all of you! 😊

I was creating some aggregate for a project and wrote a simple script to create it if not already there:

CREATE OR REPLACE FUNCTION public.sample_agg (anyelement, anyelement)
RETURNS anyelement LANGUAGE SQL COST 1 IMMUTABLE STRICT AS $$
SELECT $1;
$$;
DO $$ BEGIN
if not exists (select p.oid::regprocedure
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on p.pronamespace = n.oid
where p.oid::regprocedure::text='sample(anyelement)' and n.nspname = 'public'
) THEN
CREATE AGGREGATE public.sample (
sfunc = public.sample_agg,
basetype = anyelement,
stype = anyelement
);
END IF;
END $$;

This script worked the first time but then failed on reruns. After some investigation, I noticed that this was run as part of a larger script and a “search_path” was set. I was able to isolate a behavior that I am not able to understand, i.e., bug or expected behavior?

The code below fails:

SET search_path TO XXX;
select p.oid::regprocedure
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on p.pronamespace = n.oid
where p.oid::regprocedure::text='sample(anyelement)'
and n.nspname = 'public'

This code however works (adding public to the search_path):

SET search_path TO XXX, public;
select p.oid::regprocedure
from pg_catalog.pg_proc p
join pg_catalog.pg_namespace n on p.pronamespace = n.oid
where p.oid::regprocedure::text='sample(anyelement)'
and n.nspname = 'public'

Why is a query on pg_catalog tables dependent on the search_path variable when all parts of the query are properly prefixed with pg_catalog? I testec this on PG 11, PG13 and PG15, all on Windows. The behavior is consistent. I didn’t get a chance to test on Linux.

If this proves to be a bug, I’ll gladly enter the details in the bug system.

Thank you,
Laurent.