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:
- 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
