Re: Support for CREATE MODULE?

Hi

st 2. 6. 2021 v 15:39 odesílatel Jim Mlodgenski <jimmy76(at)gmail(dot)com> napsal:

> Working with users over the years, some have large libraries of server
> side code sometimes consisting of 100k+ lines of code over 1000+ functions
> and procedures. This usually comes from a migration of a commercial
> database like Oracle where it was best practice to put all of your
> business logic into stored procedures. In these types of apps, just
> managing the code is a challenge. To help classify objects, schemas
> are used, but you are at the mercy of a naming convention to show
> association. For example, a frequent naming convention would be having
> related schemas with the names of foo_bar and foo_baz. For devs, that's
> akin to keeping a file like xlog.c in a directory structure like
> backend_access_transam instead of backend/access/transam. IMHO, having
> a hierarchy makes it simpler to reason about related code bits.
>
> The SQL spec does have a concept of modules that help address this. It's
> defined as a persistent object within a schema that contains one or more
> routines. It also defines other things like local temporary tables and
> path specifications. There are other databases like DB2 that have
> implemented module support each with their own way of defining the
> routines within the module. The spec doesn't really give guidance on
> how to manipulate the objects within the module.
>
> Attached is a POC patch for modules. I modeled it as a sub-schema because
> that is more what it seems like to me. It adds additional columns to
> pg_namespace and allows for 3-part (or 4 with the database name) naming
> of objects within the module. This simple example works with the patch.
>
> CREATE SCHEMA foo;
> CREATE MODULE foo.bar
> CREATE FUNCTION hello() RETURNS text
> LANGUAGE sql
> RETURN 'hello'
> CREATE FUNCTION world() RETURNS text
> LANGUAGE sql
> RETURN 'world';
> SELECT foo.bar.hello();
>
> Questions
> - Do we want to add module support?
>
> - If we do, should it be implemented as a type of namespace or should it
> be its own object type that lives in something like pg_module?
>
> - How should users interact with objects within a module? They could be
> mostly independent like the current POC or we can introduce a path like
> ALTER MODULE foo ADD FUNCTION blah
>

I never liked the SQL/PSM concept of modules. The possibility to assign
database objects to schema or to modules looks like schizophrenia.

There are only two advantages of modules - a) possibility to define private
objects, b) local scope - the objects from modules shadows external objects
without dependency of search_path.

But both these features are pretty hard to implement in PL/pgSQL - where
expression executor is SQL executor.

Without these features I don't see strong benefits for modules.

Regards

Pavel

>
> --Jim
>