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

On Sat, Feb 4, 2023 at 9:38 PM ldh(at)laurent-hasson(dot)com <
ldh(at)laurent-hasson(dot)com> wrote:

>
>
> 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'
>

Working as documented:

https://www.postgresql.org/docs/current/datatype-oid.html

"The reg<...> input converter handles the <...> lookup according to the
schema path setting, and so it does the “right thing” automatically."

and, more pertinent to your example:

"All of the OID alias types for objects that are grouped by namespace
accept schema-qualified names, and will display schema-qualified names on
output if the object would not be found in the current search path without
being qualified."

Thus what is really failing is your attempt to compare the text cast result
in the where clause to a non-schema qualified procedure named
sample(anyelement).

David J.

P.S. Please provide the behavior you observe and why you think it is
incorrect in future reports, don't just say "it fails", there are many ways
in which things can fail.
P.P.S. This mailing list is the bug system so you've already entered the
details.