From: Tom Lane <tgl@sss.pgh.pa.us>
To: "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Behavior of pg_catalog dependent on search_path: expected or bug?
In-reply-to: 
 <MN2PR15MB2560EF9522E4971792A7209B85D59@MN2PR15MB2560.namprd15.prod.outlook.com>
References: 
 <MN2PR15MB2560EF9522E4971792A7209B85D59@MN2PR15MB2560.namprd15.prod.outlook.com>
Comments: In-reply-to "ldh@laurent-hasson.com" <ldh@laurent-hasson.com>
	message dated "Sun, 05 Feb 2023 04:38:39 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <2348185.1675575287.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Sun, 05 Feb 2023 00:34:47 -0500
Message-ID: <2348186.1675575287@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2348186.1675575287%40sss.pgh.pa.us>
Precedence: bulk

"ldh@laurent-hasson.com" <ldh@laurent-hasson.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 =3D n.oid
>  where p.oid::regprocedure::text=3D'sample(anyelement)'
>    and n.nspname =3D '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 =3D n.oid
         where n.nspname =3D 'public'
           and p.proname =3D 'sample'
           and p.proargtypes =3D array['anyelement'::regtype]::oidvector);

which seems to produce a good query plan.

> If this proves to be a bug, I=E2=80=99ll 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