Re: support for CREATE MODULE

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(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> čt 3. 2. 2022 v 5:46 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)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(dot)miller(at)gmail(dot)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