扩展
来自 PostgreSQL Wiki
跳转到导航跳转到搜索扩展
扩展在 PostgreSQL 9.1 中实现，以便更轻松地打包对 PostgreSQL 的补充。扩展可以打包用户可见的函数，或使用 PostgreSQL 中的钩子来修改数据库执行某些过程的方式。
扩展构建者对未来功能的愿望清单
此列表最初是在 PgCon 2018 的非正式会议期间编写的。请随时添加其他请求和/或澄清这些请求。
版本控制
- 如何在同一个 PostgreSQL 集群中处理不同数据库中的多个版本？
- 如何在升级时处理旧版本的卸载？
- 当前系统由于钩子执行方式（使用指向其他扩展的指针）而无法真正卸载。
- 核心代码是否应该注册钩子并管理它们的调用，而不是依赖扩展对彼此友好并传递钩子？
- 如何在升级期间处理之前版本中定义的扩展定义的 GUC？
文档/调试改进
- 目前，钩子在整个代码库中相对难以找到（
ack _hook似乎是人们用来做这件事的方法）我们如何才能更轻松地找到扩展可能想要使用的所有钩子，并改进关于它们可能对什么有用/为什么存在的文档？ - 我们如何在安装/升级时通过 PL/PGSQL 提供更好的错误消息？它们目前过于简短，无法真正轻松地理解发生了什么错误。
依赖检查和安装改进
- 目前，我们按照在
shared_preload_libraries变量中指定的顺序加载扩展，我们是否应该改为检查依赖项并根据依赖项对它们进行排序？ - 我们能否改进如何检查扩展的依赖项？
- 这应该如何与版本控制交互？
- 我们能否以某种方式对扩展版本进行排序，无论是通用排序还是扩展定义的排序，以便依赖项可以进行>/</=类型的版本检查？
- 或者，扩展是否应该列出它们的功能，以便我们改为依赖扩展的功能而不是版本？
安装/卸载/目录改进
- 我们是否应该允许在安装扩展时自动将某些内容添加到
shared_preload_libraries中，而不是必须在配置文件中指定？ - 是否应该为扩展提供一个共享目录，并确定哪些版本安装在哪些数据库中？
新钩子？
- 一个需要考虑的主要问题是：我们是否应该放弃扩展的钩子式集成，采用另一种模型？这种模型可能是什么样的？
- 可能的新的钩子列表，请根据需要添加新的钩子。
- 表文件扩展钩子
关于 9.1 版中原始扩展补丁集的说明
一系列补丁为 PostgreSQL 的转储和恢复实现了扩展支持。为了实现这一目标，需要做很多事情，而且现在的补丁相当大。这是累积分支的 diffstat
$ 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(-)
概述
扩展补丁必须考虑到转储和恢复支持。关于完成的所有操作都是为了这个目的，那里几乎没有额外的操作。目标是让 pg_dump 不输出扩展的安装脚本，该脚本与数据库对象分开维护。这样，当需要升级扩展时，也可以更容易地恢复。我们希望在转储中看到的是
CREATE EXTENSION citext WITH SCHEMA foo NO USER DATA;
对于模式支持，请参阅上面的讨论。
一些扩展将安装用户可以编辑数据的表，或使用命令和函数来更改用户的数据。这里的想法是数据会发生变化，并且我们希望将它们作为转储的一部分，因为这些数据的维护不是在扩展的 SCM 中进行的，而是在数据库中进行的。
用户界面
psql
既有 \dx 用于列出已安装的扩展，也有 \dx+ 用于列出已安装的和可用的扩展。
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)
pg_extension_objects() 函数对于最终用户和想要轻松找到其对象 oid 以管理升级（例如）的扩展作者非常有用。
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)
目录、系统视图、函数
目录在这里只有最少的信息。所有与扩展相关的操作都只能由超级用户执行，我们只需要一个名称、一个版本字符串和一个 custom_variable_classes 设置，如后文所述。
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)
pg_extensions 系统视图还将列出可用的扩展，并提供一个“已安装”列来对这些扩展进行排序
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)
命令
基本上，您可以创建和删除扩展，还可以修改扩展。
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 ]
此外
ALTER EXTENSION extension_name SET SCHEMA schema_name;
用户数据和扩展
扩展补丁的目标是让 pg_dump *不*关心扩展的对象，因为它们在某个存储库中维护，而不是在数据库中维护。这里有一个例外，当扩展安装用户可以编辑的表（或任何东西，实际上）时，无论是直接编辑还是通过扩展提供的某些工具来编辑。现在，我们也需要转储这些表。
扩展作者有一个函数，他们可以从其脚本中调用该函数来标记一些需要转储的对象，文档包含此示例
+ 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;
+ $$;
这些标志将使 pg_dump 分别转储对象。想法是，对象与其扩展之间的依赖关系从 DEPENDENCY_INTERNAL（不转储）更改为 DEPENDENCY_NORMAL（转储）。
模式和扩展
在 -hackers 列表中有人要求提供模式支持，这只是一个简单地搜索和替换扩展脚本中的 @extschema@ 占位符，用用户在 CREATE EXTENSION 命令中输入的模式替换它，该模式默认为“public”。
另一个补丁提出了 ALTER EXTENSION SET SCHEMA 命令：我们能够跟踪所有扩展的对象，因此我们能够对所有对象运行 SET SCHEMA 命令。这是一个可选的“糖”设施，被认为很有价值 :)
pg_dump 支持
pg_dump 命令已经打过补丁，因此它不会考虑任何对扩展的 oid 具有直接 DEPENDENDY_INTERNAL pg_depend 条目的对象。这是所有工作的唯一目标。
当然，我们有一些特殊情况需要考虑，否则它就不会有趣，例如以下情况
! 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);
! }
通常，它比这简单得多，就像操作符族示例一样
! 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);
! }
使用 DEPENDENCY_INTERNAL 实现使实现 pg_dump 支持变得非常简单。
扩展创作
与您在打补丁之前需要做的事情相比，作为扩展作者，您现在只需要做很少的额外事情。您必须提供将最终进入 pg_extension 目录的元数据，这就是新的 .control 文件的作用。pgxs Makefile 中也支持这一点。contrib 中的所有这些更改都与之有关。
控制文件
允许将扩展注册到目录中。最小的元数据和语法支持（使用 recovery.conf 解析器）、固定预期位置（与 SQL 脚本并排）、pgxs 支持
$ cat contrib/citext/citext.control.in # citext comment = 'case-insensitive character string type' version = 'EXTVERSION'
$ grep EXT contrib/citext/Makefile EXTENSION = $(MODULES) EXTVERSION = $(VERSION)
一些更复杂的示例。首先，一个脚本，其名称与扩展名称不同，其次，一些更多变量。
$ 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
此 GUC 明显是扩展作者的工具。用户将能够编辑其 postgresql.conf 文件并在那里设置自定义类别的各个设置，但如果我们能够自动管理，则最好不要将自定义变量类别公开给用户。
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
之后，custom_variable_classes 会存储在 pg_extension 目录中，并在后端创建时动态设置。在补丁中，GUC 管理已更改为 SUSET。为此，我们在辅助存储中保留 GUC 占位符，并且当 custom_variable_classes 发生变化时，我们能够将这些占位符合并到主列表中。
代码设计和补丁系列
像往常一样，补丁在不断增长，有人要求将其一部分分开，以便于审查。我们现在有 5 个补丁，它们之间存在一些依赖关系。
cfparser \
} extension \
pg_execute_from_file() / } alter_extension
/
set_schema /
扩展补丁仅使用 cfparser 和 pg_execute_from_file() 部分生成，以便于测试。set schema 是单独生成的，并且是独立的，alter_extension 补丁是在扩展和 set_schema 补丁之上的增量补丁，以便于讨论 API。
使用 http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=summary 上的 Git 存储库应该方便得多。
跟踪扩展对象
backend/commands/extension.c 文件使用一些新的后端静态变量来跟踪对象的创建并添加依赖关系
/* * 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
补丁的这一部分允许从代码中的其他地方重用 recovery.conf 行解析器函数。
pg_execute_from_files()
该函数将在内存中加载文件内容，然后应用占位符，然后使用 SPI，就像它是一个单个查询字符串一样，它确实有点像。这意味着扩展脚本不能嵌入 BEGIN/COMMIT 命令。
extension，主要补丁
此主要补丁包含用于 pg_dump 对扩展的支持的大部分工作，并为脚本文件提出了一个唯一的占位符，@extschema@。
set_schema
支持补丁，用于能够轻松地将所有扩展对象移动到另一个模式中。
/* + * 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)
因此，我们有以下支持
+ 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
从之前的支持开始，我们现在能够使用 ALTER EXTENSION citext SET SCHEMA utils。
