Module Manager

NOTE: as of year 2020, this is obsoleted by current extension packaging system and the CREATE EXTENSION command.
----
There has been some interest PostgreSQL extension/package/module manager.
We would like some software to make it easier for users and developers to install and manage
various add-on features available for PostgreSQL.
Currently the process of installing add-ons is rather manual and cumbersome. 
It usually requires command-line interface and/or superuser access.
It would be great, if modules could be used through SQL alone.
Recently, this topic has reappeared on pgsql-hackers mailing list:
* 2008-04 pgsql-hackers thread
* 2007-10 pgsql-hackers thread
* 2006-05 pgsql-hackers thread
* Database owner installable modules patch by Tom Dunstan
* PostgreSQL extensions packaging proposal by Dimitri Fontaine
Perhaps a difference among terms extension, package and module should be clarified?
Here the term module is used, due to popular demand.
==Synopsis==
===As a database user===
====Using modules====
Import desired module with contrib.import() function.
ljudmila=> select contrib.import('hstore');
... stuff ...
 import 
---------------
 INSTALL hstore
(1 row)
ljudmila=> select 'a=>3'::hstore;
 hstore 
----------
 "a"=>"3"
(1 row)
When user calls import() for the first time, module is installed into current schema.
===As a database administrator===
====Install the module manager and contrib modules====
By default, this goes into schema contrib.
postgres@mama:$ pg_module all >pg_module.sql
postgres@mama:$ psql
ljudmila=# \i pg_module.sql
BEGIN
... stuff ...
COMMIT
ljudmila=# set search_path=contrib; \d+
SET
 List of relations
 Schema | Name | Type | Owner | Description 
---------+--------------------+-------+------------+-----------------------------------------
 contrib | pg_module | table | pg_modules | modules, which can imported or deported
 contrib | pg_module_contents | table | pg_modules | module contents
 contrib | pg_module_usage | view | pg_modules | modules imported into schemas
(3 rows)
====List available modules====
ljudmila=# select * from modules.pg_module;
 modname | modimport | moddeport 
---------------------+---------------------------------------+---------------------------------
 pg_buffercache | module_pg_buffercache_import() | module_pg_buffercache_deport()
 pgrowlocks | module_pgrowlocks_import() | module_pgrowlocks_deport()
 tsearch2 | module_tsearch2_import() | module_tsearch2_deport()
 moddatetime | module_moddatetime_import() | 
 pgcrypto | module_pgcrypto_import() | module_pgcrypto_deport()
 lo | module_lo_import() | module_lo_deport()
 seg | module_seg_import() | module_seg_deport()
 pgxml | module_pgxml_import() | module_pgxml_deport()
 test_parser | module_test_parser_import() | module_test_parser_deport()
 tablefunc | module_tablefunc_import() | module_tablefunc_deport()
 cube | module_cube_import() | module_cube_deport()
 timetravel | module_timetravel_import() | 
 chkpass | module_chkpass_import() | module_chkpass_deport()
 _int | module__int_import() | module__int_deport()
 refint | module_refint_import() | 
 int_aggregate | module_int_aggregate_import() | module_int_aggregate_deport()
 pgstattuple | module_pgstattuple_import() | module_pgstattuple_deport()
 fuzzystrmatch | module_fuzzystrmatch_import() | module_fuzzystrmatch_deport()
 pg_freespacemap | module_pg_freespacemap_import() | module_pg_freespacemap_deport()
 dblink | module_dblink_import() | module_dblink_deport()
 isn | module_isn_import() | module_isn_deport()
 pageinspect | module_pageinspect_import() | module_pageinspect_deport()
 insert_username | module_insert_username_import() | 
 adminpack | module_adminpack_import() | module_adminpack_deport()
 ltree | module_ltree_import() | module_ltree_deport()
 dict_int | module_dict_int_import() | module_dict_int_deport()
 autoinc | module_autoinc_import() | 
 btree_gist | module_btree_gist_import() | module_btree_gist_deport()
 dict_xsyn | module_dict_xsyn_import() | module_dict_xsyn_deport()
 pg_trgm | module_pg_trgm_import() | module_pg_trgm_deport()
 hstore | module_hstore_import() | module_hstore_deport()
 earthdistance | module_earthdistance_import() | module_earthdistance_deport()
 sslinfo | module_sslinfo_import() | module_sslinfo_deport()
(35 rows)
====List module contents====
ljudmila=# select * from modules.pg_module_contents where modname='tablefunc';
 modname | sql_type | sql_identifier | objid | objsubid 
