Re: Request for new column in pg_namespace

On Sun, Dec 15, 2024 at 2:20 PM 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.
>

It'll simplify the SQL to pass it a pg_class.relnamespace value, since
that's what's stored in pg_class.

select ...
from pg_class cl INNER JOIN ...
where not is_system_schema(cl.relnamespace)
and ...;

Might it be slightly slower? Sure... but pg_class and pg_namespace aren't
giant tables, and the queries won't run thousands of times per day. Thus,
in this case, a little less efficiency for much cleaner code is an
acceptable trade-off TO ME.

Heck, given how often "pg_class cl INNER JOIN pg_namespace nsp ON
cl.relnamespace = nsp.oid" appears in my (and so much other code around the
Internet), I should probably create a view that joins the two tables, and
adds an is_system_schema column.

That would *really* simplify my code...

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!