Re: support for CREATE MODULE

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