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.
