Re: Request for new column in pg_namespace

On Sun, 15 Dec 2024 at 14:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Isaac Morland <isaac(dot)morland(at)gmail(dot)com> writes:
> > On Sun, 15 Dec 2024 at 12:29, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> What I'd suggest as an improvement that could be implemented
> >> immediately is to wrap the checks in a user-defined function
> >> like "is_system_schema(nspname name)".
>
> > Would it make sense to make the parameter be of type regnamespace?
>
> Meh ... you could, but what the function really needs is the name.
> Getting from regnamespace (which is an OID) to the name would incur
> an extra syscache lookup. Admittedly, if it removes the need for
> the calling query to join to pg_namespace at all, you'd probably
> come out about even --- the net effect would be about like a
> hashjoin to pg_namespace, I think, since the syscache would act
> like the inner hashtable of a hashjoin.

Thanks for the critique. It occurs to me that this function is perhaps just
as much about “is this name a system-reserved name?” as “is this schema a
system schema?”. So putting in a name that doesn’t actually exist in the
database should be perfectly valid, which of course only works if it takes
a string. Generally speaking I am a big fan of the reg* data types but in
this specific case I think it’s not a clear win. I might still suggest
providing both versions using function overloading.