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>
In-Reply-To: <1627466.1734283766@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Sun, 15 Dec 2024 12:46:42 -0500
Message-ID: <CANzqJaBZc8odgkF_Gn6kgp7gaU2dMiGhS34oTk=+7S28CEHeig@mail.gmail.com>
Subject: Re: Request for new column in pg_namespace
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000042f54c062952a60c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBZc8odgkF_Gn6kgp7gaU2dMiGhS34oTk%3D%2B7S28CEHeig%40mail.gmail.com>
Precedence: bulk

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

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

> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > ne 15. 12. 2024 v 17:59 odes=C3=ADlatel Ron Johnson <ronljohnsonjr@gmai=
l.com>
> > napsal:
> >> A new boolean column named "indissystem" that's true only for system
> >> relations would make *many* maintenance queries cleaner, since they'd
> >> look like:
> >> select ...
>
> > oid of all system objects is less then 0x4000
>
> That wouldn't help for excluding temp schemas, and it's not totally
> trustworthy for information_schema either.
>
> But I think the real problem with Ron's proposal is that it presumes
> there is a one-size-fits-all notion of "system schema".  As a
> counterexample, for some maintenance activities (such as vacuuming)
> you might wish to process pg_catalog.
>

In that case, one would explicitly mention pg_catalog, no?
where cl.relnamespace =3D nsp.oid
  and (nsp.indissystem =3D false or nsp.nspname =3D 'pg_catalog');


> 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)".
>

Good idea.

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

--00000000000042f54c062952a60c
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 12:29=E2=80=AFPM =
Tom 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 c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">Pavel Stehule &lt;<a href=3D"mailto:pa=
vel.stehule@gmail.com" target=3D"_blank">pavel.stehule@gmail.com</a>&gt; wr=
ites:<br>
&gt; ne 15. 12. 2024 v 17:59 odes=C3=ADlatel Ron Johnson &lt;<a href=3D"mai=
lto:ronljohnsonjr@gmail.com" target=3D"_blank">ronljohnsonjr@gmail.com</a>&=
gt;<br>
&gt; napsal:<br>
&gt;&gt; A new boolean column named &quot;indissystem&quot; that&#39;s true=
 only for system<br>
&gt;&gt; relations would make *many* maintenance queries cleaner, since the=
y&#39;d<br>
&gt;&gt; look like:<br>
&gt;&gt; select ...<br>
<br>
&gt; oid of all system objects is less then 0x4000<br>
<br>
That wouldn&#39;t help for excluding temp schemas, and it&#39;s not totally=
<br>
trustworthy for information_schema either.<br>
<br>
But I think the real problem with Ron&#39;s proposal is that it presumes<br=
>
there is a one-size-fits-all notion of &quot;system schema&quot;.=C2=A0 As =
a<br>
counterexample, for some maintenance activities (such as vacuuming)<br>
you might wish to process pg_catalog.<br></blockquote><div><br></div><div>I=
n that case, one would explicitly mention pg_catalog, no?</div><div><span s=
tyle=3D"font-family:monospace">where cl.relnamespace =3D nsp.oid</span><br =
style=3D"font-family:monospace"><span style=3D"font-family:monospace">=C2=
=A0 and (nsp.indissystem =3D false or=C2=A0</span><span style=3D"font-famil=
y:monospace">nsp.</span><span style=3D"font-family:monospace">nspname =3D &=
#39;pg_catalog&#39;);</span></div><div>=C2=A0</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">What I&#39;d suggest as an improvement that could=
 be implemented<br>
immediately is to wrap the checks in a user-defined function<br>
like &quot;is_system_schema(nspname name)&quot;.<br></blockquote><div><br><=
/div><div>Good idea.</div><div><br></div></div><span class=3D"gmail_signatu=
re_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div di=
r=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil m=
e, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div><=
/div></div></div></div>

--00000000000042f54c062952a60c--