Extensions

From PostgreSQL wiki

Revision as of 13:59, 15 November 2010 by Dim (Talk | contribs)

Jump to: navigation, search

Contents

Extensions

A series of patches implements extensions support for dump and restore for PostgreSQL. In order to reach this goal, several things need to happen, and the patch is quite large by now. This is the diffstat of the cumulative branch:

$ git diff master..|diffstat
contrib/adminpack/Makefile                               |    3 
contrib/adminpack/adminpack.control.in                   |    3 
contrib/btree_gin/Makefile                               |    3 
contrib/btree_gin/btree_gin.control.in                   |    3 
contrib/btree_gin/btree_gin.sql.in                       |    2 
contrib/btree_gin/expected/install_btree_gin.out         |    2 
contrib/btree_gin/sql/install_btree_gin.sql              |    4 
contrib/btree_gist/Makefile                              |    3 
contrib/btree_gist/btree_gist.control.in                 |    3 
contrib/btree_gist/btree_gist.sql.in                     |    2 
contrib/btree_gist/expected/init.out                     |    2 
contrib/btree_gist/sql/init.sql                          |    4 
contrib/chkpass/Makefile                                 |    3 
contrib/chkpass/chkpass.control.in                       |    3 
contrib/chkpass/chkpass.sql.in                           |    2 
contrib/citext/Makefile                                  |    3 
contrib/citext/citext.control.in                         |    3 
contrib/citext/citext.sql.in                             |    2 
contrib/citext/expected/citext.out                       |    3 
contrib/citext/expected/citext_1.out                     |    3 
contrib/citext/sql/citext.sql                            |    4 
contrib/cube/Makefile                                    |    3 
contrib/cube/cube.control.in                             |    3 
contrib/cube/cube.sql.in                                 |    2 
contrib/cube/expected/cube.out                           |    2 
contrib/cube/expected/cube_1.out                         |    2 
contrib/cube/expected/cube_2.out                         |    2 
contrib/cube/sql/cube.sql                                |    4 
contrib/dblink/Makefile                                  |    2 
contrib/dblink/dblink.control.in                         |    3 
contrib/dblink/dblink.sql.in                             |    2 
contrib/dblink/expected/dblink.out                       |    2 
contrib/dblink/sql/dblink.sql                            |    4 
contrib/dict_int/Makefile                                |    3 
contrib/dict_int/dict_int.control.in                     |    3 
contrib/dict_int/dict_int.sql.in                         |    2 
contrib/dict_int/expected/dict_int.out                   |    2 
contrib/dict_int/sql/dict_int.sql                        |    4 
contrib/dict_xsyn/Makefile                               |    3 
contrib/dict_xsyn/dict_xsyn.control.in                   |    3 
contrib/dict_xsyn/dict_xsyn.sql.in                       |    2 
contrib/dict_xsyn/expected/dict_xsyn.out                 |    2 
contrib/dict_xsyn/sql/dict_xsyn.sql                      |    4 
contrib/earthdistance/Makefile                           |    3 
contrib/earthdistance/earthdistance.control.in           |    3 
contrib/earthdistance/earthdistance.sql.in               |    2 
contrib/earthdistance/expected/earthdistance.out         |    3 
contrib/earthdistance/sql/earthdistance.sql              |    6 
contrib/fuzzystrmatch/Makefile                           |    3 
contrib/fuzzystrmatch/fuzzystrmatch.control.in           |    3 
contrib/fuzzystrmatch/fuzzystrmatch.sql.in               |    2 
contrib/hstore/Makefile                                  |   17 +
contrib/hstore/expected/hstore.out                       |    6 
contrib/hstore/hstore.control.in                         |    3 
contrib/hstore/hstore.sql.in                             |    2 
contrib/hstore/sql/hstore.sql                            |    6 
contrib/intagg/Makefile                                  |    3 
contrib/intagg/int_aggregate.control.in                  |    3 
contrib/intarray/Makefile                                |    3 
contrib/intarray/_int.sql.in                             |    2 
contrib/intarray/expected/_int.out                       |    2 
contrib/intarray/intarray.control.in                     |    5 
contrib/intarray/sql/_int.sql                            |    4 
contrib/isn/Makefile                                     |    3 
contrib/isn/isn.control.in                               |    3 
contrib/isn/isn.sql.in                                   |    2 
contrib/lo/Makefile                                      |    3 
contrib/lo/lo.control.in                                 |    3 
contrib/lo/lo.sql.in                                     |    2 
contrib/ltree/Makefile                                   |    3 
contrib/ltree/expected/ltree.out                         |    2 
contrib/ltree/ltree.control.in                           |    3 
contrib/ltree/ltree.sql.in                               |    2 
contrib/ltree/sql/ltree.sql                              |    4 
contrib/pageinspect/Makefile                             |    3 
contrib/pageinspect/pageinspect.control.in               |    3 
contrib/pageinspect/pageinspect.sql.in                   |    2 
contrib/pg_buffercache/Makefile                          |    3 
contrib/pg_buffercache/pg_buffercache.control.in         |    3 
contrib/pg_buffercache/pg_buffercache.sql.in             |    2 
contrib/pg_freespacemap/Makefile                         |    3 
contrib/pg_freespacemap/pg_freespacemap.control.in       |    3 
contrib/pg_freespacemap/pg_freespacemap.sql.in           |    2 
contrib/pg_stat_statements/Makefile                      |    3 
contrib/pg_stat_statements/pg_stat_statements.control.in |    8 
contrib/pg_stat_statements/pg_stat_statements.sql.in     |    2 
contrib/pg_trgm/Makefile                                 |    3 
contrib/pg_trgm/expected/pg_trgm.out                     |    2 
contrib/pg_trgm/pg_trgm.control.in                       |    3 
contrib/pg_trgm/pg_trgm.sql.in                           |    2 
contrib/pg_trgm/sql/pg_trgm.sql                          |    4 
contrib/pgcrypto/Makefile                                |    2 
contrib/pgcrypto/expected/init.out                       |    2 
contrib/pgcrypto/pgcrypto.control.in                     |    3 
contrib/pgcrypto/pgcrypto.sql.in                         |    2 
contrib/pgcrypto/sql/init.sql                            |    4 
contrib/pgrowlocks/Makefile                              |    3 
contrib/pgrowlocks/pgrowlocks.control.in                 |    3 
contrib/pgrowlocks/pgrowlocks.sql.in                     |    2 
contrib/pgstattuple/Makefile                             |    3 
contrib/pgstattuple/pgstattuple.control.in               |    3 
contrib/pgstattuple/pgstattuple.sql.in                   |    2 
contrib/seg/Makefile                                     |    3 
contrib/seg/expected/seg.out                             |    2 
contrib/seg/expected/seg_1.out                           |    2 
contrib/seg/seg.control.in                               |    3 
contrib/seg/seg.sql.in                                   |    2 
contrib/seg/sql/seg.sql                                  |    4 
contrib/spi/Makefile                                     |    4 
contrib/spi/auto_username.control.in                     |    4 
contrib/spi/autoinc.control.in                           |    3 
contrib/spi/autoinc.sql.in                               |    2 
contrib/spi/insert_username.sql.in                       |    2 
contrib/spi/moddatetime.control.in                       |    3 
contrib/spi/moddatetime.sql.in                           |    2 
contrib/spi/refint.control.in                            |    3 
contrib/spi/refint.sql.in                                |    2 
contrib/spi/timetravel.control.in                        |    3 
contrib/spi/timetravel.sql.in                            |    2 
contrib/sslinfo/Makefile                                 |    3 
contrib/sslinfo/sslinfo.control.in                       |    3 
contrib/sslinfo/sslinfo.sql.in                           |    2 
contrib/tablefunc/Makefile                               |    3 
contrib/tablefunc/expected/tablefunc.out                 |    2 
contrib/tablefunc/sql/tablefunc.sql                      |    4 
contrib/tablefunc/tablefunc.control.in                   |    3 
contrib/tablefunc/tablefunc.sql.in                       |    2 
contrib/test_parser/Makefile                             |    3 
contrib/test_parser/expected/test_parser.out             |    2 
contrib/test_parser/sql/test_parser.sql                  |    4 
contrib/test_parser/test_parser.control.in               |    3 
contrib/test_parser/test_parser.sql.in                   |    2 
contrib/tsearch2/Makefile                                |    3 
contrib/tsearch2/expected/tsearch2.out                   |    2 
contrib/tsearch2/expected/tsearch2_1.out                 |    2 
contrib/tsearch2/sql/tsearch2.sql                        |    4 
contrib/tsearch2/tsearch2.control.in                     |    3 
contrib/tsearch2/tsearch2.sql.in                         |    2 
contrib/unaccent/Makefile                                |    3 
contrib/unaccent/expected/unaccent.out                   |    2 
contrib/unaccent/sql/unaccent.sql                        |    4 
contrib/unaccent/unaccent.control.in                     |    3 
contrib/unaccent/unaccent.sql.in                         |    2 
contrib/uuid-ossp/Makefile                               |    3 
contrib/uuid-ossp/uuid-ossp.control.in                   |    3 
contrib/uuid-ossp/uuid-ossp.sql.in                       |    2 
contrib/xml2/Makefile                                    |    3 
contrib/xml2/expected/xml2.out                           |    2 
contrib/xml2/expected/xml2_1.out                         |    2 
contrib/xml2/pgxml.sql.in                                |    2 
contrib/xml2/sql/xml2.sql                                |    4 
contrib/xml2/xml2.control.in                             |    4 
doc/src/sgml/catalogs.sgml                               |  131 ++++++++++++++
doc/src/sgml/extend.sgml                                 |  118 ++++++++++++
doc/src/sgml/func.sgml                                   |  123 +++++++++++++
doc/src/sgml/ref/allfiles.sgml                           |    2 
doc/src/sgml/ref/alter_conversion.sgml                   |   10 +
doc/src/sgml/ref/alter_opclass.sgml                      |   10 +
doc/src/sgml/ref/alter_operator.sgml                     |   10 +
doc/src/sgml/ref/alter_opfamily.sgml                     |   10 +
doc/src/sgml/ref/alter_tsconfig.sgml                     |   10 +
doc/src/sgml/ref/alter_tsdictionary.sgml                 |   10 +
doc/src/sgml/ref/alter_tsparser.sgml                     |   10 +
doc/src/sgml/ref/alter_tstemplate.sgml                   |   10 +
doc/src/sgml/ref/create_extension.sgml                   |  144 +++++++++++++++
doc/src/sgml/ref/drop_extension.sgml                     |  115 ++++++++++++
doc/src/sgml/reference.sgml                              |    2 
doc/src/sgml/xfunc.sgml                                  |   30 +++
src/backend/access/transam/xlog.c                        |   97 ----------
src/backend/catalog/Makefile                             |    1 
src/backend/catalog/dependency.c                         |  140 +++++++++++----
src/backend/catalog/heap.c                               |   23 +-
src/backend/catalog/namespace.c                          |   27 ++
src/backend/catalog/objectaddress.c                      |   14 +
src/backend/catalog/pg_aggregate.c                       |    7 
src/backend/catalog/pg_conversion.c                      |    7 
src/backend/catalog/pg_depend.c                          |  109 +++++++++++
src/backend/catalog/pg_namespace.c                       |   13 +
src/backend/catalog/pg_operator.c                        |    7 
src/backend/catalog/pg_proc.c                            |    7 
src/backend/catalog/pg_type.c                            |    8 
src/backend/catalog/system_views.sql                     |    3 
src/backend/commands/Makefile                            |    3 
src/backend/commands/alter.c                             |  226 ++++++++++++++++++++++++
src/backend/commands/comment.c                           |    6 
src/backend/commands/conversioncmds.c                    |   50 +++++
src/backend/commands/extension.c                         | 1322 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
src/backend/commands/foreigncmds.c                       |   19 ++
src/backend/commands/functioncmds.c                      |   39 ++--
src/backend/commands/opclasscmds.c                       |  170 ++++++++++++++++++
src/backend/commands/operatorcmds.c                      |   51 +++++
src/backend/commands/proclang.c                          |    7 
src/backend/commands/tsearchcmds.c                       |  194 ++++++++++++++++++++
src/backend/commands/typecmds.c                          |   17 +
src/backend/nodes/copyfuncs.c                            |   29 +++
src/backend/nodes/equalfuncs.c                           |   25 ++
src/backend/parser/gram.y                                |  169 +++++++++++++++++-
src/backend/rewrite/rewriteDefine.c                      |    7 
src/backend/tcop/utility.c                               |   42 ++++
src/backend/utils/adt/genfile.c                          |  130 +++++++++++++-
src/backend/utils/init/postinit.c                        |    4 
src/backend/utils/misc/Makefile                          |    2 
src/backend/utils/misc/cfparser.c                        |  115 ++++++++++++
src/backend/utils/misc/guc-file.l                        |   25 +-
src/backend/utils/misc/guc.c                             |  160 +++++++++++++++--
src/bin/pg_dump/common.c                                 |    6 
src/bin/pg_dump/pg_dump.c                                |  543 +++++++++++++++++++++++++++++++++++++++++++++++++++++-----
src/bin/pg_dump/pg_dump.h                                |   11 +
src/bin/pg_dump/pg_dump_sort.c                           |    7 
src/bin/psql/command.c                                   |    3 
src/bin/psql/describe.c                                  |   50 +++++
src/bin/psql/describe.h                                  |    3 
src/bin/psql/help.c                                      |    1 
src/include/catalog/dependency.h                         |   40 ++++
src/include/catalog/indexing.h                           |    6 
src/include/catalog/namespace.h                          |    2 
src/include/catalog/pg_extension.h                       |   61 ++++++
src/include/catalog/pg_proc.h                            |   21 +-
src/include/catalog/toasting.h                           |    1 
src/include/commands/alter.h                             |    9 
src/include/commands/conversioncmds.h                    |    3 
src/include/commands/defrem.h                            |   15 +
src/include/commands/extension.h                         |   70 +++++++
src/include/commands/typecmds.h                          |    1 
src/include/nodes/nodes.h                                |    2 
src/include/nodes/parsenodes.h                           |   21 ++
src/include/parser/kwlist.h                              |    1 
src/include/utils/builtins.h                             |    7 
src/include/utils/cfparser.h                             |   18 +
src/include/utils/genfile.h                              |   22 ++
src/include/utils/guc.h                                  |   11 +
src/makefiles/pgxs.mk                                    |   23 ++
232 files changed, 5042 insertions(+), 383 deletions(-)