-----------+----------+--------------------------------------------------------+---------+----------
 tablefunc | FUNCTION | normal_rand(integer,double precision,double precision) | 6542867 | 0
 tablefunc | FUNCTION | crosstab(text) | 6542868 | 0
 tablefunc | TYPE | tablefunc_crosstab_2 | 6542871 | 0
 tablefunc | TYPE | tablefunc_crosstab_3 | 6542874 | 0
 tablefunc | TYPE | tablefunc_crosstab_4 | 6542877 | 0
 tablefunc | FUNCTION | crosstab2(text) | 6542878 | 0
 tablefunc | FUNCTION | crosstab3(text) | 6542879 | 0
 tablefunc | FUNCTION | crosstab4(text) | 6542880 | 0
 tablefunc | FUNCTION | crosstab(text,integer) | 6542881 | 0
 tablefunc | FUNCTION | crosstab(text,text) | 6542882 | 0
 tablefunc | FUNCTION | connectby(text,text,text,text,integer,text) | 6542883 | 0
 tablefunc | FUNCTION | connectby(text,text,text,text,integer) | 6542884 | 0
 tablefunc | FUNCTION | connectby(text,text,text,text,text,integer,text) | 6542885 | 0
 tablefunc | FUNCTION | connectby(text,text,text,text,text,integer) | 6542886 | 0
(14 rows)
====Show module usage by schema====
ljudmila=# select * from modules.pg_module_usage;
 schema_name | module_name | module_order 
-------------+-------------+--------------
(0 rows)
====Grant module usage privilege to user====
ljudmila=# grant usage on schema modules to atom;
GRANT
==Elaboration==
===Use Cases===
* Installing stuff from contrib
* Installing stuff from [Pgfoundry]
* Installing user's own stuff
* Convincing ISPs that modules are cool and can be easily installed and used safely
===Wishes/Requirements===
✓ = done,
❤ = work in progress
====✓ enable regular users to install modules====
Done through use of SECURITY DEFINER functions. 
Basically, module import/deport functions run as superuser and can do anything.
USAGE privilege on module manager schema must be granted to roles to enable them to use modules.
====install modules by name (and optionally version)====
====❤ command line interface, in spirit of apt-get or CPAN====
====✓ transactional installation (module either gets installed ok or not at all)====
====✓ you should be able to tell what is installed====
====✓ should manage current contrib stuff too====
====support multiple versions of databases and extensions====
====create and drop (and alter) extensions in user-specified databases/schemas====
====auto-get and compile and install extensions from sources from internet====
====module sources: contrib, PgFoundry, user====
====dependencies management (install dependencies as well)====
====uninstall should use dependancies, not hand-written SQL scripts====
====support for/integration with pg_dump/pg_restore; should dump import() statements insted of objects====
==Construction==
===Current status===
There is a prototype implementation of user level module manager in PL/PgSQL in form of SQL schema.
The name of the schema defaults to modules, but can be configured at module manager installation time.
There is a prototype implementation of command-line tool in Perl, which help with module creation.
This is not needed by normal SQL users.
===Concepts===
A module is a collection of SQL objects, 
which can be created or dropped at once.
Module manager is a tool, which is specific to PostgreSQL, 
not a specific operating system. It should handle all tasks
related to module management
There are two aspect to module manager:
# user level access for normal database users: functions import(modulename), install(modulename) and uninstall(modulename)
# admin level access for database administrators: functions create_module(), drop_module() and analyze_module()
Modules are installed on local system (eg Debian Linux), per
specific PostgreSQL version.
Modules are installed each in it's own directory, like:
 /usr/share/posteresql/8.3/modules/tablefunc-8.3.0/
path structure:
 $base/postgresql/pg_version/modules/modulename-moduleversion/
where:
* pg_version - postgresql version number (so multiple installations are supported)
* modulename - unique module name, (ex. hstore, tablefunc, newsysviews)
* moduleversion - debian-like version numbers. For contrib, this should probably match PostgreSQL version.
module directory should contain at least:
* SQL install instructions (file install.sql)
* SQL uninstall instructions (file uninstall.sql)
* Module information (file module.xml) - contents of this should be elaborated
This would make it easier for utilities to enumerate installed modules.
Once installed, extensions can be:
* loaded (installed in particular database/schema)
* unloaded (removed from particular database/schema)
* upgraded (to a new version)
Perhaps words CREATE, DROP and ALTER should be used?
===Current contrib problems===
* everything in one directory; it is clumsy to match install and uninstall files and even see, what is available
* no module versions specified (or any metadata for that matter)
* many files explicitly set schema to public:
** this is the default anyway
** it requires user to edit SQL file to change this
** this should be set by module manager, if it is to support module installation in any schema
Recommendations: 
* Move everything from contrib to appropriate dirs/files in modules directory
* Add missing module.xml files
* Make symlinks from contrib
===See Also===
* User:Ziga/xpg_package - somewhat older document of a similar nature
Category:Development
Category:Historical information