MIME-Version: 1.0
References: <CAFj8pRCBKqh=hs-QM9dscC5GfeGbKam=2zGH8ybzkYAAVgjFKg@mail.gmail.com>
 <202202040142.luk526wfnjjl@alvherre.pgsql>
In-Reply-To: <202202040142.luk526wfnjjl@alvherre.pgsql>
From: Swaha Miller <swaha.miller@gmail.com>
Date: Fri, 4 Feb 2022 12:52:08 -0800
Message-ID: <CAPXknY7v2AX+Pvbw9+_4AWSRC6J49dGTBre61aZfJcU0dCemuA@mail.gmail.com>
Subject: Re: support for CREATE MODULE
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000044ae6b05d7376d8a"
Archived-At: <https://www.postgresql.org/message-id/CAPXknY7v2AX%2BPvbw9%2B_4AWSRC6J49dGTBre61aZfJcU0dCemuA%40mail.gmail.com>
Precedence: bulk

--00000000000044ae6b05d7376d8a
Content-Type: text/plain; charset="UTF-8"

On Thu, Feb 3, 2022 at 5:42 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2022-Feb-03, Pavel Stehule wrote:
>
> > The biggest problem is coexistence of Postgres's SEARCH_PATH  object
> > identification, and local and public scopes used in MODULEs or in
> Oracle's
> > packages.
> >
> > I can imagine MODULES as third level of database unit object grouping
> with
> > following functionality
> >
> > 1. It should support all database objects like schemas
>
> I proposed a way for modules to coexist with schemas that got no reply,
> https://postgr.es/m/202106021908.ddmebx7qfdld@alvherre.pgsql
>

Yes, I arrived a little after that thread, and used that as my starting
point.

The POC patch Jim Mlodgenski had on that thread was similar to your
proposed
way - modules were rows in pg_namespace, with the addition of a new column
in pg_namespace for the nspkind (module or schema.)

Jim had asked about two options  -- the above approach and an alternative
one
of having a pg_module system catalog and got some input
https://www.postgresql.org/message-id/2897116.1622642280%40sss.pgh.pa.us

Picking up from there, I am exploring the alternative approach. And what
qualified
names would look like and how they get interpreted unambiguously, when
schemas and modules co-exist. (Also, being new to PostgreSQL, it has been a
great learning exercise for me on some of the internals of PostgreSQL.)

With modules being their own type of object stored in a pg_module system
catalog, deconstructing a qualified path could always give precedence to
schema over module. So when there is function f() in schema s and another
function f() in module s in schema public, then s.f() would invoke the
former.

What are some other directions we might want to take this patch?

I still think that that idea is valuable; it would let us create
> "private" routines, for example, which are good for encapsulation.
> But the way it interacts with schemas means we don't end up with a total
> mess in the namespace resolution rules.  I argued that modules would
> only have functions, and maybe a few other useful object types, but not
> *all* object types, because we don't need all object types to become
> private.  For example, I don't think I would like to have data types or
> casts to be private, so they can only be in a schema and they cannot be
> in a module.
>
> Of course, that idea of modules would also ease porting large DB-based
> applications from other database systems.
>

Indeed. Looking at commercial databases Oracle and Microsoft SQLServer,
they both have implemented module-like structures.

Swaha Miller

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Feb 3, 2022 at 5:42 PM Alvaro Her=
rera &lt;<a href=3D"mailto:alvherre@alvh.no-ip.org">alvherre@alvh.no-ip.org=
</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex">On 2022-Feb-03, Pavel Stehule wrote:<br>
<br>
&gt; The biggest problem is coexistence of Postgres&#39;s SEARCH_PATH=C2=A0=
 object<br>
&gt; identification, and local and public scopes used in MODULEs or in Orac=
le&#39;s<br>
&gt; packages.<br>
&gt; <br>
&gt; I can imagine MODULES as third level of database unit object grouping =
with<br>
&gt; following functionality<br>
&gt; <br>
&gt; 1. It should support all database objects like schemas<br>
<br>
I proposed a way for modules to coexist with schemas that got no reply,<br>
<a href=3D"https://postgr.es/m/202106021908.ddmebx7qfdld@alvherre.pgsql" re=
l=3D"noreferrer" target=3D"_blank">https://postgr.es/m/202106021908.ddmebx7=
qfdld@alvherre.pgsql</a><br></blockquote><div><br></div><div>Yes, I arrived=
 a=C2=A0little after that thread, and used that as my starting point.</div>=
<div><br></div><div>The POC patch Jim Mlodgenski had on that thread was sim=
ilar to your proposed=C2=A0</div><div>way - modules were rows in pg_namespa=
ce, with the addition of a new column in pg_namespace for the nspkind (modu=
le or schema.)</div><div><br></div><div>Jim had asked about two options=C2=
=A0 -- the above approach and an alternative one=C2=A0</div><div>of having =
a pg_module system catalog and got some input</div><div><a href=3D"https://=
www.postgresql.org/message-id/2897116.1622642280%40sss.pgh.pa.us" target=3D=
"_blank">https://www.postgresql.org/message-id/2897116.1622642280%40sss.pgh=
.pa.us</a><br></div><div><br></div><div>Picking up from there, I am explori=
ng the alternative approach. And what qualified=C2=A0</div><div>names would=
 look like and how they get interpreted unambiguously, when=C2=A0</div><div=
>schemas and modules co-exist. (Also, being new to PostgreSQL, it has been =
a=C2=A0</div><div>great learning exercise for me on some of the internals o=
f PostgreSQL.)</div><div><br></div><div>With modules being their own type o=
f object stored in a pg_module system=C2=A0</div><div>catalog, deconstructi=
ng a qualified path could always give precedence to=C2=A0</div><div>schema =
over module. So when there is function f() in schema s and another=C2=A0</d=
iv><div>function f() in module s in schema public, then s.f() would invoke =
the former.</div><div><br></div><div>What are some other directions we migh=
t=C2=A0want to=C2=A0take this patch?=C2=A0</div><div><br></div><blockquote =
class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px sol=
id rgb(204,204,204);padding-left:1ex">
I still think that that idea is valuable; it would let us create<br>
&quot;private&quot; routines, for example, which are good for encapsulation=
.<br>
But the way it interacts with schemas means we don&#39;t end up with a tota=
l<br>
mess in the namespace resolution rules.=C2=A0 I argued that modules would<b=
r>
only have functions, and maybe a few other useful object types, but not<br>
*all* object types, because we don&#39;t need all object types to become<br=
>
private.=C2=A0 For example, I don&#39;t think I would like to have data typ=
es or<br>
casts to be private, so they can only be in a schema and they cannot be<br>
in a module.<br>
<br>
Of course, that idea of modules would also ease porting large DB-based<br>
applications from other database systems.<br></blockquote><div><br></div><d=
iv>Indeed. Looking at commercial databases Oracle and Microsoft SQLServer,=
=C2=A0</div><div>they both have implemented module-like structures.<br><br>=
Swaha Miller</div></div></div>

--00000000000044ae6b05d7376d8a--