Thread: support for CREATE MODULE

support for CREATE MODULE

From
Swaha Miller
Date:
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]

Following are some examples of what the new module syntax would look
like. The attached patch has detailed documentation.

CREATE MODULE mtest1 CREATE FUNCTION m1testa() RETURNS text
    LANGUAGE sql
    RETURN '1x';
SELECT mtest1.m1testa();
ALTER MODULE mtest1 CREATE FUNCTION m1testd() RETURNS text
    LANGUAGE sql
    RETURN 'm1testd';
SELECT mtest1.m1testd();
ALTER MODULE mtest1 RENAME TO mtest1renamed;
SELECT mtest1renamed.m1testd();
REVOKE ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() FROM public;
GRANT ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() TO
regress_priv_user1;

I am new to the PostgreSQL community and would really appreciate your
input and feedback.

Thanks,
Swaha Miller
Amazon Web Services

Attachment

Re: support for CREATE MODULE

From
Pavel Stehule
Date:
Hi

č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]

Following are some examples of what the new module syntax would look
like. The attached patch has detailed documentation.

CREATE MODULE mtest1 CREATE FUNCTION m1testa() RETURNS text
    LANGUAGE sql
    RETURN '1x';
SELECT mtest1.m1testa();
ALTER MODULE mtest1 CREATE FUNCTION m1testd() RETURNS text
    LANGUAGE sql
    RETURN 'm1testd';
SELECT mtest1.m1testd();
ALTER MODULE mtest1 RENAME TO mtest1renamed;
SELECT mtest1renamed.m1testd();
REVOKE ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() FROM public;
GRANT ON MODULE mtest1 REFERENCES ON FUNCTION m1testa() TO
regress_priv_user1;

I am new to the PostgreSQL community and would really appreciate your
input and feedback.

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?

Regards

Pavel
 

Re: support for CREATE MODULE

From
Julien Rouhaud
Date:
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.



Re: support for CREATE MODULE

From
Pavel Stehule
Date:


čt 3. 2. 2022 v 5:46 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:
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.

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
2. all public objects should be accessed directly when outer schema is in SEARCH_PATH
3. private objects cannot be accessed from other modules
4. modules should be movable between schemas, databases without a loss of functionality
5. modules should to support renaming without loss of functionality
6. there should be redefined some rules of visibility, because there can be new identifier's collisions and ambiguities
7. there should be defined relation of modules's objects and schema's objects. Maybe an introduction of the default module can be a good idea.

I had the opportunity to see a man who modified routines in pgAdmin. It can be hell, but if we introduce a new concept (and it is an important concept), then there should be strong benefits - for example - possibility of strong encapsulation of code inside modules (or some units - the name is not important).

The problem with pgAdmin maybe can be solved better by adding some annotations to database objects that allows more user friendly organization in the object tree in pgAdmin (and similar tools). Maybe it can be useful to have more tries (defined by locality, semantic, quality, ...).

Regards

Pavel

Re: support for CREATE MODULE

From
Swaha Miller
Date:
Thank you for the feedback Pavel and Julien. I'll try to explain some of the issues and points you raise to the best of my understanding.

The reason for modules is that it would serve as an organizational unit that can allow setting permissions on those units. So, for example, all functions in a module can be subject to setting access permissions on for some user(s) or group(s). I didn't explain it well in the sgml docs, but along with module syntax, I'm proposing introducing privileges to grant/revoke on modules and routines in modules. And why modules for this purpose? Because its in the SQL spec so seems like a way to do it.

I'm adding comments inline for the list of functionality you mentioned. I look forward to discussing this more and figuring out how to make a useful contribution to the community.

On Wed, Feb 2, 2022 at 11:22 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:


čt 3. 2. 2022 v 5:46 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:
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.


I haven't yet added support for variables so will need to look into the problems with this if we're going to do that.
 
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.