Overview

The extension patch has to be considered with the dump and restore support in mind. About all that is done is to that end, there is very few extra in there. The goal is for pg_dump not to output the extension's install script, that is maintained separately from the database objects. That allows for easier restores too, when the extension needs to be upgraded. What we want to see in the dump is:

 CREATE EXTENSION citext WITH SCHEMA foo NO USER DATA;

For schema support, see the discussion above.

Some extension will install tables where users will be able to edit the data, or use commands and functions that will change the data for them. The line of though here is that the data will change and that we want to have them as part of the dump, because the maintenance of those data is not done in the extension's SCM but in the database.

User Interface

psql

dfontaine=# \dx
                                                List of extensions
 Schema |        Name        | Version  |                               Description                               
--------+--------------------+----------+-------------------------------------------------------------------------
 utils  | adminpack          | 9.1devel | Administrative functions for PostgreSQL
 utils  | seg                | 9.1devel | data type for representing line segments, or floating point intervals
 utils  | refint             | 9.1devel | functions for implementing referential integrity
 utils  | hstore             | 9.1devel | storing sets of key/value pairs
 utils  | pg_freespacemap    | 9.1devel | examine the free space map (FSM)
 utils  | pgstattuple        | 9.1devel | obtain tuple-level statistics
 utils  | citext             | 9.1devel | case-insensitive character string type
 utils  | dict_int           | 9.1devel | example of an add-on dictionary template for full-text search
 utils  | pg_trgm            | 9.1devel | determine the similarity of text, with indexing support
 utils  | pageinspect        | 9.1devel | inspect the contents of database pages at a low level
 utils  | pgcrypto           | 9.1devel | cryptographic functions
 utils  | intarray           | 9.1devel | one-dimensional arrays of integers: functions, operators, index support
 utils  | test_parser        | 9.1devel | example of a custom parser for full-text search
 utils  | pg_stat_statements | 9.1devel | tracking execution statistics of all SQL statements executed
 utils  | int_aggregate      | 9.1devel | integer aggregator and an enumerator (obsolete)
 utils  | tablefunc          | 9.1devel | various functions that return tables, including crosstab(text sql)
 utils  | chkpass            | 9.1devel | Store crypt()ed passwords
 utils  | lo                 | 9.1devel | managing Large Objects
 utils  | dblink             | 9.1devel | connect to other PostgreSQL databases from within a database
 utils  | timetravel         | 9.1devel | functions for implementing time travel
 utils  | pgrowlocks         | 9.1devel | show row locking information for a specified table
 utils  | pg_buffercache     | 9.1devel | examine the shared buffer cache in real time
 utils  | btree_gin          | 9.1devel | GIN support for common types BTree operators
 utils  | moddatetime        | 9.1devel | functions for tracking last modification time
 utils  | unaccent           | 9.1devel | text search dictionary that removes accents
 utils  | cube               | 9.1devel | data type for representing multidimensional cubes
 utils  | dict_xsyn          | 9.1devel | example of an add-on dictionary template for full-text search
 utils  | ltree              | 9.1devel | data type for hierarchical tree-like structure
 utils  | btree_gist         | 9.1devel | GiST support for common types BTree operators
 utils  | earthdistance      | 9.1devel | calculating great circle distances on the surface of the Earth
 utils  | autoinc            | 9.1devel | functions for autoincrementing fields
 utils  | tsearch2           | 9.1devel | backwards-compatible text search functionality (pre-8.3)
 utils  | auto_username      | 9.1devel | functions for tracking who changed a table
 utils  | fuzzystrmatch      | 9.1devel | determine similarities and distance between strings
 utils  | isn                | 9.1devel | data types for the international product numbering standards
