Thread: support for CREATE MODULE

Hi,

On Thu, Feb 03, 2022 at 05:25:27AM +0100, Pavel Stehule wrote:
> 
> čt 3. 2. 2022 v 3:28 odesílatel Swaha Miller <swaha.miller@gmail.com>
> napsal:
> 
> > Hi,
> >
> > I'm following up from Jim's POC for adding MODULE to PostgreSQL. [1]
> >
> > My proposal implements modules as schema objects to be stored in a new
> > system catalog pg_module with new syntax for CREATE [OR REPLACE] MODULE,
> > ALTER MODULE, DROP MODULE and for GRANT and REVOKE for privileges on
> > modules and module routines. I am attempting to follow the SQL spec.
> > However, for right now, I'm proposing to support only routines as module
> > contents, with local temporary tables and path specifications as defined
> > in the SQL spec, to be supported in a future submission. We could also
> > include support for variables depending on its status. [2]
> 
> I dislike this feature. The modules are partially redundant to schemas and
> to extensions in Postgres, and I am sure, so there is no reason to
> introduce this.
> 
> What is the benefit against schemas and extensions?

I agree with Pavel.  It seems that it's mainly adding another namespacing layer
between schemas and objects, and it's likely going to create a mess.
That's also going to be problematic if you want to add support for module
variables, as you won't be able to use e.g.
dbname.schemaname.modulename.variablename.fieldname.

Also, my understanding was that the major interest of modules (at least for the
routines part) was the ability to make some of them private to the module, but
it doesn't look like it's doing that, so I also don't see any real benefit
compared to schemas and extensions.

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

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.

What do others think?

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/

Hi,

On Thu, Feb 03, 2022 at 10:42:32PM -0300, Alvaro Herrera 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

Ah, sorry I missed this one.

> 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.
> 
> What do others think?

This approach seems way better as it indeed fixes the qualification issues with
the patch proposed in this thread.

On Wed, Feb  2, 2022 at 06:28:30PM -0800, Swaha Miller wrote:
> Hi,
> 
> I'm following up from Jim's POC for adding MODULE to PostgreSQL. [1]
> 
> My proposal implements modules as schema objects to be stored in a new
> system catalog pg_module with new syntax for CREATE [OR REPLACE] MODULE,

You might want to consider the steps that are most successful at getting
Postgres patches accepted:

    https://wiki.postgresql.org/wiki/Todo
    Desirability -> Design -> Implement -> Test -> Review -> Commit

In this case, you have jumped right to Implement.  Asking about
Desirability first can avoid a lot of effort.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 2022-Feb-04, Swaha Miller wrote:

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

I don't agree that what he proposed was similar to my proposal.  The
main problem I saw in his proposal is that he was saying that modules
would be *within* schemas, which is where I think the whole thing
derailed completely.

He said:

> [ This patch ] [...] allows for 3-part (or 4 with the database name)
> naming of objects within the module. 

He then showed the following example:

> CREATE SCHEMA foo;
> CREATE MODULE foo.bar
>   CREATE FUNCTION hello() [...]
> SELECT foo.bar.hello();

Notice the three-part name there.  That's a disaster, because the name
resolution rules become very complicated or ambiguous.  What I describe
avoids that disaster, by forcing there to be two-part names only: a
module lives on its own, not in a schema, so a function name always has
at most two parts (never three), and the first part can always be
resolved down to a pg_namespace row of some kind.

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/
"La conclusión que podemos sacar de esos estudios es que
no podemos sacar ninguna conclusión de ellos" (Tanenbaum)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> He said:

>> [ This patch ] [...] allows for 3-part (or 4 with the database name)
>> naming of objects within the module. 

> He then showed the following example:

>> CREATE SCHEMA foo;
>> CREATE MODULE foo.bar
>> CREATE FUNCTION hello() [...]
>> SELECT foo.bar.hello();

> Notice the three-part name there.  That's a disaster, because the name
> resolution rules become very complicated or ambiguous.

Right.  We've looked into that before --- when I made pg_namespace,
I called it that because I thought we might be able to support
nested namespaces --- but it'd really create a mess.  In particular,
the SQL standard says what a three-part name means, and this ain't it.