Yes, that is indeed the goal/use-case with setting permissions with grant and revoke. Right now, I have proposed create and reference as the kinds of access that can be controlled on modules, and reference as the kind of access that can be controlled on routines inside modules.
 
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 am not extremely familiar with Oracle's packages, but do know of them. I'm wondering if local and public scopes for MODULE is in the SQL spec? (I will check for that...) My thinking was to implement functionality that conforms to the SQL spec, not try to match Oracle's package which differs from the spec in some ways.
 
I can imagine MODULES as third level of database unit object grouping with following functionality

1. It should support all database objects like schemas

Do you mean that schemas should be groupable under modules? My thinking was to follow what the SQL spec says about what objects should be in modules, and I started with routines as one of the objects that there are use cases for. Such a controlling access permissions on routines at some granularity that is not an entire schema and not individual functions/procedures.
 
2. all public objects should be accessed directly when outer schema is in SEARCH_PATH

Yes, an object inside a module is in a schema and can be accessed with schemaname.func() as well as modulename.func() as well as schemaname.modulename.func(). I think you are saying it should be accessible with func() without a qualifying schemaname or modulename if the schemaname is in the search path, and that sounds reasonable too. Unless, of course, func() was created in a module, in which case access permissions for the module and module contents will determine whether func() should be directly accessible. In my current proposal, a previously created func() can't be added to a module created later. The purpose of creating routines inside a module (either when the module is created or after the module is created) would be with the intent of setting access permissions on those routines differently than for the outer schema.
 
3. private objects cannot be accessed from other modules

Yes, I hope that is going to be the case with setting permissions with grant and revoke. Right now, I have proposed create and reference as the kinds of access that can be controlled on modules, and reference as the kind of access that can be controlled on routines inside modules.
 
4. modules should be movable between schemas, databases without a loss of functionality

pg_dump will dump modules so that can provide ways of moving them between databases. I hadn't envisioned moving modules between schemas, but can think of ways that can be supported. Would the objects within the modules also move implicitly to the new schema?
 
5. modules should to support renaming without loss of functionality

yes renaming of modules is supported in my proposal
 
6. there should be redefined some rules of visibility, because there can be new identifier's collisions and ambiguities

I'm not sure I understand this point. Can you please explain more?
 

7. there should be defined relation of modules's objects and schema's objects. Maybe an introduction of the default module can be a good idea.

I was thinking of module as a unit of organization (with the goal of controlling access to it) of objects that are still in some schema, and the module itself as an object that is also in a schema.
 

I had the opportunity to see a man who modified routines in pgAdmin. It can be hell, but if we introduce a new concept (and it is an important concept), then there should be strong benefits - for example - possibility of strong encapsulation of code inside modules (or some units - the name is not important).

The problem with pgAdmin maybe can be solved better by adding some annotations to database objects that allows more user friendly organization in the object tree in pgAdmin (and similar tools). Maybe it can be useful to have more tries (defined by locality, semantic, quality, ...).

Regards

Pavel

Best regards,
Swaha 

Re: support for CREATE MODULE

From
Pavel Stehule
Date:


čt 3. 2. 2022 v 20:21 odesílatel Swaha Miller <swaha.miller@gmail.com> napsal:
Thank you for the feedback Pavel and Julien. I'll try to explain some of the issues and points you raise to the best of my understanding.

The reason for modules is that it would serve as an organizational unit that can allow setting permissions on those units. So, for example, all functions in a module can be subject to setting access permissions on for some user(s) or group(s). I didn't explain it well in the sgml docs, but along with module syntax, I'm proposing introducing privileges to grant/revoke on modules and routines in modules. And why modules for this purpose? Because its in the SQL spec so seems like a way to do it.

This part of the standard is dead - there is no strong reason to implement it.
 

I'm adding comments inline for the list of functionality you mentioned. I look forward to discussing this more and figuring out how to make a useful contribution to the community.

On Wed, Feb 2, 2022 at 11:22 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:


čt 3. 2. 2022 v 5:46 odesílatel Julien Rouhaud <rjuju123@gmail.com> napsal:
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.


I haven't yet added support for variables so will need to look into the problems with this if we're going to do that.
 
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.