(35 rows)
dfontaine=# select * from pg_extension_objects('unaccent');
     class      | classid | objid |                               objdesc                               
----------------+---------+-------+---------------------------------------------------------------------
 pg_extension   |    3996 | 18009 | extension unaccent
 pg_proc        |    1255 | 18010 | function utils.unaccent(regdictionary,text)
 pg_proc        |    1255 | 18011 | function utils.unaccent(text)
 pg_proc        |    1255 | 18012 | function utils.unaccent_init(internal)
 pg_proc        |    1255 | 18013 | function utils.unaccent_lexize(internal,internal,internal,internal)
 pg_ts_template |    3764 | 18014 | text search template unaccent
 pg_ts_dict     |    3600 | 18015 | text search dictionary unaccent
(7 rows)

catalog, system view, functions

dfontaine=# \d pg_extension
 Table "pg_catalog.pg_extension"
    Column    | Type | Modifiers 
--------------+------+-----------
 extname      | name | not null
 extversion   | text | 
 custom_class | text | 
Indexes:
    "pg_extension_name_index" UNIQUE, btree (extname)
    "pg_extension_oid_index" UNIQUE, btree (oid)
dfontaine=# \d pg_extensions
        View "pg_catalog.pg_extensions"
         Column          |  Type   | Modifiers 