If we invent modules I think they need to work more like extensions
naming-wise, ie they group objects but have no effect for naming.
Alternatively, you could insist that a module *is* a schema for naming
purposes, with some extra properties but acting exactly like a schema
for naming.  But I don't see what that buys you that you can't get
from an extension that owns a schema that contains all its objects.

On the whole I'm kind of allergic to inventing an entire new concept
that has as much overlap with extensions as modules seem to.  I'd
rather try to understand what functional requirements we're missing
and see if we can add them to extensions.  Yeah, we won't end up being
bug-compatible with Oracle's feature, but that's not a project goal
anyway --- and where we have tried to emulate Oracle closely, it's
often not worked out well (poster child: to_date).

            regards, tom lane

On Fri, Feb 04, 2022 at 05:12:43PM -0500, Tom Lane wrote:
> If we invent modules I think they need to work more like extensions
> naming-wise, ie they group objects but have no effect for naming.
> Alternatively, you could insist that a module *is* a schema for naming
> purposes, with some extra properties but acting exactly like a schema
> for naming.  But I don't see what that buys you that you can't get
> from an extension that owns a schema that contains all its objects.
> 
> On the whole I'm kind of allergic to inventing an entire new concept
> that has as much overlap with extensions as modules seem to.  I'd
> rather try to understand what functional requirements we're missing
> and see if we can add them to extensions.  Yeah, we won't end up being
> bug-compatible with Oracle's feature, but that's not a project goal
> anyway --- and where we have tried to emulate Oracle closely, it's
> often not worked out well (poster child: to_date).

If I'm understanding correctly, you are suggesting that CREATE MODULE would
be more like creating an extension without a control file, installation
script, etc.  Objects would be added aѕ members with something like ALTER
MODULE ADD, and members could share properties such as access control.  And
this might be possible to do by enhancing CREATE EXTENSION instead of
creating a new catalog, dependency type, etc.

I think this could be a nice way to sidestep the naming resolution problems
discussed upthread while still allowing folks to group objects together in
some meaningful way.  Also, while it might be nice to have separate CREATE
EXTENSION and CREATE MODULE commands, perhaps they would use roughly the
same code paths behind the scenes.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

Nathan Bossart <nathandbossart@gmail.com> writes:
> On Fri, Feb 04, 2022 at 05:12:43PM -0500, Tom Lane wrote:
>> On the whole I'm kind of allergic to inventing an entire new concept
>> that has as much overlap with extensions as modules seem to.  I'd
>> rather try to understand what functional requirements we're missing
>> and see if we can add them to extensions.  Yeah, we won't end up being
>> bug-compatible with Oracle's feature, but that's not a project goal
>> anyway --- and where we have tried to emulate Oracle closely, it's
>> often not worked out well (poster child: to_date).

> If I'm understanding correctly, you are suggesting that CREATE MODULE would
> be more like creating an extension without a control file, installation
> script, etc.  Objects would be added aѕ members with something like ALTER
> MODULE ADD, and members could share properties such as access control.  And
> this might be possible to do by enhancing CREATE EXTENSION instead of
> creating a new catalog, dependency type, etc.

> I think this could be a nice way to sidestep the naming resolution problems
> discussed upthread while still allowing folks to group objects together in
> some meaningful way.  Also, while it might be nice to have separate CREATE
> EXTENSION and CREATE MODULE commands, perhaps they would use roughly the
> same code paths behind the scenes.

Hm. If the functional requirement is "group objects without needing
any out-in-the-filesystem infrastructure", then I could see defining
a module as being exactly like an extension except there's no such
infrastructure --- and hence no concept of versions, plus pg_dump
needs to act differently.  That's probably enough semantic difference
to justify using a separate word, even if we can share a lot of
code infrastructure.

            regards, tom lane

On 04.02.22 23:12, Tom Lane wrote:
> Right.  We've looked into that before --- when I made pg_namespace,
> I called it that because I thought we might be able to support
> nested namespaces --- but it'd really create a mess.  In particular,
> the SQL standard says what a three-part name means, and this ain't it.

Modules are part of the SQL standard, so there is surely some 
name-resolution system specified there as well.