Yes, that is indeed the goal/use-case with setting permissions with grant and revoke. Right now, I have proposed create and reference as the kinds of access that can be controlled on modules, and reference as the kind of access that can be controlled on routines inside modules.
 
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 am not extremely familiar with Oracle's packages, but do know of them. I'm wondering if local and public scopes for MODULE is in the SQL spec? (I will check for that...) My thinking was to implement functionality that conforms to the SQL spec, not try to match Oracle's package which differs from the spec in some ways.
 
I can imagine MODULES as third level of database unit object grouping with following functionality

1. It should support all database objects like schemas

Do you mean that schemas should be groupable under modules? My thinking was to follow what the SQL spec says about what objects should be in modules, and I started with routines as one of the objects that there are use cases for. Such a controlling access permissions on routines at some granularity that is not an entire schema and not individual functions/procedures.

SQLspec says so there can be just temporary tables and routines. It is useless. Unfortunately SQL/PSM came too late and there is no progress in the last 20 years.  It is a dead horse.

 
2. all public objects should be accessed directly when outer schema is in SEARCH_PATH

Yes, an object inside a module is in a schema and can be accessed with schemaname.func() as well as modulename.func() as well as schemaname.modulename.func(). I think you are saying it should be accessible with func() without a qualifying schemaname or modulename if the schemaname is in the search path, and that sounds reasonable too. Unless, of course, func() was created in a module, in which case access permissions for the module and module contents will determine whether func() should be directly accessible. In my current proposal, a previously created func() can't be added to a module created later. The purpose of creating routines inside a module (either when the module is created or after the module is created) would be with the intent of setting access permissions on those routines differently than for the outer schema.
 
3. private objects cannot be accessed from other modules

Yes, I hope that is going to be the case with setting permissions with grant and revoke. Right now, I have proposed create and reference as the kinds of access that can be controlled on modules, and reference as the kind of access that can be controlled on routines inside modules.

The permission is not enough strategy -  if I implement some private objects in the module, and I push this module to the schema on the search path, the private objects need to be invisible. I don't want to allow shadowing of public objects by private objects.
 
 
4. modules should be movable between schemas, databases without a loss of functionality

pg_dump will dump modules so that can provide ways of moving them between databases. I hadn't envisioned moving modules between schemas, but can think of ways that can be supported. Would the objects within the modules also move implicitly to the new schema?

I thought more about extending the CREATE EXTENSION command to support modules.
 
 
5. modules should to support renaming without loss of functionality

yes renaming of modules is supported in my proposal

But if I call a module function from the same module, this should work after renaming.  That's mean so there should be some mechanism how to implement routine call without necessity to use absolute path

 
6. there should be redefined some rules of visibility, because there can be new identifier's collisions and ambiguities

I'm not sure I understand this point. Can you please explain more?

I can have function fx in schema s, and then I can have module s in public schema with function fx. What will be called when I write s.fx() ?
 
 

7. there should be defined relation of modules's objects and schema's objects. Maybe an introduction of the default module can be a good idea.

I was thinking of module as a unit of organization (with the goal of controlling access to it) of objects that are still in some schema, and the module itself as an object that is also in a schema.

I understand, but just this is not enough benefit for implementation, when Postgres supports schemas and extensions already. The benefit can be better encapsulation or better isolation than we have with schemas.
 
 

I had the opportunity to see a man who modified routines in pgAdmin. It can be hell, but if we introduce a new concept (and it is an important concept), then there should be strong benefits - for example - possibility of strong encapsulation of code inside modules (or some units - the name is not important).

The problem with pgAdmin maybe can be solved better by adding some annotations to database objects that allows more user friendly organization in the object tree in pgAdmin (and similar tools). Maybe it can be useful to have more tries (defined by locality, semantic, quality, ...).

Regards

Pavel

Best regards,
Swaha 

Re: support for CREATE MODULE

From
Alvaro Herrera
Date:
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/



Re: support for CREATE MODULE

From
Julien Rouhaud
Date:
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.



Re: support for CREATE MODULE