-------------------------+---------+-----------
 name                    | name    | 
 version                 | text    | 
 custom_variable_classes | text    | 
 comment                 | text    |  
 installed               | boolean | 
dfontaine=# \df pg_ext*
                                                                                 List of functions
   Schema   |            Name             | Result data type |                                            Argument data types                                             |  Type  
------------+-----------------------------+------------------+------------------------------------------------------------------------------------------------------------+--------
 pg_catalog | pg_extension_flag_dump      | boolean          | oid                                                                                                        | normal
 pg_catalog | pg_extension_objects        | SETOF record     | name name, OUT class regclass, OUT classid oid, OUT objid oid, OUT objdesc text                            | normal
 pg_catalog | pg_extension_with_user_data | boolean          |                                                                                                            | normal
 pg_catalog | pg_extensions               | SETOF record     | OUT name name, OUT version text, OUT custom_variable_classes text, OUT comment text, OUT installed boolean | normal
(4 rows)

commands

dfontaine=# \h create extension
Command:     CREATE EXTENSION
Description: define and install a new extension
Syntax:
CREATE EXTENSION extension_name
    [ [ WITH ] [ [ NO ] USER DATA ]
           [ SCHEMA [=] schema ]
           [ ENCODING [=] encoding ]
dfontaine=# \h drop extension                                               
Command:     DROP EXTENSION
Description: remove a tablespace
Syntax:
DROP EXTENSION [ IF EXISTS ] extension_name [ CASCADE | RESTRICT ]

Also

ALTER EXTENSION extension_name SET SCHEMA schema_name;

user data and extensions

schema and extensions

pg_dump support

Authoring Extension

Contrib changes

The control file

Allows to register an extension into the catalogs. Minimal meta-data and syntax support (uses recovery.conf parser), fixed expected location alongside the SQL script, pgxs support

$ cat contrib/citext/citext.control.in 
# citext
comment = 'case-insensitive character string type'
version = 'EXTVERSION'
$ grep EXT contrib/citext/Makefile
EXTENSION = $(MODULES)
EXTVERSION = $(VERSION)

Some more involved examples:

$ cat contrib/pg_stat_statements/pg_stat_statements.control.in 
# pg_stat_statements
comment = 'tracking execution statistics of all SQL statements executed'
version = 'EXTVERSION'
custom_variable_classes = 'pg_stat_statements'
pg_stat_statements.max = '1000'
pg_stat_statements.track = 'top'
pg_stat_statements.track_utility = 'true'
pg_stat_statements.save = 'true'
$ cat contrib/intarray/intarray.control.in 
# intarray
comment = 'one-dimensional arrays of integers: functions, operators, index support'
version = 'EXTVERSION'
script = '_int.sql'

custom_variable_classes

An extension author facility.

Code design and patch series

tracking extension objects

ObjectAddress CreateExtensionAddress;
bool create_extension = false;
bool create_extension_with_user_data = true;

cfparser

pg_execute_from_files()

placeholders

extension, the main patch

set_schema

Support patch for being able to move all extension's object into another schema easily.

  /*
+  * Generic function to change the namespace of a given object, for simple
+  * cases (won't work for tables or functions, objects which have more than 2
+  * key-attributes to use when searching for their syscache entries --- we
+  * don't want nor need to get this generic here).
+  *
+  * The AlterFooNamespace() calls just above will call a function whose job
+  * is to lookup the arguments for the generic function here.
+  *
+  * Relation must already by open, it's the responsibility of the caller to
+  * close it.
+  */
+ void
+ AlterObjectNamespace(Relation rel, int cacheId,
+ 					 Oid classId, Oid objid, Oid nspOid,
+ 					 int Anum_name, int Anum_namespace, int Anum_owner,
+ 					 AclObjectKind acl_kind,
+ 					 bool superuser_only)

So we have this supporting:

+ ALTER CONVERSION <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER OPERATOR CLASS <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</replaceable> | NONE } , { <replaceable>right_type</replaceable> | NONE } ) SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER TEXT SEARCH CONFIGURATION <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER TEXT SEARCH DICTIONARY <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER TEXT SEARCH PARSER <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>
+ ALTER TEXT SEARCH TEMPLATE <replaceable>name</replaceable> SET SCHEMA <replaceable>new_schema</replaceable>

alter extension

Starting from the previous support, we now are able to have ALTER EXTENSION citext SET SCHEMA utils;

Personal tools