Re: Support for CREATE MODULE? - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: Support for CREATE MODULE?
Date
Msg-id CAFj8pRCwacB5UFfZWrQi5K=kbb=T-urjCUSk1GPZQM8J0iQ1Ug@mail.gmail.com
Whole thread Raw
In response to Support for CREATE MODULE?  (Jim Mlodgenski <jimmy76@gmail.com>)
List pgsql-hackers
Hi

st 2. 6. 2021 v 15:39 odesílatel Jim Mlodgenski <jimmy76@gmail.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

pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: Are we missing (void) when return value of fsm_set_and_search is ignored?
Next
From: David Christensen
Date:
Subject: Re: DELETE CASCADE