From
Bruce Momjian
Date:
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.




Re: support for CREATE MODULE

From
Swaha Miller
Date:
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

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

Re: support for CREATE MODULE

From
Swaha Miller
Date:
On Fri, Feb 4, 2022 at 10:46 AM Bruce Momjian <bruce@momjian.us> wrote:
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.

Thanks Bruce, that's really helpful. I was building on the discussion in Jim's 
original thread, which is why I went ahead with another POC implementation, 
but I do consider this implementation as part of the desirability/design aspect 
and am hoping to get input from the community to shape this proposal/patch.

Swaha Miller

Re: support for CREATE MODULE

From
Alvaro Herrera
Date:
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)



Re: support for CREATE MODULE

From
Tom Lane
Date:
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



Re: support for CREATE MODULE

From
Jim Mlodgenski
Date:


On Fri, Feb 4, 2022 at 5:12 PM Tom Lane <tgl@sss.pgh.pa.us> 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).


Developers need a way to group related objects in some fashion so
that they can be more easily reasoned about. Modules are just the
way to do this in the spec, but if we want to leverage extensions,
that will work too. Many users who need this only have access through
a database connection. They wouldn't have access to the file system
to add a control file nor a script to add the objects. Enhancing
CREATE EXTENSION to be able to create some sort of empty extension
and then having the ability to add and remove objects from that
extension may be the minimum amount of functionality we would need
to give users the ability to group their objects.

Re: support for CREATE MODULE

From
Nathan Bossart
Date:
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



Re: support for CREATE MODULE

From
Tom Lane
Date:
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



Re: support for CREATE MODULE

From
Peter Eisentraut
Date:
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.



Re: support for CREATE MODULE

From
Swaha Miller
Date:
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?

Swaha

Re: support for CREATE MODULE

From
Bruce Momjian
Date:
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.




Re: support for CREATE MODULE

From
Alvaro Herrera
Date:
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/



Re: support for CREATE MODULE

From
Robert Haas
Date:
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



Re: support for CREATE MODULE

From
Swaha Miller
Date:
On Thu, Feb 10, 2022 at 1:06 PM Bruce Momjian <bruce@momjian.us> wrote:
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?

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.

Also a module would be a grouping of primarily functions. Schemas can
have many other object types. Setting ACLs on groups of functions in a
schema, which is what users could do with a module, would require
breaking up a schema into other schemas based on ACLs. But schemas
allow setting ACLs on other objects in it, not only functions. If we
create groupings based on functions, what happens to the other types
of objects, what schema do they get grouped into? If modules are
supposed to solve setting ACLs on groups of functions, does using
schemas conflate setting ACLs for those other types of objects with
setting ACLs for functions?

Modules could also, in the future, serve as a way of allowing for private
functions within the grouping. Doing this in schemas would require
those kinds of changes in the schema construct.

Swaha

Re: support for CREATE MODULE

From
Bruce Momjian
Date:
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.




Re: support for CREATE MODULE

From
Bruce Momjian
Date:
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.


Attachment

Re: support for CREATE MODULE

From
Swaha Miller
Date:
On Mon, Feb 14, 2022 at 4:58 PM Bruce Momjian <bruce@momjian.us> wrote:
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.

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
for a talk. A practical scenario would require many schemas following this 
pattern of X and X_private. In a more expressive programming language, 
the user would express modular design with classes X1, X2...Xn that 
have both public interface calls and private functions. The modularity would 
reflect what they are trying to do and not be restricted by how they are 
allowed to do so. In this context, I am going to link to a scenario Jim had 
described before 


In your example, the only way a developer would know that X and 
X_private are related is that they are named consistently. With another
grouping construct like Modules, they would both have been in the same
schema, but now they are not. From a long term maintainability of the code 
perspective, that becomes an issue because people make simple mistakes. 
What if in the future a new developer added a 3rd grouping, x_utils. Is it 
related to the other groupings? This has a lower case x instead of the X. 
How is one to know? What if in the future a new developer added a 3rd 
grouping X_utils but without having paid attention to the already existing X 
and X_private? Now naming becomes the means of expressing 
classification and modular design. It is a relatively weak construct 
compared to a language construct.

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.

