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: Isaac Morland <isaac.morland@gmail.com>
Date: Sun, 15 Dec 2024 15:05:46 -0500
Message-ID: <CAMsGm5dm-taSdESDPn1zVLaptKoFHnzJZowx+WJ6f-sr15d3xg@mail.gmail.com>
Subject: Re: Request for new column in pg_namespace
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Stehule <pavel.stehule@gmail.com>, Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b2cc560629549748"
Archived-At: <https://www.postgresql.org/message-id/CAMsGm5dm-taSdESDPn1zVLaptKoFHnzJZowx%2BWJ6f-sr15d3xg%40mail.gmail.com>
Precedence: bulk

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

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


Thanks for the critique. It occurs to me that this function is perhaps just
as much about =E2=80=9Cis this name a system-reserved name?=E2=80=9D as =E2=
=80=9Cis this schema a
system schema?=E2=80=9D. So putting in a name that doesn=E2=80=99t 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=E2=80=99s not a clear win. I might still sugg=
est
providing both versions using function overloading.

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

<div dir=3D"ltr"><div dir=3D"ltr">On Sun, 15 Dec 2024 at 14:20, Tom Lane &l=
t;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br>=
</div><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;borde=
r-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">Isa=
ac Morland &lt;<a href=3D"mailto:isaac.morland@gmail.com" target=3D"_blank"=
>isaac.morland@gmail.com</a>&gt; writes:<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.</blockquote><div><br></div><div>Tha=
nks for the critique. It occurs to me that this function is perhaps just as=
 much about =E2=80=9Cis this name a system-reserved name?=E2=80=9D as =E2=
=80=9Cis this schema a system schema?=E2=80=9D. So putting in a name that d=
oesn=E2=80=99t actually exist in the database should be perfectly valid, wh=
ich of course only works if it takes a string. Generally speaking I am a bi=
g fan of the reg* data types but in this specific case I think it=E2=80=99s=
 not a clear win. I might still suggest providing both versions using funct=
ion overloading.</div></div></div>

--000000000000b2cc560629549748--