Module Manager

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 18: Line 18:
 
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?
 
Perhaps a difference among terms ''extension'', ''package'' and ''module'' should be clarified?
 
Here the term ''module'' is used, due to popular demand.
 
Here the term ''module'' is used, due to popular demand.
 
A ''module'' is a collection of SQL objects.
 
 
  
 
==Synopsis==
 
==Synopsis==
Line 152: Line 149:
 
GRANT
 
GRANT
 
</pre>
 
</pre>
 
  
 
==Elaboration==
 
==Elaboration==
 +
 
===Use Cases===
 
===Use Cases===
 
* Installing stuff from ''contrib''
 
* Installing stuff from ''contrib''
Line 180: Line 177:
  
 
==Construction==
 
==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===
 
===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,  
 
''Module manager'' is a tool, which is specific to PostgreSQL,  
 
not a specific operating system. It should handle all tasks
 
not a specific operating system. It should handle all tasks
 
related to module management.
 
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
 
Modules are installed on local system (eg Debian Linux), per

Revision as of 14:31, 17 October 2008

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.

Contents

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:

  1. user level access for normal database users: functions import(modulename) and deport(modulename)
  2. 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

See Also

Personal tools