public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Request for new column in pg_namespace
Date: Sun, 15 Dec 2024 19:16:21 -0500
Message-ID: <CANzqJaBdXYTjAxNPGihAYNbs2-uxEnhY2ZpRHboVCNVx_oOx_w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CANzqJaCCF9kpfdbWbRR2xuqH_qaBgL+me13w2c-5S=_YATKh+Q@mail.gmail.com>
<CAFj8pRDxMpQv4P3Bi-zEQ+OQ2dsVCZ+g-vbpU-F3qvozZKjUzA@mail.gmail.com>
<[email protected]>
<CAMsGm5dmNjPuYAg8V7yutENjgJod5pNXhoQpiWoPH3e7i1RrVg@mail.gmail.com>
<[email protected]>
On Sun, Dec 15, 2024 at 2:20 PM Tom Lane <[email protected]> wrote:
> Isaac Morland <[email protected]> writes:
> > On Sun, 15 Dec 2024 at 12:29, Tom Lane <[email protected]> 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!
view thread (4+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Request for new column in pg_namespace
In-Reply-To: <CANzqJaBdXYTjAxNPGihAYNbs2-uxEnhY2ZpRHboVCNVx_oOx_w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox