Module Manager

From PostgreSQL wiki

Revision as of 14:31, 17 October 2008 by Ziga (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

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

Simply import desired module with modules.import() function.

ljudmila=> select modules.import('hstore');
... stuff ...
    import     
---------------
 IMPORT DEFINE hstore
(1 row)

ljudmila=> select 'a=>3'::hstore;
  hstore  
----------
 "a"=>"3"
(1 row)

If module is already imported, IMPORT is returned instead of IMPORT DEFINE.

As a database administrator

Install the module manager and contrib modules

By default, this goes into schema modules.

postgres@mama:$ pg_module all >pg_module.sql
postgres@mama:$ psql
ljudmila=# \i pg_module.sql
BEGIN
... stuff ...
COMMIT
ljudmila=# set search_path=modules; \d+
SET
                                      List of relations
 Schema  |        Name        | Type  |   Owner    |               Description               
---------+--------------------+-------+------------+-----------------------------------------
 modules | pg_module          | table | pg_modules | modules, which can imported or deported
 modules | pg_module_contents | table | pg_modules | module contents
 modules | 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

✓ enable regular users to install 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

✓ = done, ❤ = work in progress

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 droped 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) and deport(modulename)
admin level access for database administrators: functions create_module(), drop_module() and analyze_module()

Mo

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