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?
Date: Sun, 05 Feb 2023 00:34:47 -0500
Message-ID: <2348186.1675575287@sss.pgh.pa.us> (raw)
In-Reply-To: <MN2PR15MB2560EF9522E4971792A7209B85D59@MN2PR15MB2560.namprd15.prod.outlook.com>
References: <MN2PR15MB2560EF9522E4971792A7209B85D59@MN2PR15MB2560.namprd15.prod.outlook.com>
"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 = 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, ldh@laurent-hasson.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Behavior of pg_catalog dependent on search_path: expected or bug?
In-Reply-To: <2348186.1675575287@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox