RE: Behavior of pg_catalog dependent on search_path: expected or bug?

> -----Original Message-----
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Sent: Sunday, February 5, 2023 00:35
> To: ldh(at)laurent-hasson(dot)com
> Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
> Subject: Re: Behavior of pg_catalog dependent on search_path: expected
> or bug?
>
> "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> writes:
> > 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'
>
> The problem is that what regprocedure-to-text produces is search path
> dependent: it might be 'sample(anyelement)', or it might be
> 'public.sample(anyelement)', depending on whether public is in the
> search path.
>
> You could perhaps write an OR to try both of those possibilities, but the
> whole query seems quite ugly and probably slow, and an OR would make
> it even slower. Personally I'd try something like
>
> exists(select 1
> from pg_catalog.pg_proc p
> join pg_catalog.pg_namespace n on p.pronamespace = n.oid
> where n.nspname = 'public'
> and p.proname = 'sample'
> and p.proargtypes = array['anyelement'::regtype]::oidvector);
>
> which seems to produce a good query plan.
>
> > If this proves to be a bug, I’ll gladly enter the details in the bug system.
>
> It's intentional behavior. Maybe if we were doing regprocedure over
> again today, we'd think twice about the conditional schema qualification
> ... but that behavior is a couple of decades old, so I think it's too late to
> re-litigate it.
>
> regards, tom lane

Thank you so much! And David too. I was surprised by that behavior and thought it might be a bug, but now I understand why and it's been what it is for a long time. Thank you for the enhanced query too. I will try it asap.

Laurent.