Extensions
Extensions
Extensions were implemented in PostgreSQL 9.1 to allow for easier packaging of additions to PostgreSQL. Extensions can package user-visible functions or use hooks in the PostgreSQL to modify how the database does certain processes.
Wish List for Future Features from Extension Builders
This list was originally compiled during an unconference session at PgCon 2018. Please feel free to add additional requests and/or clarify these.
Versioning
- How to deal with multiple versions in different databases in the same PostgreSQL cluster?
- How to deal with unloading old versions on upgrade?
- Current system can't really unload due to the way hooks are executed using pointers to other extensions.
- Should the core code register hooks and manage their calling rather than relying on extensions being nice to one another and passing along hooks?
- How to deal with extension defined GUCs defined in previous versions during an upgrade?
Documentation/Debugging Improvements
- Currently hooks are relatively hard to find throughout the codebase (
ack _hookappears to be the way folks do that) how can we make it easier to find all hooks an extension might want to use and improve documentation around what they might be useful for/why they exist? - How can we give better error messages through PL/PGSQL at install/upgrade time? They are currently too terse to really understand easily what has failed.
Dependency Checking and Installation improvements
- Currently we load extensions in the order specified in the shared_preload_libraries variable, should we instead check dependencies and order them based on dependencies?
- Can we improve how dependencies for extensions are checked?
- How should this interact with versioning?
- Can we have some way of ordering extension versions either universally or extension defined so that dependencies can do >/</= type version checking?
- Or should extensions list their features so that we can instead depend on a feature of the extension rather than a version?
Installation/Uninstallation/Catalog Improvements
- Should we allow an install of an extension automatically add something to shared_preload_libraries rather than having to specify in the configuration file?
- Should there be a shared catalog for extensions and determining which versions are installed in which databases?
New Hooks?
- One major question to be considered: should we abandon the hook style integration of extensions for another model? What might that model look like?
- List of possible new hooks please add new ones as needed.
- Table file extension hook
Notes on Original Extensions Patchset from 9.1
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
There's both \dx to list installed extensions and \dx+ to list installed and available extensions.
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)
The function pg_extension_objects() is useful both for end-users and extension authors who will want to find their objects oid easily to manage upgrades, e.g.
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
The calalog has minimal information here. All extension related operations are super-user only, and we only need a name, a version string and a custom_variable_classes setting, as detailed later.
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)
The pg_extensions system view will also list available extensions, and offers an "installed" column to sort that out:
dfontaine=# \d pg_extensions
View "pg_catalog.pg_extensions"
Column | Type | Modifiers
-------------------------+---------+-----------
name | name |
version | text |
custom_variable_classes | text |
comment | text |
installed | boolean |
Some utility functions are available, intended for extension authoring. See later for details:
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
Basically, you can CREATE and DROP an extension, and also ALTER it.
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
The extension's patches goal is for pg_dump *NOT* to care about extension's objects, because they are maintained in some repository out there, not in the database. There's an exception here, when the extension installs tables (or anything, really) that the user can edit, directly or through some facilities given by the extension. Now we need to dump those as well.
Extension authors have a function they can call from their scripts to flag some objects are necessary to dump, and the documentation contains this example:
+ DO $$
+ BEGIN
+ IF pg_extension_with_user_data() THEN
+ create schema foo;
+ create table foo.bar(id serial primary key);
+ perform pg_extension_flag_dump('foo.bar_id_seq'::regclass);
+ perform pg_extension_flag_dump('foo.bar::regclass);
+ END IF;
+ END;
+ $$;
Such flags will make it so that pg_dump will dump the objects separately. The idea is that the dependency between the object and its extension is changed from DEPENDENCY_INTERNAL (no dump) to DEPENDENCY_NORMAL (dump).
schema and extensions
The schema support has been asked for on the -hackers list and is a simple search and replace of the @extschema@ placeholder in the extension's script, replacing it by the user entered schema in the CREATE EXTENSION command, which defaults to 'public'.
Another patch proposes the ALTER EXTENSION SET SCHEMA command: we are able to track all extension's object, so we are able to run the SET SCHEMA command on all of them. That's an optional sugar ish facility that is seen as a great value :)
pg_dump support
The pg_dump command has been patched so that it will not consider any object that has a direct DEPENDENDY_INTERNAL pg_depend entry to the extension's oid. That's about the only goal of all this work.
Of course we have some special cases to care about or it wouldn't be funny, such as the following:
! if (g_fout->remoteVersion >= 90100)
! {
! /*
! * So we want the namespaces, but we want to filter out any
! * namespace created by an extension's script. That's unless the
! * user went over his head and created objects into the extension's
! * schema: we now want the schema not to be filtered out to avoid:
! *
! * pg_dump: schema with OID 77869 does not exist
! */
! appendPQExpBuffer(query, "SELECT n.tableoid, n.oid, n.nspname, "
! "(%s nspowner) AS rolname, "
! "n.nspacl "
! " FROM pg_namespace n "
! " WHERE n.nspname != 'information_schema' "
! " AND CASE WHEN (SELECT count(*) "
! " FROM pg_depend "
! " WHERE refobjid = n.oid and deptype != 'p') > 0 "
! " THEN EXISTS( "
! "WITH RECURSIVE depends AS ( "
! " select n.oid as nsp, objid, refobjid, array[refobjid] as deps "
! " from pg_depend "
! " where refobjid = n.oid and deptype != 'p' "
! " UNION ALL "
! " select p.nsp, p.objid, d.refobjid, deps || d.refobjid "
! " from pg_depend d JOIN depends p ON d.objid = p.objid "
! " where d.deptype != 'p' and not d.refobjid = any(deps) "
! " and not (d.refclassid = 'pg_extension'::regclass and d.deptype = 'n') "
! ") "
! " SELECT nsp, objid, array_agg(distinct refobjid) "
! " FROM depends "
! "GROUP BY nsp, objid "
! " HAVING NOT array_agg(distinct refobjid) && array(select oid from pg_extension) "
! ") "
! " ELSE true "
! " END "
! "UNION ALL "
! "SELECT n.tableoid, n.oid, n.nspname, "
! "(%s nspowner) AS rolname, "
! "nspacl FROM pg_namespace n "
! "WHERE n.nspname = 'information_schema'",
! username_subquery,
! username_subquery);
! }
! else
! {
! appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
! "(%s nspowner) AS rolname, "
! "nspacl FROM pg_namespace",
! username_subquery);
! }
Usually it's a lot more simple than that, as in the operator families example:
! if (g_fout->remoteVersion >= 90100)
! {
! appendPQExpBuffer(query, "SELECT o.tableoid, o.oid, o.opfname, "
! "o.opfnamespace, "
! "(%s opfowner) AS rolname "
! "FROM pg_opfamily o "
! "LEFT JOIN pg_depend d ON d.objid = o.oid and d.deptype = 'i' "
! "and d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass "
! "LEFT JOIN pg_extension x ON d.refobjid = x.oid "
! "WHERE x.oid IS NULL",
! username_subquery);
! }
! else
! {
! appendPQExpBuffer(query, "SELECT tableoid, oid, opfname, "
! "opfnamespace, "
! "(%s opfowner) AS rolname "
! "FROM pg_opfamily",
! username_subquery);
! }
Using the DEPENDENCY_INTERNAL implementation makes it quite simple to achieve pg_dump support.
Authoring Extension
As an extension author, you have very few new things to do compared to what you needed to do before the patch. You have to provide the meta-data that will end-up into the pg_extension catalog, that's what the new .control file is for. There's support for that in the pgxs makefile, too. All those changes in contrib are just about that.
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. First, a script which name is different from the extension name, second, some more variables.
$ cat contrib/intarray/intarray.control.in # intarray comment = 'one-dimensional arrays of integers: functions, operators, index support' version = 'EXTVERSION' script = '_int.sql'
$ 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'
custom_variable_classes
This GUC is clearly an extension author facility. Users will be able to edit their postgresql.conf file and set there the individual settings for the custom class, but it's a bad idea to expose them the custom variable classes if we can have it managed automatically.
dfontaine=# create extension pg_stat_statements; NOTICE: Extension 'pg_stat_statements' SET custom_variable_classes TO 'pg_stat_statements,pg_stat_statements' NOTICE: Extension 'pg_stat_statements' SET pg_stat_statements.max TO '1000': please edit '/home/dfontaine/pgsql/exts/data/postgresql.conf'. NOTICE: Extension 'pg_stat_statements' SET pg_stat_statements.track TO 'top': please edit '/home/dfontaine/pgsql/exts/data/postgresql.conf'. NOTICE: Extension 'pg_stat_statements' SET pg_stat_statements.track_utility TO 'true': please edit '/home/dfontaine/pgsql/exts/data/postgresql.conf'. NOTICE: Extension 'pg_stat_statements' SET pg_stat_statements.save TO 'true': please edit '/home/dfontaine/pgsql/exts/data/postgresql.conf'. NOTICE: Installing extension 'pg_stat_statements' from '/home/dfontaine/pgsql/exts/share/contrib/pg_stat_statements.sql', in schema public, with user data CREATE EXTENSION
After that, the custom_variable_classes is stored in the pg_extension catalog and will be dynamically set at backend creation time. The GUC management has been changed to be SUSET in the patch. To do that, we keep GUC placeholder in a secondary storage, and we are able to merge those into the main list when custom_variable_classes changes.
Code design and patch series
As usual, the patch growing, it has been asked to separate some parts of it in order to ease reviewing. We're now at 5 patches, with some dependencies between them.
cfparser \
} extension \
pg_execute_from_file() / } alter_extension
/
set_schema /
The extension patch has been only produced with the cfparser and pg_execute_from_file() bits, to ease testing. The set schema has been produced separately and is independent, the alter_extension patch is an incremental patch atop both the extension and the set_schema patch, to ease discussion of APIs.
It should be far easier to use the git repository at http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary
tracking extension objects
The backend/commands/extension.c file uses some new backend static variables in order to track the objects creation and add dependencies:
/* * create_extension is only set when running a CREATE EXTENSION command, it * allows to register the (INTERNAL) dependencies between the pg_extension * row and the SQL objects created by its installation script. * * For that to work out, all CREATE commands have been modified so that they * will inquire about create_extension and call recordDependencyOn() when * it's set. */ extern ObjectAddress CreateExtensionAddress; bool create_extension; bool create_extension_with_user_data;
cfparser
This part of the patch allows to reuse the recovery.conf line parser function from elsewhere in the code.
pg_execute_from_files()
That function will load the file content in memory, then apply placeholders, then use SPI as if it were a single query string, which it is, somehow. That means the extension's script can not embed BEGIN/COMMIT commands.
extension, the main patch
This main patch contains the bulk work for pg_dump support of extensions, and proposes a single placeholder for script files, @extschema@.
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;