Swaha

Re: support for CREATE MODULE

From
Pavel Stehule
Date:
Hi


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.

Nobody will talk against better modularization.  But it is not coming with your proposal.

The main issue in this case is fact, so plpgsql is fully integrated to Postgres (on second hand this integration is a big performance win). It is pretty different from PL/SQL. In Oracle you have a package, or any other similar features, because PL/SQL is an "independent" environment to the database engine. You cannot do the same with PL/pgSQL. And if you try to implement some enhancement of object hierarchy for PL/pgSQL, then you have to do it in the PostgreSQL core engine first. I'm 100% for enhancing stored procedures about full modularization, but this feature cannot be implemented step by step because you can break compatibility in any step. We need a robust solution. The solution, that helps with something, but it is not robust, it is not progress.

Regards

Pavel





Swaha

Re: support for CREATE MODULE

From
Jim Mlodgenski
Date:


On Wed, Feb 16, 2022 at 12:20 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

The main issue in this case is fact, so plpgsql is fully integrated to Postgres (on second hand this integration is a big performance win). It is pretty different from PL/SQL. In Oracle you have a package, or any other similar features, because PL/SQL is an "independent" environment to the database engine. You cannot do the same with PL/pgSQL. And if you try to implement some enhancement of object hierarchy for PL/pgSQL, then you have to do it in the PostgreSQL core engine first. I'm 100% for enhancing stored procedures about full modularization, but this feature cannot be implemented step by step because you can break compatibility in any step. We need a robust solution. The solution, that helps with something, but it is not robust, it is not progress.


I'm not sure I understand your feedback. The proposed design for modules
is implemented in the engine and is orthogonal to PL/pgSQL. A module can
contain a mix of PL/pgSQL, PL/perl and PL/TCL functions if one wants
to do something like that.

Do you have any thoughts on how some sort of modularization or grouping
should be implemented? The Module route was the first thought on this
because it's the way the spec tells us we should solve this problem. We
can invent something new if we have a better way of solving this.

Re: support for CREATE MODULE

From
Pavel Stehule
Date:


st 16. 2. 2022 v 14:17 odesílatel Jim Mlodgenski <jimmy76@gmail.com> napsal:


On Wed, Feb 16, 2022 at 12:20 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

The main issue in this case is fact, so plpgsql is fully integrated to Postgres (on second hand this integration is a big performance win). It is pretty different from PL/SQL. In Oracle you have a package, or any other similar features, because PL/SQL is an "independent" environment to the database engine. You cannot do the same with PL/pgSQL. And if you try to implement some enhancement of object hierarchy for PL/pgSQL, then you have to do it in the PostgreSQL core engine first. I'm 100% for enhancing stored procedures about full modularization, but this feature cannot be implemented step by step because you can break compatibility in any step. We need a robust solution. The solution, that helps with something, but it is not robust, it is not progress.


I'm not sure I understand your feedback. The proposed design for modules
is implemented in the engine and is orthogonal to PL/pgSQL. A module can
contain a mix of PL/pgSQL, PL/perl and PL/TCL functions if one wants
to do something like that.

Do you have any thoughts on how some sort of modularization or grouping
should be implemented? The Module route was the first thought on this
because it's the way the spec tells us we should solve this problem. We
can invent something new if we have a better way of solving this.


The proposal doesn't help with isolation PL/pgSQL code from outside. This is just secondary collecting, and I agree with other people that say so maybe extending extensions can be good enough for this case.

Regards

Pavel


 

Re: support for CREATE MODULE

From
Bruce Momjian
Date:
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.




Re: support for CREATE MODULE

From
Nathan Bossart
Date:
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



Re: support for CREATE MODULE

From
Swaha Miller
Date:
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

Re: support for CREATE MODULE

From
Nathan Bossart
Date:
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



Re: support for CREATE MODULE

From
Nathan Bossart
Date:
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