Thread: Behavior of pg_catalog dependent on search_path: expected or bug?
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.
Re: Behavior of pg_catalog dependent on search_path: expected or bug?
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'
"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
RE: Behavior of pg_catalog dependent on search_path: expected or bug?
> -----Original Message----- > From: Tom Lane <tgl@sss.pgh.pa.us> > Sent: Sunday, February 5, 2023 00:35 > To: ldh@laurent-hasson.com > Cc: pgsql-bugs@lists.postgresql.org > Subject: Re: Behavior of pg_catalog dependent on search_path: expected > or bug? > > "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 Thank you so much! And David too. I was surprised by that behavior and thought it might be a bug, but now I understand whyand it's been what it is for a long time. Thank you for the enhanced query too. I will try it asap. Laurent.