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