On Thu, Feb 10, 2022 at 08:53:15AM -0800, Swaha Miller wrote:
> On Fri, Feb 4, 2022 at 3:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
>     Hm. If the functional requirement is "group objects without needing
>     any out-in-the-filesystem infrastructure", then I could see defining
>     a module as being exactly like an extension except there's no such
>     infrastructure --- and hence no concept of versions, plus pg_dump
>     needs to act differently.  That's probably enough semantic difference
>     to justify using a separate word, even if we can share a lot of
>     code infrastructure.
> 
> Then as a first cut for modules, could we add CREATE MODULE
> syntax which adds an entry to pg_extension like CREATE EXTENSION
> does? And also add a new column to pg_extension to distinguish 
> modules from extensions. 
> 
> The three-part path name resolution for functions would remain the 
> same, nothing would need to change there because of modules.
> 
> Would that be an acceptable direction to go?

Well, that would allow us to have CREATE EXTENSION syntax, but what
would it do that CREATE SCHEMA does not?

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On 2022-Feb-04, Tom Lane wrote:

> If we invent modules I think they need to work more like extensions
> naming-wise, ie they group objects but have no effect for naming.

I think modules are an orthogonal concept to extensions, and trying to
mix both is messy.

The way I see modules working is as a "logical" grouping of objects --
they provide encapsulated units of functionality.  A module has private
functions, which cannot be called except from other functions in the
same module.  You can abstract them out of the database design, leaving
you with only the exposed functions, the public API.

An extension is a way to distribute or package objects.  An extension
can contain a module, and of course you should be able to use an
extension to distribute a module, or even several modules.  In fact, I
think it should be possible to have several extensions contribute
different objects to the same module.

But things like name resolution rules (search path) are not affected by
how the objects are distributed, whereas the search path is critical in
how you think about the objects in a module.


If modules are just going to be extensions, I see no point.

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/

On Thu, Feb 10, 2022 at 4:17 PM Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2022-Feb-04, Tom Lane wrote:
> > If we invent modules I think they need to work more like extensions
> > naming-wise, ie they group objects but have no effect for naming.
>
> I think modules are an orthogonal concept to extensions, and trying to
> mix both is messy.
>
> The way I see modules working is as a "logical" grouping of objects --
> they provide encapsulated units of functionality.  A module has private
> functions, which cannot be called except from other functions in the
> same module.  You can abstract them out of the database design, leaving
> you with only the exposed functions, the public API.
>
> An extension is a way to distribute or package objects.  An extension
> can contain a module, and of course you should be able to use an
> extension to distribute a module, or even several modules.  In fact, I
> think it should be possible to have several extensions contribute
> different objects to the same module.
>
> But things like name resolution rules (search path) are not affected by
> how the objects are distributed, whereas the search path is critical in
> how you think about the objects in a module.
>
> If modules are just going to be extensions, I see no point.

+1.

I think that extensions, as we have them in PostgreSQL today, are
basically feature-complete. In my experience, they do all the things
that we need them to do, and pretty well. I think Tom was correct when
he predicted many years ago that getting the extension feature into
PostgreSQL would be remembered as one the biggest improvements of that
era. (I do not recall his exact words.)

But the same is clearly not true of schemas. Schemas are missing
features that people expect to have, private objects being one of
them, and variables another, and I think there are other things
missing, too. Also, search_path is an absolutely wretched design and
I'd propose ripping it out if I had any sensible idea what would
replace it.

IMHO, if we're going to do anything at all in this area, it ought to
be targeting the rather-large weaknesses of the schema system, rather
than anything about the extension system.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

On Mon, Feb 14, 2022 at 03:23:07PM -0800, Swaha Miller wrote:
> A prominent use case for grouping functions into modules would
> be access control on the group as a whole, with one command
> for an entire module instead of many individual functions. One reason
> for such a grouping is to set ACLs. Users migrating their database from
> commercial databases to PostgreSQL wanting to set ACLs on
> thousands or hundreds of thousands of functions would benefit from
> a grouping concept like modules.
> 
> If users use schemas to group functions, then they have to break up
> functions that may have all been in one namespace into a bunch of new
> schemas. So we'd like to have a different grouping mechanism that can
> group functions within a schema. So we're looking at a new construct like
> modules that can serve that purpose without introducing sub-schemas.

