MIME-Version: 1.0
References: <CANzqJaCCF9kpfdbWbRR2xuqH_qaBgL+me13w2c-5S=_YATKh+Q@mail.gmail.com>
 <CAFj8pRDxMpQv4P3Bi-zEQ+OQ2dsVCZ+g-vbpU-F3qvozZKjUzA@mail.gmail.com>
 <1627466.1734283766@sss.pgh.pa.us> <CAMsGm5dmNjPuYAg8V7yutENjgJod5pNXhoQpiWoPH3e7i1RrVg@mail.gmail.com>
 <1753818.1734290423@sss.pgh.pa.us>
In-Reply-To: <1753818.1734290423@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 15 Dec 2024 19:16:21 -0500
Message-ID: <CANzqJaBdXYTjAxNPGihAYNbs2-uxEnhY2ZpRHboVCNVx_oOx_w@mail.gmail.com>
Subject: Re: Request for new column in pg_namespace
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c2b37606295817cf"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBdXYTjAxNPGihAYNbs2-uxEnhY2ZpRHboVCNVx_oOx_w%40mail.gmail.com>
Precedence: bulk

--000000000000c2b37606295817cf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sun, Dec 15, 2024 at 2:20=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Isaac Morland <isaac.morland@gmail.com> writes:
> > On Sun, 15 Dec 2024 at 12:29, Tom Lane <tgl@sss.pgh.pa.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 =3D nsp.oid" appears in my (and so much other code around t=
he
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...

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

--000000000000c2b37606295817cf
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, Dec 15, 2024 at 2:20=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Isaac Morland &lt;<a href=3D"mailto:isa=
ac.morland@gmail.com" target=3D"_blank">isaac.morland@gmail.com</a>&gt; wri=
tes:<br>
&gt; On Sun, 15 Dec 2024 at 12:29, Tom Lane &lt;<a href=3D"mailto:tgl@sss.p=
gh.pa.us" target=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>
&gt;&gt; What I&#39;d suggest as an improvement that could be implemented<b=
r>
&gt;&gt; immediately is to wrap the checks in a user-defined function<br>
&gt;&gt; like &quot;is_system_schema(nspname name)&quot;.<br>
<br>
&gt; Would it make sense to make the parameter be of type regnamespace?<br>
<br>
Meh ... you could, but what the function really needs is the name.<br>
Getting from regnamespace (which is an OID) to the name would incur<br>
an extra syscache lookup.=C2=A0 Admittedly, if it removes the need for<br>
the calling query to join to pg_namespace at all, you&#39;d probably<br>
come out about even --- the net effect would be about like a<br>
hashjoin to pg_namespace, I think, since the syscache would act<br>
like the inner hashtable of a hashjoin.<br></blockquote><div><br></div><div=
>It&#39;ll simplify the SQL to pass it a <font face=3D"monospace">pg_class<=
/font><span style=3D"font-family:monospace">.relnamespace</span>=C2=A0 valu=
e, since that&#39;s what&#39;s stored in pg_class.</div><div><br></div><div=
><font face=3D"monospace">select ...<br>from pg_class cl INNER JOIN ...<br>=
where not is_system_schema(cl.relnamespace)</font></div><div><font face=3D"=
monospace">=C2=A0 and ...;</font></div></div><div><br></div><div>Might it b=
e slightly slower?=C2=A0 Sure... but=C2=A0pg_class and pg_namespace aren=
9;t giant tables, and the queries won&#39;t run thousands of times per day.=
=C2=A0 Thus, in this case, a little less efficiency for much cleaner code i=
s an acceptable trade-off TO ME.</div><div><br></div><div>Heck, given how o=
ften &quot;pg_class cl INNER JOIN pg_namespace nsp ON cl.relnamespace =3D n=
sp.oid&quot; 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=C2=A0i=
s_system_schema column.</div><div><br></div><div>That would <b>really</b>=
=C2=A0simplify my code...</div><div><br></div><span class=3D"gmail_signatur=
e_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=
=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me=
, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></=
div></div></div></div>

--000000000000c2b37606295817cf--