Module Manager

This page contains historical information or deprecated articles.

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:

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