Extensions
From PostgreSQL wiki
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 important now:
dfontaine@dfontaine-laptop:~/PostgreSQL/postgresql-extension$ 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
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;