I was working on a talk about microservices today and decided to create
two schemas --- a public one that has USAGE permission for other services
with views and SECURITY DEFINER functions that referenced a private
schema that can only be accessed by the owning service.  Is that a usage
pattern that requires modules since it already works with schemas and
just uses schema permissions to designate public/private schema
interfaces.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Mon, Feb 14, 2022 at 07:42:21PM -0500, Bruce Momjian wrote:
> On Mon, Feb 14, 2022 at 03:23:07PM -0800, Swaha Miller wrote:
> > A prominent use case for grouping functions into modules would
> > be access control on the group as a whole, with one command
> > for an entire module instead of many individual functions. One reason
> > for such a grouping is to set ACLs. Users migrating their database from
> > commercial databases to PostgreSQL wanting to set ACLs on
> > thousands or hundreds of thousands of functions would benefit from
> > a grouping concept like modules.
> > 
> > If users use schemas to group functions, then they have to break up
> > functions that may have all been in one namespace into a bunch of new
> > schemas. So we'd like to have a different grouping mechanism that can
> > group functions within a schema. So we're looking at a new construct like
> > modules that can serve that purpose without introducing sub-schemas.
> 
> I was working on a talk about microservices today and decided to create
> two schemas --- a public one that has USAGE permission for other services
> with views and SECURITY DEFINER functions that referenced a private
> schema that can only be accessed by the owning service.  Is that a usage
> pattern that requires modules since it already works with schemas and
> just uses schema permissions to designate public/private schema
> interfaces.

Attached is an example.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

On Tue, Feb 15, 2022 at 12:29:54PM -0800, Swaha Miller wrote:
> On Mon, Feb 14, 2022 at 4:58 PM Bruce Momjian <bruce@momjian.us> wrote:
>     > I was working on a talk about microservices today and decided to create
>     > two schemas --- a public one that has USAGE permission for other services
>     > with views and SECURITY DEFINER functions that referenced a private
>     > schema that can only be accessed by the owning service.  Is that a usage
>     > pattern that requires modules since it already works with schemas and
>     > just uses schema permissions to designate public/private schema
>     > interfaces.
> 
> 
> I think the reason for modules would be to make it a better experience for
> PostgreSQL users to do what they need. Yours is a great example

...

> Yes, anything a user may want to do with modules is likely possible to
> do already with schemas. But just because it is possible doesn't mean
> it is not tedious and awkward because of the mechanisms available to do 
> them now. And I would advocate for more expressive constructs to enable
> users of PostgreSQL to focus and reason about more of the "what" than 
> the "how" of the systems they are trying to build or migrate.

Well, I think there are two downsides of having modules that do
some things like schemas, and some not:

*  You now have two ways of controlling namespaces and permissions
*  It is unclear how the two methods would interact

Some people might find that acceptable, but historically more people have
rejected that, partly due to user API complexity and partly due to
server code complexity.

Given what you can do with Postgres already, what is trying to be
accomplished?  Having public and private objects in the same schema? 
Having public objects in a schema as visible to some users and private
objects invisible to them?  The visibility control is currently not
possible without using two schemas, but access control is possible.

Now, if we could throw away are current schema/permission controls and
just use one based on modules, that might be more acceptable, but it
would cause too much breakage and be non-standard, and also be less
flexible than what we already support.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

It seems unlikely that this will be committed for v15.  Swaha, should the
commitfest entry be adjusted to v16 and moved to the next commitfest?

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Thu, Mar 17, 2022 at 04:26:31PM -0700, Swaha Miller wrote:
> On Thu, Mar 17, 2022 at 4:16 PM Nathan Bossart <nathandbossart@gmail.com>
> wrote:
> 
>> It seems unlikely that this will be committed for v15.  Swaha, should the
>> commitfest entry be adjusted to v16 and moved to the next commitfest?
>>
>>
> Yes please, thank you Nathan

Done!

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Thu, Mar 17, 2022 at 04:30:43PM -0700, Nathan Bossart wrote:
> On Thu, Mar 17, 2022 at 04:26:31PM -0700, Swaha Miller wrote:
>> On Thu, Mar 17, 2022 at 4:16 PM Nathan Bossart <nathandbossart@gmail.com>
>> wrote:
>>> It seems unlikely that this will be committed for v15.  Swaha, should the
>>> commitfest entry be adjusted to v16 and moved to the next commitfest?
>>>
>>>
>> Yes please, thank you Nathan
> 
> Done!

I spoke with Swaha off-list, and we agreed that this commitfest entry can
be closed for now.  I'm going to mark it as returned-with-feedback.

-- 
Nathan Bossart
Amazon Web Services: https://aws.amazon.com